Re: [SQL] OFFSET impact on Performance???

Alex wrote:
> How do you create a temporary view that has only a small subset of the
> data from the DB init?  (Links to docs are fine - I can read ;).  My
> query isn't all that complex, and my number of records might be from
> 10 to 2k depending on how I implement it.

Well, you can't.  My point was that the traditional query/view approach
is often more appropriate for these cases.

Cursors are really designed to provide an in-transaction working set.
Because of this, they provide the luxury of absolute addressing which is
normally impossible in SQL.

Queries allow for relative addressing, in other words 'fetch me the next
c of x based on y'.  This is a good thing, because it forces the
application developer to consider changes that happen from other users
while browsing a dataset.  Applications that don't use transactions
should not provide any guarantees about the data in between queries like
the number of records matching a certain criteria.  This is a trap that
many developers fall into, especially when coming from flat file
databases that use to allow this.  This puts particularly nasty
constraints on web application developers who are unable to hold a
transaction between page refreshes.  However this just a variant of SQL
developer trap #2, which is that you are not supposed to hold a
transaction open waiting for user input.

In your particular case IMO what you really need is a materialized view.
Currently, it is possible to rig them up in a fashion with plgsql that
may or may not meet your requirements.  Given some careful thought,
mat-views can be used to solve all kinds of nasty performance related
issues (and it all boils down to performance, otherwise we'd all just
use limit/offset).

Merlin