On 7/31/07, Joseph Kocherhans <[EMAIL PROTECTED]> wrote:
> Which patch did you look at and what version of Django are you using?
> You might be able to get some SQL Server support working in 3 weeks,
> but I don't think you will be able to solve the pagination problem in
> that amount of time.
Here, I attach a response from Andrzej, the pymssql maintainer, with
some suggestions for how to accomplish pagination using SQL Server.
---------- Forwarded message ----------
From: Andrzej KukuĊa <[EMAIL PROTECTED]>
Date: Mar 2, 2006 4:34 PM
...
> The common case for LIMIT/OFFSET is pagination of query results.
LIMIT/OFFSET are by no means standard approach to the problem; they
are supported only by MySQL and PostgreSQL, and are not supported by
MS SQL, Oracle or IBM DB2.
There are 3 other methods which you can use without any change in pymssql:
1) typical MSSQL method:
SELECT * FROM (
SELECT TOP num_of_interesting_rows * FROM (
SELECT TOP num_of_interesting_rows+num_rows_to_skip interesting_columns
FROM interesting_table
[WHERE ...]
ORDER BY orderby_columns ASC
) t1 ORDER BY orderby_columns DESC
) t2 ORDER BY orderby_columns ASC
in this example t1 and t2 are dummy identifiers and can be anything;
this nested SELECT is really very efficient and is used everywhere in
MS SQL world for pagination. Remember that it's optimal only when
orderby_columns are indexed.
Pay attention that orderby_columns have to be the same in all 3 ORDER
BY clauses. Of course the innermost FROM clause doesn't have to be
that simple, you still have 29 levels of query nesting, JOINS,
grouping and others.
2) CURSOR method:
DECLARE curs_name CURSOR FOR SELECT TOP total_rows ....
OPEN curs_name
FETCH RELATIVE num_rows_to_skip ...
CLOSE curs_name
Still viable. Can be used by SP that returns table.
3) SQL:2003 method: SQL Server 2005, IBM DB2 and Oracle implements the
"standard" way, i.e. ROW_NUMBER() window function.
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY orderby_columns ASC) AS rowno,
interesting_columns
FROM interesting_table
) t1
WHERE rowno > num_rows_to_skip
AND rowno <= (num_rows_to_skip+num_of_interesting_rows)
This is very powerful numbering method, just dig Google for more.
> I am considering making a substantial change to pymssql to support
> this behavior, but I don't really want to do it if you are not willing
> to merge the patch in.
...
No need to bother; pymssql doesn't need any change wrt pagination. I'd
propose to focus on virtualizing that element in Django to make use of
abovementioned methods when it works with Microsoft SQL, Oracle or IBM
DB2. It'd improve its portability across more RDBMs.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en
-~----------~----~----~----~------~----~------~--~---
