Thread: Best ODBC cursor and lock types for fastest reading?
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
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
> 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