[thelist] SQL-What is quicker?
Simon Davies
simon.davies at bigpond.com
Mon Nov 20 19:30:34 CST 2000
Uh, if you're using ADO just use the number of records per page pagesize and
make life easy for yourself. No special coding required in the SP:
'Open the connection
funOpenDb
'
sSql = "webSelectAllMerchantProductListsForPostcode " & iPostCodeID & ", " &
iAlpha
if funSelectSQL(adoRecordSet, sSql, true, false) then
if not adoRecordSet.eof and not adoRecordSet.bof then
'Paging
'Set number of records required per page
adoRecordSet.pagesize = 10
'Set logical page number, if 1 then 1-10 isplayed, if 2 then 11-20
displayed etc you have to maintain this on your prev/next page buttons
adoRecordSet.absolutepage = iPage
'Loop through the required number of records
for iRecord = 1 to adoRecordSet.pagesize
...
...
adoRecordSet.movenext
if adoRecordSet.eof then exit for
next
end if
end if
Simon
-----Original Message-----
From: thelist-admin at lists.evolt.org
[mailto:thelist-admin at lists.evolt.org]On Behalf Of Judah McAuley
Sent: Tuesday, 21 November 2000 09:20
To: thelist at lists.evolt.org
Subject: Re: [thelist] SQL-What is quicker?
At 10:59 AM 11/21/2000 +1000, you wrote:
>Hi Gang,
>
>Scenario..Search on a criteria and then show 10 records per page.
>
>What would be quicker...
>Selecting all the records that meet the criteria and then only show 10 at a
>time i.e select all then show records 0-9..select all again then show
>records 10-19 etc (using an if ()).
>or
>select records that meet criteria but use LIMIT i.e select all that meet
>LIMIT 0,10....then select again but show LIMIT 10,10...etc (the first
number
>being the offset and the second being the number of records to return).
>
>I'm thinking that the second is quicker because it doesn't have to read the
>whole table. But is it less effective because it has to find the start and
>end point each time? Or does it depend on the number of records in the
>table?
The second case would certainly decrease the amount of information pulled
from the database, so each query would be faster. However, if you have a
fast machine and a relatively small amount of data being pulled, then the
greatest overhead is actually in the opening/closing of the database
connection. In this situation, it may make more sense to read the whole
recordset into a memory variable and then loop through the variable instead
of opening new database connections.
And in response to Seth's earlier CF Val() tip...
<tip type="ColdFusion" Title="Beware of forcing number values in CF">
Always be aware of your data types. Val() assumes that your value can be
converted to an integer. If you are retrieving a number from a source
(say, an identity column) then the number may be perfectly valid but larger
than an INT and running the Val() function will not do what you expect.
Example: If you have a value that is a Numeric 18,0 and it's 15 digits long
then Val(thatnumber) will not return the 15 digit number.
</tip>
Judah
---------------------------------------
For unsubscribe and other options, including
the Tip Harvester and archive of TheList go to:
http://lists.evolt.org Workers of the Web, evolt !
More information about the thelist
mailing list