Extreme slowness

Hi all,

I am working with a company that is trying to migrate an application to
Postgresql. After long last we have passed the setting up stage, and
have moved over to the performance testing stage. At the moment,
performance when running a long query is 10 times worse for PGSQL than
MSDE (both when running on the same machine).

When enabling debugging on PostgreSQL, I can see that the query that
takes so long translates to several hundred thousands individual
queries. The log is full with lines of repeating queries (each in it's
own transaction), with slightly varying ctid selectors.

The application uses the CRecordSet MFC construct to query the
database.I have not delved too deeply into the code, but my
understanding of it is that, due to the lack of proper cursors suport,
the ODBC driver seperates the process into distinguish queries. This
turns the ODBC->server communication into the bottleneck.

My questions:
1. Is it true that, when PGSQL 7.4 will be out, the ODBC will allow
support of bidirectional updateable cursors? Can anyone give a rough
uncommiting time estimate for that once the database is out?
2. Is there a reason each select is in it's own query? I would have
though they could have been prepared together?
3. Is there a reason each select is in it's own transaction? Does the
ODBC standard require that the data be updated in the query if someone
else changes it?

Shachar

--
Shachar Shemesh
Open Source integration consultant
Home page & resume - http://www.shemesh.biz/