Re: slowness in fetch from the psqlodbc driver - Mailing list pgsql-odbc

Sounds more like a query optimisation issue on the
database side.  Are you running "VACUUM ANALYZE"
regularly in PostgreSQL?  Try running "EXPLAIN
ANALYZE" for the queries that are causing you trouble.
 The ODBC logs will give you the exact queries that
are being sent to PostgreSQL.

--- Ionut Giurea <elrubio@unirea.ro> wrote:
>
> Hi,
>
> I am using PsqlODBC 7.2.5 and Postgres 7.2.2. While
> trying to run a Delphi
> 3 application using BDE I noticed some important
> differences in comparison
> to an MS Sql database in selecting multiple rows
> from a database. This can
> be noticed even in borland's database explorer when
> running a select * from
> large_table and displaying the results in a grid
> table. I looked at the
> traffic between the computer and the database usgina
> tcp/ip packet sniffer
> and the response I got from the MS SQL and Postgres
> SQL server are quite
> similiar as traffic and speed, BUT there are two
> differences:
>
> 1. In the postgres case, all the respone records are
> fetched from the
> database server by the odbc driver,while in the MS
> SQL case, not all the
> response records are transferred, only a part, and
> the rest are transferred
> when I move down in the application's grid table.
> This difference is not so
> important from the user's point of view, he didn't
> even notices it, it
> might became semnificative for very large tables
> when the traffic between
> the application and the database server is big.
>
> 2. The fetch from the odbc works much,much slower in
> the postgresodbc
> driver than in the mssql driver. When I try a locate
> or a recordcount
> function in the query table from the Delphi
> application, they are making a
> fetch_all from the odbc driver so I don't see any
> traffic between the
> station and the Postgres database server, I notice
> fetching the remaining
> rows from the MS SQL Server as I explained  before,
> but this Fetch_All
> operation takes about half a second in the MS SQL
> case and 8 seconds in the
> Postgres case for a 2000 rows query. So I made a
> simple application with a
> query, a datasource anda grid table. The application
> performs a
> {query.close; query.open;query.recordcount} While
> looking at the tcp/ip
> sniffer in these 2 cases I saw that the traffic is
> the same and at the same
> speed, but while in the mssql case the grid is
> filled up almost immediately
> the tcp/ip traffic stops, in the Postgres case it
> takes many seconds until
> the results are displayed. Both cases are using bde
> with odbc drivers so
> the problem must be in postgres's odbc driver. Can
> this be speeded up?
>
> All the best,
> Ionut
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org


__________________________________________________
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com