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
DOCFOCUS.ca
There are 2 kinds of people in the world.
Those who can extrapolate from incomplete data