Ran a little test, I was curious as well. This was run against a table with
10,000 records.
Code is at the bottom.
LIKE avg time: 52ms
LEFT avg time: 55ms
Using <cfqueryparam> for the passed in value:
LIKE avg time: 5ms
LEFT avg time: 34ms
Matthew P. Smith
Web Developer, Object Oriented
----------------------------------------------------------------------------
-------<!--- set our list to loop through --->
<CFSET AlphabetList = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z">
<cfset tickOne = getTickCount()>
<!--- loop thourgh the alphabet list 100 times --->
<cfloop from="1" to="100" index="i">
<cfloop list="#AlphabetList#" index="i">
<cfquery datasource="gots">
SELECT *
FROM tbl_smsTitles
WHERE app_full_name LIKE '#i#%'
</cfquery>
</cfloop>
</cfloop>
<cfset tickTwo = getTickCount()>
<!--- compute and output the time it took --->
<cfset totalTime = int((tickTwo - tickOne)/2600)>
<cfoutput>LIKE:#totalTime#<br></cfoutput>
<cfset tickOne = getTickCount()>
<cfloop from="1" to="100" index="i">
<cfloop list="#AlphabetList#" index="i">
<cfquery datasource="gots">
SELECT *
FROM tbl_smsTitles
WHERE left(app_full_name,1) = '#i#'
</cfquery>
</cfloop>
</cfloop>
<cfset tickTwo = getTickCount()>
<cfset totalTime = int((tickTwo - tickOne)/2600)>
<cfoutput>LEFT:#totalTime#<br></cfoutput>
USING CFQUERYPARAM:<br>
<cfset tickOne = getTickCount()>
<cfloop from="1" to="100" index="i">
<cfloop list="#AlphabetList#" index="i">
<cfquery datasource="gots">
SELECT *
FROM tbl_smsTitles
WHERE app_full_name LIKE '<cfqueryparam
cfsqltype="CF_SQL_VARCHAR" value="#i#">%'
</cfquery>
</cfloop>
</cfloop>
<cfset tickTwo = getTickCount()>
<cfset totalTime = int((tickTwo - tickOne)/2600)>
<cfoutput>LIKE:#totalTime#<br></cfoutput>
<cfset tickOne = getTickCount()>
<cfloop from="1" to="100" index="i">
<cfloop list="#AlphabetList#" index="i">
<cfquery datasource="gots">
SELECT *
FROM tbl_smsTitles
WHERE left(app_full_name,1) = '<cfqueryparam
cfsqltype="CF_SQL_VARCHAR" value="#i#">'
</cfquery>
</cfloop>
</cfloop>
<cfset tickTwo = getTickCount()>
<cfset totalTime = int((tickTwo - tickOne)/2600)>
<cfoutput>LEFT:#totalTime#<br></cfoutput>
-----Original Message-----
From: Tony Schreiber [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 29, 2002 9:16 AM
To: CF-Talk
Subject: SOT: Like vs. =
While I know that like is always slower, consider this:
WHERE left(description,1) = 'A'
OR
WHERE description LIKE 'A%'
Which would be faster?
Tony Schreiber, Senior Partner Man and Machine, Limited
mailto:[EMAIL PROTECTED] http://www.technocraft.com
http://www.simplemessageboard.com ___Free Forum Software for Cold Fusion
http://www.is300.net ___________The Enthusiast's Home of the Lexus IS300
http://www.digitacamera.com ______________DigitA Camera Scripts and Tips
http://www.linklabexchange.com _____________Miata Link ECU Data Exchange
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
