[thelist] SQL-What is quicker?
Judah McAuley
judah at alphashop.com
Mon Nov 20 19:20:01 CST 2000
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
More information about the thelist
mailing list