[thelist] SQL-What is quicker?

[Judah McAuley]

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