At 12:05 PM 3/5/98 +0000, Wouter Keyser wrote:
>Which is fastest: Cursors of select loops?

Answer #1: select loops.  (but not by the margin that used to exist
early in version 6.)   I haven't done much with cursors for years, but
as far as I know the main difference now is that cursor queries can't
be REPEATED.  They prefetch just like select loops do.

Answer #2: neither.  Find a way to do the work all in one or two big
juicy queries.  

Karl R. Schendel, Jr.
K/B Computer Associates   schendel@kbcomputer.com
Ingres and Unix Expertise


Hello Wouter,

First it is important to distinguish between the type of cursor being 
used.  A cursor is updatable by default (even if the "for update 
of ..." syntax is not present).  Exclusive locks are taken (OI 2.0 
offers a few new locking options such as using the "cursor
stability" mode). This can introduce concurrency problems which has
the potential to cause delays for your query and/or other queries 
(due to lock waits).  Also, readonly cursors will perform group 
reads rather than single page reads. Additionally, there is increased
GCA traffic due to the row at a time nature of the retrieval.

A readonly cursor (where the cursor is opened "for readonly",
regardless of how it was declared) will attempt to read multiple
rows of data with each read (tunable by setting prefetchrows).
This minimizes the GCA traffic and shared locks (by default)
are used.  This improves both performance and concurrency.

The select loop is the fastest way to retrieve data but is limited
in functionality and is non-standard.  It is pretty easy to 
benchmark the performance of each of these.  Generally you
will find that the readonly cursor is close enough to the select
loop in performance that it meets most performance needs.

I hope that this helps.

Chip Nickolett           ChipN@Comp-Soln.com
Comprehensive Consulting Solutions, Inc.   (www.Comp-Soln.com)
Phone:  262-544-9954     Fax:  262-544-1236

Ingres Q & A

Back to William's Home Page

© William Yuan 2000

Email William