On Tue, Apr 3, 2012 at 7:04 PM, Dave Crooke <dcrooke@gmail.com> wrote:
>On Tue, Apr 3, 2012 at 10:38 AM, Claudio Freire <klaussfreire@gmail.com> wrote:
>> You perform 8 roundtrips minimum per event, so that's 375us per query.
>> It doesn't look like much. That's probably Nagle and task switching
>> time, I don't think you can get it much lower than that, without
>> issuing less queries (ie: using the COPY method).
> I may be missing something stated earlier, but surely there are options in between 7 individual statements and
resortingto COPY and temp tables.
> I'm thinking of a set of precompiled queries / prepared statements along the lines of "SELECT FOR UPDATE WHERE foo in
(?,?, ?, .... ?)" that handle e.g. 500-1000 records per invocation. Or what about a stored procedure that updates one
record,performing the necessary 7 steps, and then calling that in bulk?
> I agree with the assessment that 375us per statement is pretty decent, and that going after the communication channel
(TCPvs local pipe) is chasing pennies when there are $100 bills lying around waiting to be collected.
Thanks for the suggestions. We ended up re-factoring the code: caching some of the data that we needed in order to
eliminatesome of the queries previously run and inserting data completion into update statements in the form of UPDATE
SET... (SELECT ...) which brought us down to only one SQL query as opposed to 7 and this brings the processing time
downfrom 4.5ms (previously stated 3ms was not reproduced) down to ~1ms which is great for us.
Many thanks for the help from all of you,
Ofer