Обсуждение: scrollable cursor via odbc
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 --------------------------------------------------------------
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
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 >