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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists