How efficient is Dreamweaver's recordset paging?
bmbwd
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
> 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.
--
David Powers
Adobe Community Expert, Dreamweaver
http://foundationphp.com
bmbwd
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.
bmbwd
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.
David Powers
> 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.
Dooza
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
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html
Julian Roberts
> bmbwd wrote:
>> 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)
--
Julian Roberts
bmbwd
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
> 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
>> 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)
here is an interesting discussion about it:
http://www.singingeels.com/Articles/Pagination_In_SQL_Server_2005.aspx
> 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 :)
Massimo Foti
> 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
