Re: TCP Overhead on Local Loopback

On Tue, Apr 3, 2012 at 7:04 PM, Dave Crooke <dcrooke(at)gmail(dot)com> wrote:
>On Tue, Apr 3, 2012 at 10:38 AM, Claudio Freire <klaussfreire(at)gmail(dot)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 resorting to 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 (TCP vs 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 eliminate some 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 down from 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