pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Best 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