Thread: performance of first exec of prepared statement
I've noticed that the first exec of an INSERT prepared statement takes ~5 time longer (I'm using libpq in C and wrapping the calls to time them) then subsequent exec's is this the expected behavior and if so is there any thing I can do to mitigate this affect?
Ted
On Thu, Apr 16, 2020 at 6:29 PM Ted Toth <txtoth@gmail.com> wrote:
I've noticed that the first exec of an INSERT prepared statement takes ~5 time longer (I'm using libpq in C and wrapping the calls to time them) then subsequent exec's is this the expected behavior and if so is there any thing I can do to mitigate this affect?Ted
For example (in my environment) I'm seeing the prepare take ~10ms, the first exec take ~30 ms and subsequent exec's take ~4 ms.
On 4/16/20 4:59 PM, Ted Toth wrote: > > > On Thu, Apr 16, 2020 at 6:29 PM Ted Toth <txtoth@gmail.com > <mailto:txtoth@gmail.com>> wrote: > > I've noticed that the first exec of an INSERT prepared statement > takes ~5 time longer (I'm using libpq in C and wrapping the calls to > time them) then subsequent exec's is this the expected behavior and > if so is there any thing I can do to mitigate this affect? > > Ted > > > For example (in my environment) I'm seeing the prepare take ~10ms, the > first exec take ~30 ms and subsequent exec's take ~4 ms. > I don't have an answer. I believe though that to help those that might it would be helpful to show the actual code. -- Adrian Klaver adrian.klaver@aklaver.com
On 4/16/20 6:15 PM, Adrian Klaver wrote: > On 4/16/20 4:59 PM, Ted Toth wrote: >> >> >> On Thu, Apr 16, 2020 at 6:29 PM Ted Toth <txtoth@gmail.com >> <mailto:txtoth@gmail.com>> wrote: >> >> I've noticed that the first exec of an INSERT prepared statement >> takes ~5 time longer (I'm using libpq in C and wrapping the calls to >> time them) then subsequent exec's is this the expected behavior and >> if so is there any thing I can do to mitigate this affect? >> >> Ted >> >> >> For example (in my environment) I'm seeing the prepare take ~10ms, >> the first exec take ~30 ms and subsequent exec's take ~4 ms. >> > > I don't have an answer. I believe though that to help those that might > it would be helpful to show the actual code. > > You expect the subsequent calls to benefit from the cached query parse and planning. What does you query cost without begin wrapped in a prepared statement (preferably from a cold start).
On Thu, Apr 16, 2020 at 8:09 PM Rob Sargent <robjsargent@gmail.com> wrote:
On 4/16/20 6:15 PM, Adrian Klaver wrote:
> On 4/16/20 4:59 PM, Ted Toth wrote:
>>
>>
>> On Thu, Apr 16, 2020 at 6:29 PM Ted Toth <txtoth@gmail.com
>> <mailto:txtoth@gmail.com>> wrote:
>>
>> I've noticed that the first exec of an INSERT prepared statement
>> takes ~5 time longer (I'm using libpq in C and wrapping the calls to
>> time them) then subsequent exec's is this the expected behavior and
>> if so is there any thing I can do to mitigate this affect?
>>
>> Ted
>>
>>
>> For example (in my environment) I'm seeing the prepare take ~10ms,
>> the first exec take ~30 ms and subsequent exec's take ~4 ms.
>>
>
> I don't have an answer. I believe though that to help those that might
> it would be helpful to show the actual code.
>
>
You expect the subsequent calls to benefit from the cached query parse
and planning. What does you query cost without begin wrapped in a
prepared statement (preferably from a cold start).
I thought that's what the PQprepare call was supposed to do i.e. parsing/planning.
It's a bit difficult to get an unprepared query cost since there are a lot of columns :(
#define INSERT_SQL "INSERT INTO t (<column names>) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80,$81,$82,$83,$84,$85,$86,$87);"
On Fri, Apr 17, 2020 at 8:28 AM Ted Toth <txtoth@gmail.com> wrote:
On Thu, Apr 16, 2020 at 8:09 PM Rob Sargent <robjsargent@gmail.com> wrote:On 4/16/20 6:15 PM, Adrian Klaver wrote:
> On 4/16/20 4:59 PM, Ted Toth wrote:
>>
>>
>> On Thu, Apr 16, 2020 at 6:29 PM Ted Toth <txtoth@gmail.com
>> <mailto:txtoth@gmail.com>> wrote:
>>
>> I've noticed that the first exec of an INSERT prepared statement
>> takes ~5 time longer (I'm using libpq in C and wrapping the calls to
>> time them) then subsequent exec's is this the expected behavior and
>> if so is there any thing I can do to mitigate this affect?
>>
>> Ted
>>
>>
>> For example (in my environment) I'm seeing the prepare take ~10ms,
>> the first exec take ~30 ms and subsequent exec's take ~4 ms.
>>
>
> I don't have an answer. I believe though that to help those that might
> it would be helpful to show the actual code.
>
>
You expect the subsequent calls to benefit from the cached query parse
and planning. What does you query cost without begin wrapped in a
prepared statement (preferably from a cold start).I thought that's what the PQprepare call was supposed to do i.e. parsing/planning.It's a bit difficult to get an unprepared query cost since there are a lot of columns :(#define INSERT_SQL "INSERT INTO t (<column names>) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80,$81,$82,$83,$84,$85,$86,$87);"
Ah prepare does the parsing and execute does the planning.
Another related question is that my implementation uses strings for all values is there a performance benefit to using actual values and specifying their datatype?
On Apr 17, 2020, at 7:34 AM, Ted Toth <txtoth@gmail.com> wrote:
On Fri, Apr 17, 2020 at 8:28 AM Ted Toth <txtoth@gmail.com> wrote:On Thu, Apr 16, 2020 at 8:09 PM Rob Sargent <robjsargent@gmail.com> wrote:On 4/16/20 6:15 PM, Adrian Klaver wrote:
> On 4/16/20 4:59 PM, Ted Toth wrote:
>>
>>
>> On Thu, Apr 16, 2020 at 6:29 PM Ted Toth <txtoth@gmail.com
>> <mailto:txtoth@gmail.com>> wrote:
>>
>> I've noticed that the first exec of an INSERT prepared statement
>> takes ~5 time longer (I'm using libpq in C and wrapping the calls to
>> time them) then subsequent exec's is this the expected behavior and
>> if so is there any thing I can do to mitigate this affect?
>>
>> Ted
>>
>>
>> For example (in my environment) I'm seeing the prepare take ~10ms,
>> the first exec take ~30 ms and subsequent exec's take ~4 ms.
>>
>
> I don't have an answer. I believe though that to help those that might
> it would be helpful to show the actual code.
>
>
You expect the subsequent calls to benefit from the cached query parse
and planning. What does you query cost without begin wrapped in a
prepared statement (preferably from a cold start).I thought that's what the PQprepare call was supposed to do i.e. parsing/planning.It's a bit difficult to get an unprepared query cost since there are a lot of columns :(#define INSERT_SQL "INSERT INTO t (<column names>) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80,$81,$82,$83,$84,$85,$86,$87);"Ah prepare does the parsing and execute does the planning.Another related question is that my implementation uses strings for all values is there a performance benefit to using actual values and specifying their datatype?