Thread: 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@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
You have exceeded my competence in ODBC :-) The ODBC gurus are busy people, and may take a day or so to reply, but you can usually count on help if you wait a little bit. I suggest searching the pgsql-odbc archives for information (look for mailing list archives at http://www.postgresql.org). --- Ionut Giurea <elrubio@unirea.ro> wrote: > No no no, not at all, it's nothing wrong on the > database side. I tried the > same with the postgres odbc driver provided by > openlink(you can download > and evaluate it for 30 days) and it is very fast in > fetching data between > the application and the odbc driver, as fast as ms > sql odbc driver, about > 10 times faster than psql odbc. There is probably > one or more time- > consuming functions implemented in the psql odbc > driver in the application > side, who can be optimized. > > I enabled logging in the psql odbc driver and I > think the delay is in > copy_and_convert or fetch function: > ........ > [-507079]**** SC_fetch: manual_result > [-507079]fetch: cols=15, lf=0, opts = 66391664, > opts->bindings = 66852160, > buffer[] = 17781020 > [-507079]type = 1043 > [-507079]value = '01' > [-507079]copy_and_convert: field_type = 1043, fctype > = 1, value = '01', > cbValueMax=3 > [-507079]DEFAULT: len = 2, ptr = '01' > [-507079] SQL_C_CHAR, default: len = 2, > cbValueMax = 3, rgbValueBindRow > = '01' > [-507079]copy_and_convert: retval = 0 > [-507079]fetch: cols=15, lf=1, opts = 66391664, > opts->bindings = 66852160, > buffer[] = 17781160 > [-507079]type = 1043 > [-507079]value = 'AMRO PLOIESTI' > [-507079]copy_and_convert: field_type = 1043, fctype > = 1, value = 'AMRO > PLOIESTI', cbValueMax=51 > [-507079]DEFAULT: len = 13, ptr = 'AMRO PLOIESTI' > [-507079] SQL_C_CHAR, default: len = 13, > cbValueMax = 51, > rgbValueBindRow = 'AMRO PLOIESTI' > [-507079]copy_and_convert: retval = 0 > ............. > > > On Fri, 14 Feb 2003 07:02:34 -0800 (PST), Jeff > Eckermann > <jeff_eckermann@yahoo.com> wrote: > > > 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 > > > > > > > > -- > __________________________________________________ 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
Hi, I've been trying to get my app to work using the server side prepare option in the odbc driver. I't dies on the first query - with SQLSTATE=07009 - Invalid column number in DescribeCol. [Native Error=13]] The query being issued is :- 'select current_user as x, current_timestamp as y, current_database() as z'. The logs from the server look good :- 2003-05-24 23:15:06 [1541] LOG: query: BEGIN;PREPARE _PLAN1202048 as select urrent_user as x, current_timestamp as y, current_database() as z;EXECUTE _PLAN 202048 2003-05-24 23:15:06 [1541] LOG: duration: 0.009996 sec 2003-05-24 23:15:06 [1541] LOG: query: COMMIT 2003-05-24 23:15:06 [1541] LOG: duration: 0.001244 sec Has anyone tried this? got it to work? Is it really part of this release (compiled from source current from the cvs as at 2003-05-24)? or should I just ignore it for now? Regards, Wayne