From: Ronald Hahn, DOCFOCUS INC. <rhahn@docfocus.ca>
To: pgsql-performance@postgresql.org
Subject: Result Set over Network Question
Date: Mon, 30 Apr 2012 12:32:42 -0600
Message-ID: <4F9EDACA.4010901@docfocus.ca> (raw)
Hi,
We have recently switch our product from MS SQL 2000 to Postgresql
9.0.7. We have tuned the searches and indexes so that they are very
close (often better) to what sql2k was giving us. We are noticing some
differences now in the time it takes for the result set to make it back
to the client and would like some help finding out why.
What we see on the PG side is that if we run:
Select SomeInt32 from someTable where something Limit 1
It consistently returns the results "instantaneously" after the fetch
time. If we run the same select but ask for more data the fetch time
stays the same but the row takes longer to come back. Bringing back 400
bytes takes 1-2 s but bringing back 866 bytes takes 9 - 11 s.
We went to the SQL2k server (On the same hardware) and ran the selects
again. When bringing back on an int32 PG was faster with the fetch and
the row coming back in 1-5 ms and SQL2k coming back in 500-700 ms. This
tells me that the problem is not related to PG index or Disk. When
bringing back 400 bytes PG fetch time would be 1-2 ms but the results
would take 2-3 s but SQL2k would it bring back in 700-900 ms. Working
with 866 bytes, PG fetch time is 1-3 ms with the results coming back in
9 - 11 s and SQL2k bringing the results back in 2-3 s.
The head to head test was run in Aqua Data Studio 10.0.8 and ODBC driver
9.0.3.10. The same slow down happens when use PGadminIII. The differnces
in time to not occure when running on the pg/sql server computer so I
think there is a network component to this.
I know that as you bring back more data it takes longer but why is there
such a difference in the time it takes PG to send the data compared to
the time it takes sql2k to send it?
Any thoughts and suggestions are very much appreciated
Thanks
Ron
--
*Ronald Hahn* , CCP, CIPS Member
*DOCFOCUS INC.*
Suite 103, 17505 - 107 Avenue,
Edmonton, Alberta T5S 1E5
Phone: 780.444.5407
Toll Free: 800.661.2727 (ext 6)
Fax: 780.444.5409
Email: rhahn@docfocus.ca
Support:dfisupport@docfocus.ca <mailto:dfisupport@docfocus.ca>
DOCFOCUS.ca <http://docfocus.ca/;
There are 2 kinds of people in the world.
Those who can extrapolate from incomplete data
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rhahn@docfocus.ca
Subject: Re: Result Set over Network Question
In-Reply-To: <4F9EDACA.4010901@docfocus.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox