How efficient is Dreamweaver's recordset paging?

[bmbwd]

I don't pretend to understand all of the code that Dreamweaver produces when
you set up paging through a recordset. It seems to work fine on paging a few
hundred records in pages of 10.
But what if you have really big recordsets of thousands of records?
It looks like the code creates a recordset consisting of all the records and
then tries to step through to the correct point in the set to show the 10
records you want.
Isn't this very inefficient? Shouldn't it just retrieve the correct 10 records
each time?
Or have i got the wrong end of the stick?

[David Powers]

Post by bmbwd
I don't pretend to understand all of the code that Dreamweaver produces when
you set up paging through a recordset. It seems to work fine on paging a few
hundred records in pages of 10.
But what if you have really big recordsets of thousands of records?

You don't say which server model you're using, but the PHP/MySQL
recordset paging retrieves only those records that it actually displays.
So it's retrieving just 10 records at a time. I'm fairly sure that the
CF and ASP versions work the same.

[bmbwd]

Sorry, should've said i was using the ASP server model.
I've just set up a test page that has a query to a table of 95,000 records.
Displayed using the "Dynamic Data : Dynamic Table" set to show 10 records at a
time and then applied the "Recordset Paging : Recordset Navigation Bar".

The code that is produced is (sorry it's rather long)...

in fact too long to be allowed to post here!
You can see it at...

http://www.bmbwebdesign.com/dw-recordset-paging-asp.txt

That looks to me as though it is retrieving the whole table and then stepping
through the recordset before displaying 10 out of the 95,000 records.

[David Powers]

Post by bmbwd
That looks to me as though it is retrieving the whole table and then stepping
through the recordset before displaying 10 out of the 95,000 records.

It's eight years since I last used ASP, and looking at that code
reminded me why I switched. As far as I can see, the ASP code gets all
records and then displays just a select few. Very inefficient.

The PHP code adds a LIMIT clause to the SQL query, which tells the
database to return only those results that will actually be used. It's
fast and efficient.

[bmbwd]

Just done a further test and recreated the exact same page using PHP server
model, again, i don't follow exactly what the code is doing, but there must be
something different because the PHP version is almost instantaneous when
clicking the links to page thru the records whilst the ASP version takes at
least 3 or 4 seconds each time.
BTW this is all done locally using my development machine, so there's no lag
for transporting the info over the web.

[Dooza]

Post by bmbwd
Just done a further test and recreated the exact same page using PHP server
model, again, i don't follow exactly what the code is doing, but there must be
something different because the PHP version is almost instantaneous when
clicking the links to page thru the records whilst the ASP version takes at
least 3 or 4 seconds each time.
BTW this is all done locally using my development machine, so there's no lag
for transporting the info over the web.

Yes ASP recordset paging it terribly inefficient, and there is very
little you can do about it.

One option (if you are using SQL) is to use a stored procedure, but its
very complicated, and there are many ways to do it. If you have SQL
2005/2008 its much simpler, but still not easy.

I have got it to work under SQL2000 but its still not as fast as I would
like.

Dooza

[Julian Roberts]

Post by Dooza

Post by bmbwd
Just done a further test and recreated the exact same page using PHP
server model, again, i don't follow exactly what the code is doing,
but there must be something different because the PHP version is
almost instantaneous when clicking the links to page thru the records
whilst the ASP version takes at least 3 or 4 seconds each time.
BTW this is all done locally using my development machine, so there's
no lag for transporting the info over the web.

Yes ASP recordset paging it terribly inefficient, and there is very
little you can do about it.
One option (if you are using SQL) is to use a stored procedure, but its
very complicated, and there are many ways to do it. If you have SQL
2005/2008 its much simpler, but still not easy.
I have got it to work under SQL2000 but its still not as fast as I would
like.
Dooza

Yes, paging in ASP is a little convoluted. Stored procedures is probably
the best way to go. Depends on the size of the recordset. Usually, one
would filter it down to get a manageable size.

The advent of LINQ in ASP.NET 3.5 makes life a lot easier. One can have
code like

rpt.DataSource=(From P in DB.Products).Skip(20).Take(10)

[bmbwd]

Thanks for the info guys. Luckily the project i'm working on is using classic
ASP and MySQL, so i've pinched the "LIMIT" ideas from the PHP/MySQL code and
used them in my ASP code.
It seems to work fine and is much faster :-)

Would it be too much to hope that MS Access and MS-SQL had something
equivalent to "LIMIT"?

[Massimo Foti]

Post by bmbwd
Would it be too much to hope that MS Access and MS-SQL had something
equivalent to "LIMIT"?

MS SQL Server 2005+ can use "Window functions" for that. The code snippet
below, coming from O'Reilly SQL Cookbook (recommended reading), works on SQL
Server, Oracle and DB 2:

select sal
from (
select row_number( ) over (order by sal) as rn,
sal
from emp
) x
where rn between 1 and 5

The book:
http://oreilly.com/catalog/9780596009762/index.html

PostgreSQL supports LIMIT, just like MySQL (it's part of SQL's ANSI
standard)

As for MS Access, is a useful tool, but it shouldn't be used for website
anyway

----------------------------
Massimo Foti, web-programmer for hire
Tools for ColdFusion, JavaScript and Dreamweaver:
http://www.massimocorner.com
----------------------------

[Dooza]

Post by Massimo Foti

Post by bmbwd
Would it be too much to hope that MS Access and MS-SQL had something
equivalent to "LIMIT"?

MS SQL Server 2005+ can use "Window functions" for that. The code snippet
below, coming from O'Reilly SQL Cookbook (recommended reading), works on SQL
select sal
from (
select row_number( ) over (order by sal) as rn,
sal
from emp
) x
where rn between 1 and 5
http://oreilly.com/catalog/9780596009762/index.html
PostgreSQL supports LIMIT, just like MySQL (it's part of SQL's ANSI
standard)

here is an interesting discussion about it:
http://www.singingeels.com/Articles/Pagination_In_SQL_Server_2005.aspx

Post by Massimo Foti
As for MS Access, is a useful tool, but it shouldn't be used for website
anyway

Completely agree, its not butch enough for the job :)

Dooza

[Massimo Foti]

Post by Dooza
http://www.singingeels.com/Articles/Pagination_In_SQL_Server_2005.aspx

I agree with the first comment on that page, using a mix of TOP and WHERE
makes it hard to read. I very much prefer BETWEEN in the WHERE clause

Massimo

[a***@gmail.com]

I know I'm kindof late to the party.. but SQL 2012 has new OFFSET and FETCH functionality similiar to the LIMIT clause in mySQL

Post by Massimo Foti

Post by bmbwd
Would it be too much to hope that MS Access and MS-SQL had something
equivalent to "LIMIT"?

MS SQL Server 2005+ can use "Window functions" for that. The code snippet
below, coming from O'Reilly SQL Cookbook (recommended reading), works on SQL
select sal
from (
select row_number( ) over (order by sal) as rn,
sal
from emp
) x
where rn between 1 and 5
http://oreilly.com/catalog/9780596009762/index.html
PostgreSQL supports LIMIT, just like MySQL (it's part of SQL's ANSI
standard)
As for MS Access, is a useful tool, but it shouldn't be used for website
anyway
----------------------------
Massimo Foti, web-programmer for hire
http://www.massimocorner.com
----------------------------