postgres odbc slow. need help

[FBELGHAIT]

Hi,

i have a query that returns about 6000 records. when i do EXPLAIN ANALYZE, the time estimated is 1500 ms. When i try to fill a recordset with ADO it tooks more than 35 second !!.

is it problem of odbc?

thanks for your help

fodil

 

[rycamor]

Think about the network bandwidth. If you are using ODBC to access a PostgreSQL server on another machine, then your biggest bottleneck is the network. 6000 records is a lot to bring over through ODBC, and ODBC is not the fastest protocol anyway. What speed is your network?

I see this kind of question a lot, and it really makes me wonder about the reason: why do you need 6000 records all at once, for a client-side app? No matter what client-side GUI you use, the end user can only see one screen at a time. So I can see no reason to have more than approximately 50 - 200 records at a time, for the most part. It sounds like you are trying to do in your client-side application what should be done on the server: recordset manipulation. The whole point of SQL is to keep the logic in the server, where it belongs, and only use the client side for display.

Also, you might want to spend some time reading about PostgreSQL cursors with stored procedures (

http://www.postgresql.org/idocs/index.php?plpgsql-cursors.html),

which allow you to keep query results on the server, fetching them incrementally. -------------------------------------------

Big Brother: "War is Peace" -- Big Business: "Trust is Suspicion"
(

http://www.cl.cam.ac.uk/~rja14/tcpa-faq.html)