[thelist] SQL-What is quicker?

[Seth Bienek]

Hey Judah,

> <tip type="ColdFusion" Title="Beware of forcing number values in CF">

Great tip!  I learned something today!

<tip type="ColdFusion" Title="Formatting numbers">

Let's say you have a numeric value that you always want to display as, oh,
at least 5 digits long.

For example, customer number 453 should be represented as number 00453, but
a longer customer number (we'll use 8675309 in this example) should not be
truncated.

ColdFusion includes a handy function for doing this.  NumberFormat() can do
all kinds of freaky stuff with your numbers, including forcing padding, for
example:

#NumberFormat(Session.UserID, "00000")#

Will pad our customer number just so, making it at least 5 digits with 0's
as placeholders.

</tip>

Thanks Judah!

Seth

------------------------------
Seth Bienek
Solutions Development Manager
Stonebridge Technologies, Inc.
972.455.7294 tel
972.404.9754 fax
------------------------------

> -----Original Message-----
> From: thelist-admin at lists.evolt.org
> [mailto:thelist-admin at lists.evolt.org]On Behalf Of Judah McAuley
> Sent: Monday, November 20, 2000 7:20 PM
> 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 !
>