[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