September 12, 2008 at 12:36 am
Jeff Moden (9/11/2008)
Manie, great primer on PatIndex!There is hundreds of other ways to use these functions and the way I used here is most probably not the best way so, if anyone out there have any suggestions then I'm always open to new ideas. Do a WHILE loop to strip the months and insert them into one column with multiple rows.
Ok... here's a suggestion... I'd recommend NOT using a WHILE loop to parse rows. Here's why...
[font="Arial Black"]The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/font]
http://qa.sqlservercentral.com/articles/TSQL/62867/
[font="Arial Black"]Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays[/font]
Wayne and Jeff and all the others with positive feedback,
Thanks a lot for your positive feedback. This morning when I opened my e-mail and saw that my article wish published, my heart was in my throat. This is my first ever article and I am feeling very proud. It makes me feel good that guys like you gives such nice feedback. Jeff, I have taken note of your suggestion and will definitely consider using it.
Thanks to SCC for this opportunity.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
September 12, 2008 at 5:44 am
jcrawf02 (9/11/2008)
Your solution works if the original set is small, like your example of 'January:February:March', but as you pointed out, gets ugly the larger your string gets.Two things -
1) use CHARINDEX, not PATINDEX, because you can specify a starting point in CHARINDEX. Then it's just a matter of finding the previous ':' and going from there. By the way, you don't actually need the '%' in your search string when using CHARINDEX, you can just search for CHARINDEX(':',monthname)
2) you can use a loop to find these values (see below) but the best way to do this, especially if you have either an undetermined length or a large value, is to use a Tally or Numbers table. See Jeff Moden's article on Tally tables here:http://qa.sqlservercentral.com/articles/TSQL/62867/, including splitting a string.
Here's a sample loop that will accomplish this for the year's worth of months, not nearly as efficient as a tally table, but does the job.
IF OBJECT_ID('tempdb..#concatmonth') IS NOT NULL BEGIN DROP TABLE #concatmonth ENDcreate table #concatmonth(monthname varchar(255))
insert #concatmonth(monthname)select 'January:February:March:April:May:June:July:August:September:October:November:December'
-- create a table to store results in
IF OBJECT_ID('tempdb..#MyHead') IS NOT NULL BEGIN DROP TABLE #MyHead END
create table #MyHead (PK int identity(1,1),
monthnames varchar(255))
--original solution
select monthname, LEFT(monthname,PATINDEX('%:%',monthname)-1) as firstmonth,
SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN(monthname)) secondpart,
LEFT(SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN(monthname)),PATINDEX('%:%',SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN(monthname)))-1) secondmonth,
SUBSTRING(SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN(monthname)),PATINDEX('%:%',SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN( monthname)))+1,LEN(SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN(monthname)))) thirdmonth
from #concatmonth
--new solution
DECLARE @currentLocation int, @currentString varchar(255)
SET @currentLocation = 1
SET @currentString = (SELECT monthname FROM #concatmonth)
WHILE charindex(':',@currentString,@currentLocation)>0
BEGIN
INSERT INTO #MyHead
SELECT substring(@currentString,1,charindex(':',@currentString,@currentLocation)-1)
SELECT @currentString = substring(@currentString,charindex(':',@currentString,@currentLocation)+1,len(@currentString)-charindex(':',@currentString,@currentLocation)+1)
END
-- one last time to catch last iteration without ending ':'
INSERT INTO #myHead
SELECT @currentString
SELECT * FROM #MyHead ORDER BY PK
You are quite right jcrawf02. I decided test your theory about patindex and charindex and also include a check on the like as well. I created a query to count the number of rows (+-29000) with like, patindex and charindex and I must say that the difference in duration was very visible. The like and the patindex both gave me 33 milliseconds where the charindex gave me 13 milliseconds. I was a bit apprehensive about your post at first but now I am thankful. I have learned how to tune my stored procedure thanks to a part of Jeff Moden's Tally table method and also not to take all functions and methods at face value.
Thanks a lot.:P:P:P:P;););)
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
September 12, 2008 at 7:32 am
Thanks for testing and comparing the performance of PATINDEX, LIKE and CHARINDEX! You've inspired me to test some of my procedures. Looks like they could benefit from using CHARINDEX instead of LIKE.
September 12, 2008 at 7:41 am
Carla Wilson (9/12/2008)
Thanks for testing and comparing the performance of PATINDEX, LIKE and CHARINDEX! You've inspired me to test some of my procedures. Looks like they could benefit from using CHARINDEX instead of LIKE.
I'll second that 'Thank you'; my lazy butt hadn't even gone far enough to compare them, I just didn't like looking at all of the code as the string got longer. 😀
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 12, 2008 at 3:17 pm
Just wanted to let you know I tested the three different string functions, LIKE, CHARINDEX and PATINDEX, on a script I have to do data cleansing on name and address fields.
Example of part of the script:
UPDATE TestNameAddress
SET LoadType = 'T'
WHERE
CHARINDEX('TEST',firstname) > 0
or CHARINDEX('TEST',middlename) > 0
or CHARINDEX('TEST',lastname) > 0
...
UPDATE TestNameAddress
SET City = null
WHERE
CHARINDEX('XX',City) = 1
or CHARINDEX('##',City) = 1
or CHARINDEX('**',City) = 1)
...
(You get the idea)
On a test table with 21,000 rows, and values loaded into each column that would match the criteria, here are the average times (with consistent results from trial to trial).
LIKE: 2736 millisec.
CHARINDEX: 2780 millisec.
PATINDEX: 2912 millisec.
To remove the impact of the actual UPDATE on the times, I reran each script with 21,000 rows that would not match the criteria.
LIKE: 780 millisec.
CHARINDEX: 810 millisec.
PATINDEX: 960 millisec.
I was surprised to see the performance of LIKE, since I have heard it is not the most efficient.
September 12, 2008 at 7:21 pm
"LIKE" has gotten a pretty bad reputation just by word of mouth. Might be left overs from the early days of SQL. Glad to see some folks testing stuff instead of just taking other people's word for it.
Speaking of tests, one million row test coming right up....
First, the test data...
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'
-- for all rows.
-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)
-- Jeff Moden
SELECT TOP 1000000
RowNum = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
SomeHex12 = RIGHT(NEWID(),12)
INTO dbo.JBMTest
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned
-- Takes about 1 second to execute.
ALTER TABLE dbo.JBMTest
ADD PRIMARY KEY CLUSTERED (RowNum)
... and then, a couple of tests 😉 Come to your own conclusions... 😛 Output has been shunted to a variable so we testing LIKE and not the ability of the video card... 😛
DECLARE @RowNum INT
PRINT REPLICATE('*',80)
PRINT 'Found, no index'
PRINT REPLICATE('*',80)
PRINT '===== Far Right ====='
PRINT REPLICATE('-',14)+'CharIndex' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE CHARINDEX('10',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'PatIndex %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE PATINDEX('%10%',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'PatIndex %aa' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE PATINDEX('%10',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Right' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE RIGHT(SomeCsv,2) = '10' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Substring' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SUBSTRING(SomeCsv,68,2) = '10' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Like %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SomeCsv LIKE '%10%' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Like %aa' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SomeCsv LIKE '%10' SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '===== Far Left ====='
PRINT REPLICATE('-',14)+'CharIndex' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE CHARINDEX('Pa',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'PatIndex %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE PATINDEX('%Pa%',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'PatIndex aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE PATINDEX('%Pa',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Left' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE LEFT(SomeCsv,2) = 'Pa' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Substring' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SUBSTRING(SomeCsv,1,2) = 'Pa' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Like %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SomeCsv LIKE '%Pa%' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Like aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SomeCsv LIKE '%Pa' SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '===== Middle ====='
PRINT REPLICATE('-',14)+'CharIndex' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE CHARINDEX('5,',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'PatIndex %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE PATINDEX('%5,%',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Substring' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SUBSTRING(SomeCsv,34,2) = '5,' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Like %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SomeCsv LIKE '%5,%' SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
PRINT REPLICATE('*',80)
PRINT 'NOT Found, no index'
PRINT REPLICATE('*',80)
PRINT '===== Far Right ====='
PRINT REPLICATE('-',14)+'CharIndex' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE CHARINDEX('XX',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'PatIndex %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE PATINDEX('%XX%',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'PatIndex %aa' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE PATINDEX('%XX',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Right' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE RIGHT(SomeCsv,2) = 'XX' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Substring' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SUBSTRING(SomeCsv,68,2) = 'XX' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Like %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SomeCsv LIKE '%XX%' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Like %aa' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SomeCsv LIKE '%XX' SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '===== Far Left ====='
PRINT REPLICATE('-',14)+'CharIndex' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE CHARINDEX('XX',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'PatIndex %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE PATINDEX('%XX%',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'PatIndex aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE PATINDEX('%XX',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Left' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE LEFT(SomeCsv,2) = 'XX' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Substring' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SUBSTRING(SomeCsv,1,2) = 'XX' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Like %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SomeCsv LIKE '%XX%' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Like aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SomeCsv LIKE '%XX' SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '===== Middle ====='
PRINT REPLICATE('-',14)+'CharIndex' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE CHARINDEX('XX',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'PatIndex %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE PATINDEX('%XX%',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Substring' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SUBSTRING(SomeCsv,34,2) = 'XX' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Like %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SomeCsv LIKE '%XX%' SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
--=====================================================================================================================================================================
CREATE INDEX IX_JBMTest_SomeCsv ON dbo.JBMTest (SomeCsv)
--=====================================================================================================================================================================
PRINT REPLICATE('*',80)
PRINT 'Found, WITH index'
PRINT REPLICATE('*',80)
PRINT '===== Far Right ====='
PRINT REPLICATE('-',14)+'CharIndex' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE CHARINDEX('10',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'PatIndex %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE PATINDEX('%10%',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'PatIndex %aa' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE PATINDEX('%10',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Right' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE RIGHT(SomeCsv,2) = '10' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Substring' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SUBSTRING(SomeCsv,68,2) = '10' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Like %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SomeCsv LIKE '%10%' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Like %aa' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SomeCsv LIKE '%10' SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '===== Far Left ====='
PRINT REPLICATE('-',14)+'CharIndex' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE CHARINDEX('Pa',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'PatIndex %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE PATINDEX('%Pa%',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'PatIndex aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE PATINDEX('%Pa',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Left' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE LEFT(SomeCsv,2) = 'Pa' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Substring' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SUBSTRING(SomeCsv,1,2) = 'Pa' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Like %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SomeCsv LIKE '%Pa%' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Like aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SomeCsv LIKE '%Pa' SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '===== Middle ====='
PRINT REPLICATE('-',14)+'CharIndex' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE CHARINDEX('5,',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'PatIndex %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE PATINDEX('%5,%',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Substring' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SUBSTRING(SomeCsv,34,2) = '5,' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Like %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SomeCsv LIKE '%5,%' SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
PRINT REPLICATE('*',80)
PRINT 'NOT Found, With index'
PRINT REPLICATE('*',80)
PRINT '===== Far Right ====='
PRINT REPLICATE('-',14)+'CharIndex' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE CHARINDEX('XX',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'PatIndex %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE PATINDEX('%XX%',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'PatIndex %aa' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE PATINDEX('%XX',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Right' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE RIGHT(SomeCsv,2) = 'XX' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Substring' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SUBSTRING(SomeCsv,68,2) = 'XX' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Like %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SomeCsv LIKE '%XX%' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Like %aa' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SomeCsv LIKE '%XX' SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '===== Far Left ====='
PRINT REPLICATE('-',14)+'CharIndex' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE CHARINDEX('XX',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'PatIndex %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE PATINDEX('%XX%',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'PatIndex aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE PATINDEX('%XX',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Left' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE LEFT(SomeCsv,2) = 'XX' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Substring' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SUBSTRING(SomeCsv,1,2) = 'XX' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Like %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SomeCsv LIKE '%XX%' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Like aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SomeCsv LIKE '%XX' SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '===== Middle ====='
PRINT REPLICATE('-',14)+'CharIndex' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE CHARINDEX('XX',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'PatIndex %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE PATINDEX('%XX%',SomeCsv) > 0 SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Substring' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SUBSTRING(SomeCsv,34,2) = 'XX' SET STATISTICS TIME OFF
PRINT REPLICATE('-',14)+'Like %aa%' SET STATISTICS TIME ON SELECT @RowNum = RowNum FROM dbo.JBMTest WHERE SomeCsv LIKE '%XX%' SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
--DROP TABLE dbo.JBMTest
--Jeff Moden
September 12, 2008 at 7:26 pm
By the way... if you actually take the time to run the code above, I think you'll be very surprised as some of the outcome.
--Jeff Moden
September 12, 2008 at 9:15 pm
Holly shit... is the only way to describe those results!!!
********************************************************************************
Found, no index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 24187 ms, elapsed time = 24650 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 22532 ms, elapsed time = 23181 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 22468 ms, elapsed time = 22595 ms.
--------------Right
SQL Server Execution Times:
CPU time = 922 ms, elapsed time = 925 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 1000 ms, elapsed time = 991 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 23407 ms, elapsed time = 23583 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 22375 ms, elapsed time = 22615 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 2093 ms, elapsed time = 2085 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 2407 ms, elapsed time = 2405 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 25625 ms, elapsed time = 25820 ms.
--------------Left
SQL Server Execution Times:
CPU time = 1015 ms, elapsed time = 1020 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 1000 ms, elapsed time = 1000 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 1938 ms, elapsed time = 1936 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 25593 ms, elapsed time = 25795 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 12844 ms, elapsed time = 12942 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 12438 ms, elapsed time = 12543 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 1000 ms, elapsed time = 1004 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 12390 ms, elapsed time = 12528 ms.
================================================================================
********************************************************************************
NOT Found, no index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 22469 ms, elapsed time = 22725 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 23672 ms, elapsed time = 23775 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 23828 ms, elapsed time = 24016 ms.
--------------Right
SQL Server Execution Times:
CPU time = 500 ms, elapsed time = 497 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 500 ms, elapsed time = 511 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 24047 ms, elapsed time = 24265 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 23719 ms, elapsed time = 24139 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 22375 ms, elapsed time = 22499 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 23703 ms, elapsed time = 23940 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 23828 ms, elapsed time = 24018 ms.
--------------Left
SQL Server Execution Times:
CPU time = 500 ms, elapsed time = 509 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 516 ms, elapsed time = 508 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 24015 ms, elapsed time = 24224 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 23656 ms, elapsed time = 23815 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 22454 ms, elapsed time = 22553 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 23734 ms, elapsed time = 23954 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 531 ms, elapsed time = 524 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 24063 ms, elapsed time = 24275 ms.
================================================================================
********************************************************************************
Found, WITH index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 24735 ms, elapsed time = 25281 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 23687 ms, elapsed time = 24361 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 23734 ms, elapsed time = 24037 ms.
--------------Right
SQL Server Execution Times:
CPU time = 953 ms, elapsed time = 994 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 1078 ms, elapsed time = 1186 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 23937 ms, elapsed time = 24074 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 24000 ms, elapsed time = 24198 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 2140 ms, elapsed time = 2144 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 2578 ms, elapsed time = 2697 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 24297 ms, elapsed time = 24509 ms.
--------------Left
SQL Server Execution Times:
CPU time = 1125 ms, elapsed time = 1120 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 1094 ms, elapsed time = 1093 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 2000 ms, elapsed time = 1991 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 24891 ms, elapsed time = 25112 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 13297 ms, elapsed time = 13379 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 13078 ms, elapsed time = 13173 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 1047 ms, elapsed time = 1053 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 12781 ms, elapsed time = 12930 ms.
================================================================================
********************************************************************************
NOT Found, With index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 23235 ms, elapsed time = 23460 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 21360 ms, elapsed time = 21490 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 21516 ms, elapsed time = 21693 ms.
--------------Right
SQL Server Execution Times:
CPU time = 453 ms, elapsed time = 462 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 515 ms, elapsed time = 517 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 22485 ms, elapsed time = 22684 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 22594 ms, elapsed time = 22714 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 23281 ms, elapsed time = 23463 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 21422 ms, elapsed time = 21633 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 21468 ms, elapsed time = 21664 ms.
--------------Left
SQL Server Execution Times:
CPU time = 516 ms, elapsed time = 532 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 563 ms, elapsed time = 556 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 22515 ms, elapsed time = 22630 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 22672 ms, elapsed time = 23333 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 23219 ms, elapsed time = 23442 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 21375 ms, elapsed time = 21570 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 531 ms, elapsed time = 531 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 22422 ms, elapsed time = 22585 ms.
================================================================================
September 13, 2008 at 9:01 am
Ninja's_RGR'us (9/12/2008)
Holly shit... is the only way to describe those results!!!
Heh... your surprise, Remi, is that your particular computer sucks at handling strings... the results you provided are WAY out of wack from what I got and there's something really wrong on your end...
I've got a six year hold P5 running at 1.8 Ghz and a gig of ram running on twin 80 gig ide harddrives.
Here's the results I get on SQL Server 2000...
********************************************************************************
Found, no index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3414 ms, elapsed time = 3414 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 4266 ms, elapsed time = 4295 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 4335 ms, elapsed time = 4335 ms.
--------------Right
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1141 ms, elapsed time = 1148 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1257 ms, elapsed time = 1257 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3328 ms, elapsed time = 3349 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 4500 ms, elapsed time = 4514 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1219 ms, elapsed time = 1237 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1140 ms, elapsed time = 1161 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 4875 ms, elapsed time = 4900 ms.
--------------Left
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1235 ms, elapsed time = 1239 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1229 ms, elapsed time = 1229 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1156 ms, elapsed time = 1158 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 5219 ms, elapsed time = 5242 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2301 ms, elapsed time = 2301 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2625 ms, elapsed time = 2638 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1234 ms, elapsed time = 1234 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2250 ms, elapsed time = 2253 ms.
================================================================================
********************************************************************************
NOT Found, no index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2768 ms, elapsed time = 2768 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3672 ms, elapsed time = 3680 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3609 ms, elapsed time = 3660 ms.
--------------Right
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 485 ms, elapsed time = 488 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 578 ms, elapsed time = 587 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2625 ms, elapsed time = 2642 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3656 ms, elapsed time = 3666 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2813 ms, elapsed time = 2814 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3578 ms, elapsed time = 3909 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3656 ms, elapsed time = 3666 ms.
--------------Left
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 625 ms, elapsed time = 625 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 594 ms, elapsed time = 596 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2684 ms, elapsed time = 2684 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3610 ms, elapsed time = 3618 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2841 ms, elapsed time = 2841 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3578 ms, elapsed time = 3628 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 609 ms, elapsed time = 609 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2609 ms, elapsed time = 2635 ms.
================================================================================
********************************************************************************
Found, WITH index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3406 ms, elapsed time = 3703 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 4266 ms, elapsed time = 4271 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 4354 ms, elapsed time = 4354 ms.
--------------Right
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1125 ms, elapsed time = 1129 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1231 ms, elapsed time = 1231 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3282 ms, elapsed time = 3290 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 4390 ms, elapsed time = 4399 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1235 ms, elapsed time = 1254 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1156 ms, elapsed time = 1173 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 5156 ms, elapsed time = 5185 ms.
--------------Left
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1230 ms, elapsed time = 1230 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1226 ms, elapsed time = 1226 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1187 ms, elapsed time = 1189 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 5204 ms, elapsed time = 5208 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2296 ms, elapsed time = 2314 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2656 ms, elapsed time = 2656 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1355 ms, elapsed time = 1355 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2245 ms, elapsed time = 2245 ms.
================================================================================
********************************************************************************
NOT Found, With index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2719 ms, elapsed time = 2731 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3557 ms, elapsed time = 3557 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3594 ms, elapsed time = 3634 ms.
--------------Right
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 469 ms, elapsed time = 473 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 562 ms, elapsed time = 576 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2625 ms, elapsed time = 2636 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3615 ms, elapsed time = 3615 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2750 ms, elapsed time = 2793 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3560 ms, elapsed time = 3560 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3672 ms, elapsed time = 3678 ms.
--------------Left
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 577 ms, elapsed time = 577 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 578 ms, elapsed time = 645 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2640 ms, elapsed time = 2640 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3609 ms, elapsed time = 3610 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2745 ms, elapsed time = 2745 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3531 ms, elapsed time = 3566 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 563 ms, elapsed time = 566 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2609 ms, elapsed time = 2620 ms.
================================================================================
And, heres, the results I get on SQL Server 2005...
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
********************************************************************************
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Found, no index
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
********************************************************************************
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
===== Far Right =====
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
--------------CharIndex
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 3454 ms, elapsed time = 5417 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 4781 ms, elapsed time = 4912 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 4703 ms, elapsed time = 4833 ms.
--------------Right
SQL Server Execution Times:
CPU time = 1031 ms, elapsed time = 2924 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 1188 ms, elapsed time = 2975 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 3234 ms, elapsed time = 3736 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 5094 ms, elapsed time = 5384 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 1031 ms, elapsed time = 2959 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 1047 ms, elapsed time = 2917 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 5406 ms, elapsed time = 5737 ms.
--------------Left
SQL Server Execution Times:
CPU time = 1219 ms, elapsed time = 2855 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 1141 ms, elapsed time = 3091 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 1062 ms, elapsed time = 2974 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 5563 ms, elapsed time = 5940 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 2453 ms, elapsed time = 2649 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 2953 ms, elapsed time = 2993 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 1203 ms, elapsed time = 1221 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 2109 ms, elapsed time = 2175 ms.
================================================================================
********************************************************************************
NOT Found, no index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 2797 ms, elapsed time = 2857 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 4297 ms, elapsed time = 4751 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 4141 ms, elapsed time = 5223 ms.
--------------Right
SQL Server Execution Times:
CPU time = 593 ms, elapsed time = 3758 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 594 ms, elapsed time = 2180 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 2641 ms, elapsed time = 3422 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 4203 ms, elapsed time = 4409 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 2797 ms, elapsed time = 4461 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 4187 ms, elapsed time = 4304 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 4219 ms, elapsed time = 5531 ms.
--------------Left
SQL Server Execution Times:
CPU time = 609 ms, elapsed time = 1576 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 579 ms, elapsed time = 868 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 2640 ms, elapsed time = 3023 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 4266 ms, elapsed time = 4751 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 2797 ms, elapsed time = 3845 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 4109 ms, elapsed time = 4127 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 547 ms, elapsed time = 538 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 2687 ms, elapsed time = 2822 ms.
================================================================================
********************************************************************************
Found, WITH index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 3312 ms, elapsed time = 5105 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 4875 ms, elapsed time = 5590 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 4891 ms, elapsed time = 5074 ms.
--------------Right
SQL Server Execution Times:
CPU time = 1110 ms, elapsed time = 1128 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 1203 ms, elapsed time = 1229 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 3344 ms, elapsed time = 3438 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 5218 ms, elapsed time = 5468 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 1344 ms, elapsed time = 1396 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 1203 ms, elapsed time = 1248 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 5360 ms, elapsed time = 5437 ms.
--------------Left
SQL Server Execution Times:
CPU time = 1172 ms, elapsed time = 1193 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 1281 ms, elapsed time = 1279 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 1063 ms, elapsed time = 1111 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 5468 ms, elapsed time = 5562 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 2297 ms, elapsed time = 2345 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 3000 ms, elapsed time = 3049 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 1219 ms, elapsed time = 1247 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 2203 ms, elapsed time = 2251 ms.
================================================================================
********************************************************************************
NOT Found, With index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 2953 ms, elapsed time = 3047 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 4266 ms, elapsed time = 4462 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 4172 ms, elapsed time = 4308 ms.
--------------Right
SQL Server Execution Times:
CPU time = 468 ms, elapsed time = 472 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 531 ms, elapsed time = 552 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 2735 ms, elapsed time = 2776 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 4453 ms, elapsed time = 4556 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 2875 ms, elapsed time = 2924 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 4172 ms, elapsed time = 4260 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 4281 ms, elapsed time = 4378 ms.
--------------Left
SQL Server Execution Times:
CPU time = 563 ms, elapsed time = 602 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 562 ms, elapsed time = 561 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 2875 ms, elapsed time = 2916 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 4235 ms, elapsed time = 4354 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 2750 ms, elapsed time = 2836 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 4188 ms, elapsed time = 4309 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 500 ms, elapsed time = 505 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 2562 ms, elapsed time = 2601 ms.
================================================================================
The "surprise" that I was hoping that everyone would notice is that LIKE %aa% isn't as bad as everyone thinks. It's usually as good as CHARINDEX and sometimes beats it. Of course, when used appropriately, LEFT, RIGHT, and SUBSTRING beat the pants off CHARINDEX.
--Jeff Moden
September 13, 2008 at 9:39 am
Well that's a 5-10 yo PC over VPN connection, running windows 2003 server and SQL 2005 + VS 2005. The problem is that there's only 512 mB of ram. It's fast enough for the work I have to do on this project, but certainly not a production server of any sorts... ever :D.
I didn't check this out, but I'm sure that there's not enough ram available to put all that data in cache, and the hds are pretty slow on that machine, compounding the problem even more.
September 13, 2008 at 10:19 am
S'ok... just proves one thing... memory is one of the best things you can do to speed up a machine. Thanks for the feedback, Remi.
--Jeff Moden
September 13, 2008 at 2:37 pm
Jeff how did you get such a low numbers?
I've run your code on my machine, core2duo E8400 with 4GB of RAM and this is what I've got in return
SQL 2005
********************************************************************************
Found, no index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 8749 ms, elapsed time = 5908 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 8702 ms, elapsed time = 6125 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 8782 ms, elapsed time = 6065 ms.
--------------Right
SQL Server Execution Times:
CPU time = 703 ms, elapsed time = 411 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 735 ms, elapsed time = 409 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 8515 ms, elapsed time = 5690 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 8672 ms, elapsed time = 6026 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 906 ms, elapsed time = 819 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 703 ms, elapsed time = 970 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 9313 ms, elapsed time = 5063 ms.
--------------Left
SQL Server Execution Times:
CPU time = 735 ms, elapsed time = 729 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 718 ms, elapsed time = 729 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 1001 ms, elapsed time = 618 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 9390 ms, elapsed time = 5003 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 4969 ms, elapsed time = 2783 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 5281 ms, elapsed time = 2814 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 812 ms, elapsed time = 422 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 4813 ms, elapsed time = 2700 ms.
================================================================================
********************************************************************************
NOT Found, no index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 8609 ms, elapsed time = 4629 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 8641 ms, elapsed time = 4620 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 8813 ms, elapsed time = 4748 ms.
--------------Right
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 121 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 128 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 8452 ms, elapsed time = 4518 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 8688 ms, elapsed time = 4730 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 8547 ms, elapsed time = 4560 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 8703 ms, elapsed time = 4615 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 8781 ms, elapsed time = 4674 ms.
--------------Left
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 123 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 123 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 8547 ms, elapsed time = 4515 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 8797 ms, elapsed time = 4711 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 8625 ms, elapsed time = 4572 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 8687 ms, elapsed time = 4673 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 125 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 8470 ms, elapsed time = 4520 ms.
================================================================================
********************************************************************************
Found, WITH index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 8469 ms, elapsed time = 5237 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 8828 ms, elapsed time = 5462 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 8969 ms, elapsed time = 5515 ms.
--------------Right
SQL Server Execution Times:
CPU time = 688 ms, elapsed time = 397 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 781 ms, elapsed time = 412 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 8672 ms, elapsed time = 5272 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 8828 ms, elapsed time = 5536 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 1062 ms, elapsed time = 628 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 1265 ms, elapsed time = 722 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 9297 ms, elapsed time = 5018 ms.
--------------Left
SQL Server Execution Times:
CPU time = 688 ms, elapsed time = 703 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 687 ms, elapsed time = 683 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 1063 ms, elapsed time = 624 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 9469 ms, elapsed time = 5066 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 4703 ms, elapsed time = 2662 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 5328 ms, elapsed time = 2978 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 812 ms, elapsed time = 416 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 4922 ms, elapsed time = 2736 ms.
================================================================================
********************************************************************************
NOT Found, With index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 8328 ms, elapsed time = 4451 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 8875 ms, elapsed time = 4747 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 8671 ms, elapsed time = 4654 ms.
--------------Right
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 120 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 126 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 8500 ms, elapsed time = 4537 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 8751 ms, elapsed time = 4662 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 8577 ms, elapsed time = 4506 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 8844 ms, elapsed time = 4706 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 8719 ms, elapsed time = 4644 ms.
--------------Left
SQL Server Execution Times:
CPU time = 235 ms, elapsed time = 127 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 126 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 8374 ms, elapsed time = 4549 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 8766 ms, elapsed time = 4736 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 8437 ms, elapsed time = 4502 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 8891 ms, elapsed time = 4714 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 127 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 8500 ms, elapsed time = 4575 ms.
================================================================================
and in SQL 2008
********************************************************************************
Found, no index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 9922 ms, elapsed time = 5388 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 10079 ms, elapsed time = 5422 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 10406 ms, elapsed time = 5450 ms.
--------------Right
SQL Server Execution Times:
CPU time = 687 ms, elapsed time = 434 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 765 ms, elapsed time = 428 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 10189 ms, elapsed time = 5509 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 10265 ms, elapsed time = 5417 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 1047 ms, elapsed time = 640 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 1109 ms, elapsed time = 712 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 10328 ms, elapsed time = 5459 ms.
--------------Left
SQL Server Execution Times:
CPU time = 859 ms, elapsed time = 833 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 829 ms, elapsed time = 833 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 985 ms, elapsed time = 637 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 10516 ms, elapsed time = 5527 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 5358 ms, elapsed time = 3259 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 5360 ms, elapsed time = 3359 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 828 ms, elapsed time = 476 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 5452 ms, elapsed time = 3292 ms.
================================================================================
********************************************************************************
NOT Found, no index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 9672 ms, elapsed time = 5267 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 9437 ms, elapsed time = 4990 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 9406 ms, elapsed time = 5162 ms.
--------------Right
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 121 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 129 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 9438 ms, elapsed time = 4973 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 9563 ms, elapsed time = 5161 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 9624 ms, elapsed time = 5069 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 9688 ms, elapsed time = 5167 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 9578 ms, elapsed time = 5049 ms.
--------------Left
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 131 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 132 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 9453 ms, elapsed time = 5121 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 9531 ms, elapsed time = 5057 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 9688 ms, elapsed time = 5203 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 9469 ms, elapsed time = 4990 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 281 ms, elapsed time = 144 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 9406 ms, elapsed time = 5106 ms.
================================================================================
********************************************************************************
Found, WITH index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 9547 ms, elapsed time = 5623 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 9531 ms, elapsed time = 5498 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 10015 ms, elapsed time = 5595 ms.
--------------Right
SQL Server Execution Times:
CPU time = 735 ms, elapsed time = 417 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 812 ms, elapsed time = 484 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 10016 ms, elapsed time = 5608 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 10030 ms, elapsed time = 5588 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 844 ms, elapsed time = 644 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 1204 ms, elapsed time = 690 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 10500 ms, elapsed time = 5617 ms.
--------------Left
SQL Server Execution Times:
CPU time = 781 ms, elapsed time = 759 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 797 ms, elapsed time = 791 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 1188 ms, elapsed time = 684 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 10469 ms, elapsed time = 5521 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 5186 ms, elapsed time = 3215 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 5235 ms, elapsed time = 3285 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 703 ms, elapsed time = 422 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 5157 ms, elapsed time = 3228 ms.
================================================================================
********************************************************************************
NOT Found, With index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 9484 ms, elapsed time = 5220 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 9672 ms, elapsed time = 5121 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 9671 ms, elapsed time = 5232 ms.
--------------Right
SQL Server Execution Times:
CPU time = 235 ms, elapsed time = 121 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 129 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 9499 ms, elapsed time = 5083 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 9610 ms, elapsed time = 5186 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 9547 ms, elapsed time = 5421 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 9547 ms, elapsed time = 5574 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 9468 ms, elapsed time = 5223 ms.
--------------Left
SQL Server Execution Times:
CPU time = 266 ms, elapsed time = 147 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 133 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 9469 ms, elapsed time = 5382 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 9516 ms, elapsed time = 5352 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 9484 ms, elapsed time = 5468 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 9407 ms, elapsed time = 5204 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 141 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 9406 ms, elapsed time = 5202 ms.
================================================================================
-------------------------------------------------------------
"It takes 15 minutes to learn the game and a lifetime to master"
"Share your knowledge. It's a way to achieve immortality."
September 13, 2008 at 3:25 pm
Jeff is just that good at tuning :hehe:.
September 13, 2008 at 3:39 pm
Domagoj Orec (9/13/2008)
Jeff how did you get such a low numbers?I've run your code on my machine, core2duo E8400 with 4GB of RAM and this is what I've got in return
SQL 2005
Heh... I hit the "Run SQL Server FAST" button. 😛
I gotta ask, is your machine a laptop? I've found that, for some unknown reason, that laptops seem to choke on the CHARINDEX and PATINDEX functions (as well as other things). Notice that the RIGHT, LEFT, and SUBSTRING functions on your box ran even faster than mine.
--Jeff Moden
September 13, 2008 at 3:53 pm
It is a desktop machine, and I'm running them as virtual machines and I gave every VM 2 GB of memory to work with.
Not sure what is wrong, it could be mem, proc or HD, who knows, but it is a new machine.
Heh... I hit the "Run SQL Server FAST" button.
I wish I had one of those buttons too 😀
-------------------------------------------------------------
"It takes 15 minutes to learn the game and a lifetime to master"
"Share your knowledge. It's a way to achieve immortality."
Viewing 15 posts - 16 through 30 (of 51 total)
You must be logged in to reply to this topic. Login to reply