Postgres ODBC very slow

[FBELGHAIT]

Hi every body,


I have postgresql version 7.1.3 installed on AIX OS, and an ODBC version 7.1.9.

I tried to execute simple sql query on my PC using the odbc, and it was very slow. the same query on the AIX machine is very quick. i don't know if there is something to do in order to make the ODBC quicker.

Thanks for your help

FBELGHAIT

 

[rycamor]

Was this by any chance a query that returns the whole contents of the table as a result set? Remember, ODBC is only a pipe. If you make a large query locally, the result set might be several megabytes, but it seems to return instantly, because it's happening in RAM. Making the same request over a network will cause all of that data to transfer, in order to return the result. This is even the case without ODBC, if you are just doing a direct TCP connection to PostgreSQL. ODBC just adds the favor of making it a little slower, by adding the ODBC database abstraction layer.

I'm sure there are things you can do to optimize ODBC, and even more imporetantly, to optimize your network connection. However, the point is, when working with a database over the network, try to keep your result sets small. There is rarely a need to get a whole result set, and then manipulate it client-side. Better to return the small amount needed for any one client-side view of the data, for example a generic "SELECT * FROM tablename LIMIT 50", returning 5000 rows as "pages", with only 50 results to a page. Then ODBC only has to send 50 records at a time, as the user navigates through the pages.

Or is it extremely slow, even for small result sets? -------------------------------------------

"Now, this might cause some discomfort..."
(

http://www.wired.com/news/politics/0,1283,51274,00.html)