pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedscrollable cursor via odbc
3+ messages / 3 participants
[nested] [flat]
* scrollable cursor via odbc
@ 1998-06-29 16:22 Christian Steindl <e9425178@student.tuwien.ac.at>
0 siblings, 2 replies; 3+ messages in thread
From: Christian Steindl @ 1998-06-29 16:22 UTC (permalink / raw)
To: pgsql-interfaces
i use the odbc interface to the postgresql but it seems to be quite
slowly.
although it's a single table with about 20.000 rows.
i set a link in an access-db and scrolling (next page, last row) is very
slowly.
is there a way to optimize the performance?
can i use scrollable cursor via the odbc-drv?
best regards
Christian Steindl
--------------------------------------------------------------
student at TU Wien
Address: Austria, Vienna, A-1210 Wien, Pichelwangergasse 35/19
Telephone: +43-1-270 10 72
Facsimile: +43-1-170 45 88
e-mail: e9425178@student.tuwien.ac.at
--------------------------------------------------------------
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [INTERFACES] scrollable cursor via odbc
@ 1998-06-29 18:10 Byron Nikolaidis <byronn@insightdist.com>
parent: Christian Steindl <e9425178@student.tuwien.ac.at>
1 sibling, 0 replies; 3+ messages in thread
From: Byron Nikolaidis @ 1998-06-29 18:10 UTC (permalink / raw)
To: Christian Steindl <e9425178@student.tuwien.ac.at>; +Cc: pgsql-interfaces
Christian Steindl wrote:
> i use the odbc interface to the postgresql but it seems to be quite
> slowly.
> although it's a single table with about 20.000 rows.
> i set a link in an access-db and scrolling (next page, last row) is very
> slowly.
> is there a way to optimize the performance?
> can i use scrollable cursor via the odbc-drv?
>
We need more information than the subjective "slow".
First of all, what version of the driver are you using?
Also, there are numerous reasons besides the driver that the performance can
be slow.
Are you using an index on your table?
Are you using a unique identifier in Access? The kind of queries this
generates (multiple OR's) can be extremely slow because of the backend
canonifier problem.
Is the system postgres is running on slow?
Do you have the "Use Declare/Fetch" driver option checked?
If you are using a unique identifier, is it a single keypart or multiples?
Byron
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [INTERFACES] scrollable cursor via odbc
@ 1998-06-29 18:45 David Hartwig <daveh@insightdist.com>
parent: Christian Steindl <e9425178@student.tuwien.ac.at>
1 sibling, 0 replies; 3+ messages in thread
From: David Hartwig @ 1998-06-29 18:45 UTC (permalink / raw)
To: Christian Steindl <e9425178@student.tuwien.ac.at>; +Cc: pgsql-interfaces
Christian,
The problem is in the backend optimizer. If you turn on the commlog, you
will find that queries are being generated with lots of ORs. This is to get
a record set. If you run that query with EXPLAIN you will find that it is
doing a sequential scan. (Maybe several per page scroll.) Even worse, if
you have a multi-part key, the optimizer will spend (expend) much resource
CNFifying the where clause.
I am working on a patch to resolve this in the backend. I have a workable
solution, for queries with this particular signature, but I am trying to
make it more general purpose in nature.
Christian Steindl wrote:
> i use the odbc interface to the postgresql but it seems to be quite
> slowly.
> although it's a single table with about 20.000 rows.
> i set a link in an access-db and scrolling (next page, last row) is very
> slowly.
> is there a way to optimize the performance?
> can i use scrollable cursor via the odbc-drv?
>
> best regards
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1998-06-29 18:45 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-06-29 16:22 scrollable cursor via odbc Christian Steindl <e9425178@student.tuwien.ac.at>
1998-06-29 18:10 ` Byron Nikolaidis <byronn@insightdist.com>
1998-06-29 18:45 ` David Hartwig <daveh@insightdist.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox