pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBest ODBC cursor and lock types for fastest reading?
3+ messages / 2 participants
[nested] [flat]
* Best ODBC cursor and lock types for fastest reading?
@ 2003-04-29 13:53 Robert John Shepherd <robert@reviewer.co.uk>
0 siblings, 1 reply; 3+ messages in thread
From: Robert John Shepherd @ 2003-04-29 13:53 UTC (permalink / raw)
To: pgsql-performance
I access Postgresql through the ODBC driver, and always only read small
recordsets (never updating them) with forward cursors.
The following options are defined in ADO with which I can create a
recordset with:
Cursor types:
adOpenForwardOnly (what I currently use)
adOpenKeyset
adOpenDynamic
adOpenStatic
Lock types:
adLockReadOnly
adLockPessimistic
adLockOptimistic (what I currently use)
adLockBatchOptimistic
Do any of these offer a performance gain over others? I used to use
adLockReadOnly with MS-SQL which really sped things up but this doesn't
seem to work at all under Postgresql and I've been using
adLockOptimistic instead.
Yours Unwhettedly,
Robert John Shepherd.
Editor
DVD REVIEWER
The UK's BIGGEST Online DVD Magazine
http://www.dvd.reviewer.co.uk
For a copy of my Public PGP key, email: pgp@robertsworld.org.uk
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Best ODBC cursor and lock types for fastest reading?
@ 2003-04-29 15:38 Josh Berkus <josh@agliodbs.com>
parent: Robert John Shepherd <robert@reviewer.co.uk>
0 siblings, 1 reply; 3+ messages in thread
From: Josh Berkus @ 2003-04-29 15:38 UTC (permalink / raw)
To: Robert John Shepherd <robert@reviewer.co.uk>; pgsql-performance
Robert,
> The following options are defined in ADO with which I can create a
> recordset with:
<snip>
> Do any of these offer a performance gain over others? I used to use
> adLockReadOnly with MS-SQL which really sped things up but this doesn't
> seem to work at all under Postgresql and I've been using
> adLockOptimistic instead.
All of the types you list were designed around the MS SQL/MSDE server
architecture, and many do not apply to PostgreSQL (for example, Postgres does
not use read locks and does not support client-side keyset cursors as far as
I know). I wouldn't be surprised to find out that the pgODBC driver is
ignoring most of these options as irrelevant -- you should contact the pgODBC
project to find out.
Certainly I wouldn't expect any setting other than adLockPessimistic to have
an effect on the speed at which you get rows from the server (Pessimistic
would presumably declare "SELECT FOR UPDATE", which would be slower).
However, one or more types might be faster on the client side than the
others; I recommmend that you set up a test case and experiment.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Best ODBC cursor and lock types for fastest reading?
@ 2003-04-29 15:43 Robert John Shepherd <robert@reviewer.co.uk>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 3+ messages in thread
From: Robert John Shepherd @ 2003-04-29 15:43 UTC (permalink / raw)
To: pgsql-performance
> All of the types you list were designed around the MS SQL/MSDE server
> architecture, and many do not apply to PostgreSQL (for
> example, Postgres does not use read locks and does not support
> client-side keyset cursors as far as I know).
Thanks, this backs up my feelings from some of my limited experiments
with them.
I guess I need to keep trying to rewrite my queries to avoid nested
loops then, as this seems to be the main performance hit I get as all
use indexes properly.
Yours Unwhettedly,
Robert John Shepherd.
Editor
DVD REVIEWER
The UK's BIGGEST Online DVD Magazine
http://www.dvd.reviewer.co.uk
For a copy of my Public PGP key, email: pgp@robertsworld.org.uk
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-04-29 15:43 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-29 13:53 Best ODBC cursor and lock types for fastest reading? Robert John Shepherd <robert@reviewer.co.uk>
2003-04-29 15:38 ` Josh Berkus <josh@agliodbs.com>
2003-04-29 15:43 ` Robert John Shepherd <robert@reviewer.co.uk>
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