Re: slowness in fetch from the psqlodbc driver

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

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