Re: slowness in fetch from the psqlodbc driver

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

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(at)unirea(dot)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

> -----Original Message-----
> From: Of Ionut Giurea
>
> 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.

Please turn on the *use declare/fetch* option.

regards,
Hiroshi Inoue