ASP Paging - ultimate solution?? you tell me...!

[Barry Lloyd]

Ok, this probably seems like a strange post but I think Ive developed the
ultimate solution to the paging (say, 10 records at a time) through a large
recordset using ASP/SQL. The reason Im posting is so anyone can suggest any
flaws with it, maybe someone out there has tried this method before.

Problem

Page through a massive recordset on a web page (served by ASP) without
having to
a) pull back all the records and persisting the recordset in some way so can
get next/previous 10 records the next time
b) create temporary tables as Ive seen in some examples (surely this is a
massive overhead everytime just to get the 10 records required???!)

i.e basically just want to tell SQL that I want records 5000 to 5010 (or
whatever) and it will return a recordset with these 10 records in it.

Added consideraiton: I need to be able to sort (both ways) on any of the
returned columns, so I cant just use the ID trick - ie. storing ID of last
returned record and getting next 10 greater than this ID etc etc

OK, my solution (Im not claiming this hasnt been done before...just Ive
never seen it suggested!)...

M=No of records required per page
N=Last record required for page (ie. for M=10, then N=10 on page 1, N=20 on
page 2 etc)
TTTTT=table/view name
FFFFF=Column list to output
CCCCC=Sort column
OOOOO=Sort order (either ASC or DESC)
ZZZZZ=Reverse sort order (i.e if OOOOO=ASC then ZZZZZ=DESC and vice-versa)
WWWWW=Any filter (WHERE clause) you may require

Just call the SQL statement...

SELECT * FROM
(SELECT TOP M *
FROM
(SELECT TOP N FFFFF
FROM TTTTT
WHERE WWWWW
ORDER BY CCCCC OOOOO)
AS TTTTT
ORDER BY CCCCC ZZZZZ)
AS TTTTTT
ORDER BY CCCCC OOOOO

Notes:
My syntax might not be exact but you should be able to get the gist.
This has to be modified slightly for the final 'page' as that might not have
M records on it - change M to be the number of records on the final page.
OOOOO and ZZZZZ are slightly more complex if you need to sort by more than 1
field - but is possible
An extra beneift of this method is you can change the sort column/sort
order/filter at any point and still return the current page based on the new
settings.

How it works...
There are 3 nested SELECT statements.
The innermost one gets the top N records in the correct order - the other 2
SELECTs just deal with discarding the unneeded (N-M) records so they arent
returned (this becomes more important when browsing higher page numbers)

Possible problems:
Cant use a SP due to all the variables - some of which cant be parameterized
in a MSSQL SP anyway! Will this make such a big difference?
How fast are the nested SELECTs? ie. if we are on page 5000 (where M=10)
then will it be slow doing the innermost SELECT (because it gets the top
50000 records) or does SQL SERVER deal with this in a clever way?

Id been really happy to hear from anyone who can suggest any possible flaws,
especially anyone who has tried something similair!!

Cheers
Barry

[Bob Barrows]

Barry Lloyd wrote:

> Just call the SQL statement...
>
> SELECT * FROM
> (SELECT TOP M *
> FROM
> (SELECT TOP N FFFFF
> FROM TTTTT
> WHERE WWWWW
> ORDER BY CCCCC OOOOO)
> AS TTTTT
> ORDER BY CCCCC ZZZZZ)
> AS TTTTTT
> ORDER BY CCCCC OOOOO
>

Dynamic sql???? Ughh!

[url]http://www.aspfaq.com/show.asp?id=2120[/url]
[url]http://www.adopenstatic.com/experiments/recordsetpaging.asp[/url]

HTH,
Bob Barrows

[Barry Lloyd]

Yeah I agree its ugly, very.

Beleive me, Ive done quite a bit of research on this and the 2 articles you
mention have come up quite a few times. In my opinion the temporary table
method is just as ugly (Ive seen quite a few variations but it boils down to
the same thing). With very large tables this has got to be a huge overhead -
copying all the records into a temp table just to read out the 10 or 20 you
need??! no? Please tell me if Im wrong. Also you need to create multiple
Stored Procs as you cant parameterize the table names, columns, sort order
etc in a single SP.

Any solution using ADO recordset paging is similarly flawed in that it has
to return the whole recordset to the web server and then just moving to the
start of the required page and reading the records - it doesnt matter if you
loop through recordset using MoveNext or use GetRows - it has this this same
drawback.

So yes I totally agree its ugly, but I think the alternatives are equally
so. This can easily be wrapped up in a component or script function (which
is what I do) or whatever so your ASP pages should be fairly clean. My main
point is really whether this is a more scalable/flexible solution than the
others without losing speed.

Cheers
Barry

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OM2vzXwgDHA.880@TK2MSFTNGP12.phx.gbl...

> Barry Lloyd wrote:

> > Just call the SQL statement...
> >
> > SELECT * FROM
> > (SELECT TOP M *
> > FROM
> > (SELECT TOP N FFFFF
> > FROM TTTTT
> > WHERE WWWWW
> > ORDER BY CCCCC OOOOO)
> > AS TTTTT
> > ORDER BY CCCCC ZZZZZ)
> > AS TTTTTT
> > ORDER BY CCCCC OOOOO
> >

> Dynamic sql???? Ughh!
>
> [url]http://www.aspfaq.com/show.asp?id=2120[/url]
> [url]http://www.adopenstatic.com/experiments/recordsetpaging.asp[/url]
>
> HTH,
> Bob Barrows
>
>

[Bob Barrows]

Barry Lloyd wrote:

> Yeah I agree its ugly, very.

And it's unparsed and uncompiled, forcing the query engine to do these
things every time it runs.
And it leaves you at the mercy of hackers using SQL Injection, unless you
take the necessary precautions described in the SQL Injection FAQ at
[url]www.sqlsecurity.com[/url].

>
> Beleive me, Ive done quite a bit of research on this and the 2
> articles you mention have come up quite a few times. In my opinion
> the temporary table method is just as ugly (Ive seen quite a few
> variations but it boils down to the same thing). With very large
> tables this has got to be a huge overhead - copying all the records
> into a temp table just to read out the 10 or 20 you need??! no?

Test it. :-)
I would say that you should not be copying the entire table into the temp
table. Intelligent usage of the WHERE clause is needed to limit the rows
being placed in the temp table.

> Please tell me if Im wrong. Also you need to create multiple Stored
> Procs as you cant parameterize the table names, columns, sort order
> etc in a single SP.

Not sure if I undestand what you are talking about here. It sounds as if you
are trying to make this too generic. Have you seen this article?
[url]http://www.algonet.se/~sommar/dyn-search.html[/url]
Take a look at this one too:
[url]http://www.algonet.se/~sommar/dynamic_sql.html[/url]

>
> Any solution using ADO recordset paging is similarly flawed in that
> it has to return the whole recordset to the web server and then just
> moving to the start of the required page and reading the records - it
> doesnt matter if you loop through recordset using MoveNext or use
> GetRows - it has this this same drawback.

Not true. Intelligent use of the CacheSize and the AbsolutePage properties
will limit the records being returned.

>
> So yes I totally agree its ugly, but I think the alternatives are
> equally so. This can easily be wrapped up in a component or script
> function (which is what I do) or whatever so your ASP pages should be
> fairly clean. My main point is really whether this is a more
> scalable/flexible solution than the others without losing speed.
>

The only way to find out is by testing, using the method Ken describes in
his article. You're only going on theory here.

Bob Barrows

[Barry Lloyd]

Thanks Bob, this is really helpful and I certainly agree with most of what
you have said. Im going to be doing some testing today and Ill hopefully be
able to puts some results up here. Im still a bit hesitant about the
Temporary table solution but Ill rethink it and the CacheSize/AbsolutePage
modifications to the ADO solution make it a lot more attractive.
However, there is still one problem, which you may be able to help with. I
definitely need to include some sort of dynamic filtering, i.e. the
table/view will be filtered depending on user input. I cant quite think how
this can be done without using dynamic SQL. Simlarly, I may need to sort the
results on different columns and in either order (ascending or descending).
I fully understand your warnings about SQL Injection security risks - thank
you - and the overhead involved with parsing/recompiling the dynamic SQL.
How could I acheive these extra requirements using either of the two other
methods?

Barry

[Bob Barrows]

> Thanks Bob, this is really helpful and I certainly agree with most of

> what you have said. Im going to be doing some testing today and Ill
> hopefully be able to puts some results up here. Im still a bit
> hesitant about the Temporary table solution but Ill rethink it and
> the CacheSize/AbsolutePage modifications to the ADO solution make it
> a lot more attractive.
> However, there is still one problem, which you may be able to help
> with. I definitely need to include some sort of dynamic filtering,
> i.e. the table/view will be filtered depending on user input. I cant
> quite think how this can be done without using dynamic SQL. Simlarly,
> I may need to sort the results on different columns and in either
> order (ascending or descending). I fully understand your warnings
> about SQL Injection security risks - thank you - and the overhead
> involved with parsing/recompiling the dynamic SQL. How could I
> acheive these extra requirements using either of the two other
> methods?
>

The two articles on Erland's site are very comprehensive. He really covers
all the bases. Make sure you read the other articles on his site as well.

The biggest problem with dynamic sql in my mind is the lack of control. You
have no way of knowing if your tables have sufficient indexes to support all
the queries your users are likely to try. You also have no way of knowing if
the indexes you do have are being used. Have you ever typed a query into
Query Analyzer and had to cancel it because it was taking too long? This is
the situation your users will be in, except that they won't be able to
cancel a long-running query, which will impact other users besides the user
who submitted the query..

Having said that, there are, of course, times when you can't avoid using
dynamic sql (dynamic crosstabs come to mind). All I am saying is that you
should exhaust other possiibilities before choosing to use it.

Bob Barrows