It will become visible slower when the offset increases because you have to iterate through the previous rows.
The nice Python syntax hides this detail but the driver has do it.
This is because the cursor is a forward cursor and you have to iterate row by row.
Even when using scrollable cursors instead of a regular select it's the same situation, opening the cursor is slow because the server will have to prepare a big result set.
I wish it was easier and have just generic database code in the framework but in the end you'll have to run that kind of query to achieve at least some decent performance.
Regards,
Dan.
On 7/21/06, DavidA <[EMAIL PROTECTED]> wrote:
> > On Thu, 2006-07-20 at 17:34 -0400, Dan Hristodorescu wrote:
> > >
> > > and for SQL 2000 should look like this:
> > >
> > > SELECT fields FROM table
> > > WHERE primary key IN
> > > (SELECT TOP limit primary_key FROM table
> > > WHERE primary_key NOT IN
> > > (SELECT TOP offset primary_key FROM table
> > > WHERE filter_conditions
> > > ORDER BY sort_field)
> > > AND filter_criteria
> > > ORDER BY sort_field)
> > > ORDER BY sort_field
> > >
> > > And with join tables it looks completely crazy (I've only used it
> > > using DISTINCT with joins), but that's the optimal way to do.
> > >
Since its so complicated for SQL 2000, couldn't you just cheat a bit
and do some in SQL and some in Python?
SELECT TOP limit+offset FROM table ...
and then in the backend:
return cursor.fetchall()[offset:]
(OK, I know its more complicated than that, but you get the idea).
That's typically the way I've seen paging done with SQL 2k in the past.
-Dave
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django developers" group.
To post to this group, send email to django-developers@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/django-developers
-~----------~----~----~----~------~----~------~--~---
