InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 07:56:46 GMT20http://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxIt was my wife's first (and probably only) :P appearance on Mythbusters. Apparently they had heard of lasers being bounced off the retroreflectors and were interested in building their own telescope and laser bounce system.Needless to say, not a very viable concept. :D I don't know how much it cost to build the telescope and observatory, but I think the mirror by itself represents a million dollars US. It also takes over a year to get a mirror made when you're looking at that size.Somehow they called the observatory and eventually ended up taping there.The observatory is owned by a consortium of universities and managed by New Mexico State. Each university pays X dollars and gets Y time on the sky scheduled, but there's frequent loss of nights due to weather or equipment problems. The observatory is over 10 years old since "first light", I don't know how long it took to build out.The APOLLO laser is mainly a UCSD project, but University of Washington and Harvard are also involved along with NASA and Space Command (my wife regularly emails with a military guy whose job title is Space Battle Commander).You might be able to find the segment on the Discovery Channel web site, but I couldn't in the limited amount of time that I had to look. I know the segment was on YouTube, they used to have it linked on the observatory's web site, but it doesn't seem to be up right now.Wed, 24 Sep 2008 17:01:41 GMTWayne Westhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspx[quote][b]Manie Verster (9/22/2008)[/b][hr]Oh, Jeff please tell me where I can download that "Run SQL Fast" button. I definitely needt it!:P;)[/quote]:P Actually, there kind-of is one... use with care! ;)[img]http://www.sqlservercentral.com/Forums/Attachment1448.aspx[/img]Mon, 22 Sep 2008 18:12:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxSorry, I nearly forgot! Wayne, congrats on your wife's appearance on that program! Is she a regular or was this a first? ;)Oh, Jeff please tell me where I can download that "Run SQL Fast" button. I definitely needt it!:P;)Mon, 22 Sep 2008 06:04:48 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxHi there,I really have to say I did not think my article will have a response like this but this make me feel good and I know that it all did not go into the wind. My main purpose of this article was for people that are new at SQL to at least have something to help them. Jeff, I did your test as well and I will definitely keep the code for future reference. Here is my results and although it does not look as well as yours, it looks better than the others that I have seen.[code](1000000 row(s) affected)********************************************************************************Found, no index********************************************************************************===== Far Right =====--------------CharIndexSQL Server Execution Times: CPU time = 2092 ms, elapsed time = 1050 ms.--------------PatIndex %aa%SQL Server Execution Times: CPU time = 2626 ms, elapsed time = 1334 ms.--------------PatIndex %aaSQL Server Execution Times: CPU time = 2641 ms, elapsed time = 1373 ms.--------------RightSQL Server Execution Times: CPU time = 640 ms, elapsed time = 318 ms.--------------SubstringSQL Server Execution Times: CPU time = 641 ms, elapsed time = 329 ms.--------------Like %aa%SQL Server Execution Times: CPU time = 2031 ms, elapsed time = 1016 ms.--------------Like %aaSQL Server Execution Times: CPU time = 2656 ms, elapsed time = 1428 ms.===================================================================================== Far Left =====--------------CharIndexSQL Server Execution Times: CPU time = 641 ms, elapsed time = 478 ms.--------------PatIndex %aa%SQL Server Execution Times: CPU time = 672 ms, elapsed time = 359 ms.--------------PatIndex aa%SQL Server Execution Times: CPU time = 2906 ms, elapsed time = 1785 ms.--------------LeftSQL Server Execution Times: CPU time = 469 ms, elapsed time = 461 ms.--------------SubstringSQL Server Execution Times: CPU time = 468 ms, elapsed time = 464 ms.--------------Like %aa%SQL Server Execution Times: CPU time = 626 ms, elapsed time = 318 ms.--------------Like aa%SQL Server Execution Times: CPU time = 2937 ms, elapsed time = 1465 ms.===================================================================================== Middle =====--------------CharIndexSQL Server Execution Times: CPU time = 1375 ms, elapsed time = 693 ms.--------------PatIndex %aa%SQL Server Execution Times: CPU time = 1640 ms, elapsed time = 824 ms.--------------SubstringSQL Server Execution Times: CPU time = 688 ms, elapsed time = 367 ms.--------------Like %aa%SQL Server Execution Times: CPU time = 1313 ms, elapsed time = 666 ms.================================================================================********************************************************************************NOT Found, no index********************************************************************************===== Far Right =====--------------CharIndexSQL Server Execution Times: CPU time = 1625 ms, elapsed time = 822 ms.--------------PatIndex %aa%SQL Server Execution Times: CPU time = 2156 ms, elapsed time = 1078 ms.--------------PatIndex %aaSQL Server Execution Times: CPU time = 2188 ms, elapsed time = 1089 ms.--------------RightSQL Server Execution Times: CPU time = 250 ms, elapsed time = 122 ms.--------------SubstringSQL Server Execution Times: CPU time = 250 ms, elapsed time = 129 ms.--------------Like %aa%SQL Server Execution Times: CPU time = 1624 ms, elapsed time = 808 ms.--------------Like %aaSQL Server Execution Times: CPU time = 2188 ms, elapsed time = 1087 ms.===================================================================================== Far Left =====--------------CharIndexSQL Server Execution Times: CPU time = 1641 ms, elapsed time = 839 ms.--------------PatIndex %aa%SQL Server Execution Times: CPU time = 2125 ms, elapsed time = 1075 ms.--------------PatIndex aa%SQL Server Execution Times: CPU time = 2156 ms, elapsed time = 1090 ms.--------------LeftSQL Server Execution Times: CPU time = 250 ms, elapsed time = 128 ms.--------------SubstringSQL Server Execution Times: CPU time = 250 ms, elapsed time = 132 ms.--------------Like %aa%SQL Server Execution Times: CPU time = 1640 ms, elapsed time = 817 ms.--------------Like aa%SQL Server Execution Times: CPU time = 2188 ms, elapsed time = 1097 ms.===================================================================================== Middle =====--------------CharIndexSQL Server Execution Times: CPU time = 1656 ms, elapsed time = 822 ms.--------------PatIndex %aa%SQL Server Execution Times: CPU time = 2125 ms, elapsed time = 1073 ms.--------------SubstringSQL Server Execution Times: CPU time = 250 ms, elapsed time = 130 ms.--------------Like %aa%SQL Server Execution Times: CPU time = 1593 ms, elapsed time = 800 ms.================================================================================********************************************************************************Found, WITH index********************************************************************************===== Far Right =====--------------CharIndexSQL Server Execution Times: CPU time = 2062 ms, elapsed time = 1059 ms.--------------PatIndex %aa%SQL Server Execution Times: CPU time = 2642 ms, elapsed time = 1338 ms.--------------PatIndex %aaSQL Server Execution Times: CPU time = 2610 ms, elapsed time = 1375 ms.--------------RightSQL Server Execution Times: CPU time = 624 ms, elapsed time = 322 ms.--------------SubstringSQL Server Execution Times: CPU time = 641 ms, elapsed time = 369 ms.--------------Like %aa%SQL Server Execution Times: CPU time = 1952 ms, elapsed time = 1076 ms.--------------Like %aaSQL Server Execution Times: CPU time = 2657 ms, elapsed time = 1354 ms.===================================================================================== Far Left =====--------------CharIndexSQL Server Execution Times: CPU time = 703 ms, elapsed time = 396 ms.--------------PatIndex %aa%SQL Server Execution Times: CPU time = 719 ms, elapsed time = 352 ms.--------------PatIndex aa%SQL Server Execution Times: CPU time = 2938 ms, elapsed time = 1478 ms.--------------LeftSQL Server Execution Times: CPU time = 453 ms, elapsed time = 458 ms.--------------SubstringSQL Server Execution Times: CPU time = 453 ms, elapsed time = 458 ms.--------------Like %aa%SQL Server Execution Times: CPU time = 656 ms, elapsed time = 332 ms.--------------Like aa%SQL Server Execution Times: CPU time = 2937 ms, elapsed time = 1460 ms.===================================================================================== Middle =====--------------CharIndexSQL Server Execution Times: CPU time = 1360 ms, elapsed time = 685 ms.--------------PatIndex %aa%SQL Server Execution Times: CPU time = 1656 ms, elapsed time = 828 ms.--------------SubstringSQL Server Execution Times: CPU time = 656 ms, elapsed time = 334 ms.--------------Like %aa%SQL Server Execution Times: CPU time = 1328 ms, elapsed time = 669 ms.================================================================================********************************************************************************NOT Found, With index********************************************************************************===== Far Right =====--------------CharIndexSQL Server Execution Times: CPU time = 1625 ms, elapsed time = 810 ms.--------------PatIndex %aa%SQL Server Execution Times: CPU time = 2219 ms, elapsed time = 1118 ms.--------------PatIndex %aaSQL Server Execution Times: CPU time = 2219 ms, elapsed time = 1103 ms.--------------RightSQL Server Execution Times: CPU time = 219 ms, elapsed time = 115 ms.--------------SubstringSQL Server Execution Times: CPU time = 250 ms, elapsed time = 120 ms.--------------Like %aa%SQL Server Execution Times: CPU time = 1593 ms, elapsed time = 793 ms.--------------Like %aaSQL Server Execution Times: CPU time = 2141 ms, elapsed time = 1077 ms.===================================================================================== Far Left =====--------------CharIndexSQL Server Execution Times: CPU time = 1593 ms, elapsed time = 809 ms.--------------PatIndex %aa%SQL Server Execution Times: CPU time = 2157 ms, elapsed time = 1081 ms.--------------PatIndex aa%SQL Server Execution Times: CPU time = 2187 ms, elapsed time = 1092 ms.--------------LeftSQL Server Execution Times: CPU time = 220 ms, elapsed time = 120 ms.--------------SubstringSQL Server Execution Times: CPU time = 218 ms, elapsed time = 121 ms.--------------Like %aa%SQL Server Execution Times: CPU time = 1594 ms, elapsed time = 797 ms.--------------Like aa%SQL Server Execution Times: CPU time = 2140 ms, elapsed time = 1078 ms.===================================================================================== Middle =====--------------CharIndexSQL Server Execution Times: CPU time = 1594 ms, elapsed time = 809 ms.--------------PatIndex %aa%SQL Server Execution Times: CPU time = 2188 ms, elapsed time = 1083 ms.--------------SubstringSQL Server Execution Times: CPU time = 250 ms, elapsed time = 123 ms.--------------Like %aa%SQL Server Execution Times: CPU time = 1609 ms, elapsed time = 818 ms.================================================================================[/code]Thanks for all the input to this article guys 'n gals!Mon, 22 Sep 2008 06:00:51 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspx[quote][b]Wayne West (9/17/2008)[/b][hr][quote][b]Jeff Moden (9/16/2008)[/b][hr]... The real key is that I have a good friend that I argue with/agree with once in a while. He said something I'll never forget... "A developer must not guess. A developer must know!" On lot's of these posts I say, "Darned good question... I know what some of the myths are, what's the truth?" I write a bunch of code (lots of copy and paste in cases like this) and test the heck out of it so I know the answer for sure before I post... settles lots of arguments when you have the proof in the form of code. :) ...[/quote]Sort of a Database Mythbusters, eh? :D(my wife was on Mythbusters a couple of weeks ago, that was cool)[/quote]Heh... yeah... kind of in the vein. :PHey, congrats on your wife's appearance on MythBusters. Haven't had much time for TV lately and missed the episode. Hope they show a rerun when they have more time.Wed, 17 Sep 2008 18:07:27 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxIt's really cool tech. The retroreflectors have been up there for what, 30+ years?, and they've been bouncing lasers off them for as long as they've been there. Prior to my wife's program, [url=http://en.wikipedia.org/wiki/Apache_Point_Observatory_Lunar_Laser-ranging_Operation]APOLLO[/url], they were measuring the distance to the moon down to the centimeter level. With APOLLO, they're down to the millimeter.There are five retroreflectors on the moon, three left by Apollo, two by Russian robotic probes. One Russian reflector is unreachable, it either is not in the location that it was thought to be at, or perhaps the support brackets for the reflector broke and it's no longer properly aligned. The other Russian reflector can only be used when it's in the dark: when the sun is shining on it, apparently the mirror flexes and is out of alignment.The laser beam is 3.5 meters exiting the telescope, 2 km when it hits the moon, no idea how big it is when it gets back to earth. And they have to hit these little 1ish meter targets pretty much dead-on to get a return. They mentioned that the laser sends out a quadrillion photons per pulse, only getting a handful in return. What they didn't mention in the episode is that the laser is pulsed, sending out multiple pulses per second (I don't know the exact pulse rate), it has to pulse as the same apparatus has to listen for the return when it's not transmitting.Wed, 17 Sep 2008 14:09:42 GMTWayne Westhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxCool! My daughter and I watched that show and were especially impressed with that segment! Awesome!Wed, 17 Sep 2008 12:52:45 GMTCarla Wilson-484785http://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxIt was the episode about the moon landings were faked/conspiracies, I think it aired Labor Day weekend in the US. She was the final segment, the astronomer who shoots a laser at the moon. As it happens, she's doing one of those runs 1am Friday morning.They taped it in January, unfortunately I had to be in Phoenix that day and couldn't go to the observatory. The funny thing was that they were testing their cold weather gear (which was all crap) as they went from New Mexico to Alaska to film that special. Dunno why it took so long to air.I got a Mythbusters t-shirt out of it, they have an autographed poster at the observatory.Wed, 17 Sep 2008 08:44:10 GMTWayne Westhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspx[quote][b]Wayne West (9/17/2008)[/b][hr][quote][b]Jeff Moden (9/16/2008)[/b][hr]... The real key is that I have a good friend that I argue with/agree with once in a while. He said something I'll never forget... "A developer must not guess. A developer must know!" On lot's of these posts I say, "Darned good question... I know what some of the myths are, what's the truth?" I write a bunch of code (lots of copy and paste in cases like this) and test the heck out of it so I know the answer for sure before I post... settles lots of arguments when you have the proof in the form of code. :) ...[/quote]Sort of a Database Mythbusters, eh? :D(my wife was on Mythbusters a couple of weeks ago, that was cool)[/quote]Oh, man, talk about a setup for a wise crack. What myth about you was your wife busting, exactly? :w00t:Wed, 17 Sep 2008 08:38:14 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspx[quote][b]Jeff Moden (9/16/2008)[/b][hr]... The real key is that I have a good friend that I argue with/agree with once in a while. He said something I'll never forget... "A developer must not guess. A developer must know!" On lot's of these posts I say, "Darned good question... I know what some of the myths are, what's the truth?" I write a bunch of code (lots of copy and paste in cases like this) and test the heck out of it so I know the answer for sure before I post... settles lots of arguments when you have the proof in the form of code. :) ...[/quote]Sort of a Database Mythbusters, eh? :D(my wife was on Mythbusters a couple of weeks ago, that was cool)Wed, 17 Sep 2008 08:29:35 GMTWayne Westhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxManie, I gotta say it again... good article. You brought some good people out in the discussion about PatIndex. Nicely done! :)Tue, 16 Sep 2008 18:04:49 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspx[quote][b]dphillips (9/16/2008)[/b][hr]Jeff, great examples and test building. It amazes me you have the time to post such significant and applicable content and in such detail.Thank you for your many contributions![/quote]Thank you very much. Heh... I stay up way too late on some of these.The real key is that I have a good friend that I argue with/agree with once in a while. He said something I'll never forget... "A developer must not guess. A developer must know!" On lot's of these posts I say, "Darned good question... I know what some of the myths are, what's the truth?" I write a bunch of code (lots of copy and paste in cases like this) and test the heck out of it so I know the answer for sure before I post... settles lots of arguments when you have the proof in the form of code. :) As you can see, though, certain machines behave differently. Remi had a machine that made LIKE look much worse than it does on my machine. So, I also post my results so people can compare. If I'm lucky, someone else (couple of good folks helped in this thread) will run the same code and post their results and we all learn more than what the original question was. Give some, take some, everybody (including me) learns more than expected. :DTue, 16 Sep 2008 18:03:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxJeff, great examples and test building. It amazes me you have the time to post such significant and applicable content and in such detail.Thank you for your many contributions!Tue, 16 Sep 2008 10:01:04 GMTDPhillips-731960http://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxvibhasjog ,Please check if you are using transaction in this step in case you don't have your linked server configured for this.Tue, 16 Sep 2008 06:05:59 GMTMuhammadSiddiqihttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspx[quote][b]Carla Wilson (9/15/2008)[/b][hr]Jeff, thanks for that great test script. I like your code for building 1,000,000 row table. I'm saving this code for reference.Thanks for pointing out how much more efficient LEFT is than LIKE or CHARINDEX. (I knew that, but wasn't thinking about it.)My original WHERE clause:WHERE Address LIKE 'XX%' would be much faster asWHERE LEFT(Address,2) = 'XX'It's easy to get caught up in WHERE Col1 LIKE '%aa%'OR Col1 LIKE '%bb%OR Col1 LIKE 'xx%'Maybe this is where LIKE gets the bad rap - when the code should be using LEFT() instead.[/quote]Yep... from what I can see, LIKE %aa% is faster than either %aa or aa% and LEFT/RIGHT blows that away. Thanks for the feedback on the test table. If you have any questions on how it works, please don't hesitate to ask.Mon, 15 Sep 2008 10:05:08 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxJeff, thanks for that great test script. I like your code for building 1,000,000 row table. I'm saving this code for reference.Thanks for pointing out how much more efficient LEFT is than LIKE or CHARINDEX. (I knew that, but wasn't thinking about it.)My original WHERE clause:WHERE Address LIKE 'XX%' would be much faster asWHERE LEFT(Address,2) = 'XX'It's easy to get caught up in WHERE Col1 LIKE '%aa%'OR Col1 LIKE '%bb%OR Col1 LIKE 'xx%'Maybe this is where LIKE gets the bad rap - when the code should be using LEFT() instead.Mon, 15 Sep 2008 08:46:03 GMTCarla Wilson-484785http://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxThe article and discussion both are superb...Mon, 15 Sep 2008 01:12:21 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxHeh... "Crap happens". :PSat, 13 Sep 2008 21:50:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxHey Jeff I found the problem with the test machine... it's just crappy.I reran the test with 10 000 rows (100 times less data), and it still ran only 110 times faster than the original test.So that's all she wrote :P.Sat, 13 Sep 2008 19:46:52 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxWow... even those seem pretty long. Maybe it's cache on my machine. I've not checked but it was advertised as a "server quality" mother board with lot's of cache... I bought it because a friend at one of the local stores gave me a deal on it. I'll take a look at the BIOS and see what's going on.Sat, 13 Sep 2008 18:45:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxhmm.. With (MAXDOP=1)for SQL 2005[code]********************************************************************************Found, no index********************************************************************************===== Far Right =====--------------CharIndexSQL Server Execution Times: CPU time = 8640 ms, elapsed time = 5027 ms.--------------PatIndex %aa%SQL Server Execution Times: CPU time = 8750 ms, elapsed time = 4993 ms.--------------PatIndex %aaSQL Server Execution Times: CPU time = 8750 ms, elapsed time = 5034 ms.--------------RightSQL Server Execution Times: CPU time = 703 ms, elapsed time = 427 ms.--------------SubstringSQL Server Execution Times: CPU time = 735 ms, elapsed time = 418 ms.--------------Like %aa%SQL Server Execution Times: CPU time = 8594 ms, elapsed time = 4922 ms.--------------Like %aaSQL Server Execution Times: CPU time = 8999 ms, elapsed time = 5404 ms.===================================================================================== Far Left =====--------------CharIndexSQL Server Execution Times: CPU time = 1063 ms, elapsed time = 675 ms.--------------PatIndex %aa%SQL Server Execution Times: CPU time = 1109 ms, elapsed time = 724 ms.--------------PatIndex aa%SQL Server Execution Times: CPU time = 8969 ms, elapsed time = 5400 ms.--------------LeftSQL Server Execution Times: CPU time = 719 ms, elapsed time = 731 ms.--------------SubstringSQL Server Execution Times: CPU time = 719 ms, elapsed time = 735 ms.--------------Like %aa%SQL Server Execution Times: CPU time = 968 ms, elapsed time = 610 ms.--------------Like aa%SQL Server Execution Times: CPU time = 9000 ms, elapsed time = 5226 ms.===================================================================================== Middle =====--------------CharIndexSQL Server Execution Times: CPU time = 4610 ms, elapsed time = 2836 ms.--------------PatIndex %aa%SQL Server Execution Times: CPU time = 4874 ms, elapsed time = 2850 ms.--------------SubstringSQL Server Execution Times: CPU time = 813 ms, elapsed time = 479 ms.--------------Like %aa%SQL Server Execution Times: CPU time = 4750 ms, elapsed time = 2856 ms.================================================================================[/code]SQL 2008[code]********************************************************************************Found, no index********************************************************************************===== Far Right =====--------------CharIndex SQL Server Execution Times: CPU time = 11657 ms, elapsed time = 9175 ms.--------------PatIndex %aa% SQL Server Execution Times: CPU time = 11281 ms, elapsed time = 7517 ms.--------------PatIndex %aa SQL Server Execution Times: CPU time = 10219 ms, elapsed time = 5786 ms.--------------Right SQL Server Execution Times: CPU time = 859 ms, elapsed time = 493 ms.--------------Substring SQL Server Execution Times: CPU time = 718 ms, elapsed time = 438 ms.--------------Like %aa% SQL Server Execution Times: CPU time = 10548 ms, elapsed time = 5706 ms.--------------Like %aa SQL Server Execution Times: CPU time = 10234 ms, elapsed time = 5853 ms.===================================================================================== Far Left =====--------------CharIndex SQL Server Execution Times: CPU time = 1109 ms, elapsed time = 711 ms.--------------PatIndex %aa% SQL Server Execution Times: CPU time = 1048 ms, elapsed time = 745 ms.--------------PatIndex aa% SQL Server Execution Times: CPU time = 10577 ms, elapsed time = 5558 ms.--------------Left SQL Server Execution Times: CPU time = 781 ms, elapsed time = 812 ms.--------------Substring SQL Server Execution Times: CPU time = 812 ms, elapsed time = 818 ms.--------------Like %aa% SQL Server Execution Times: CPU time = 1001 ms, elapsed time = 660 ms.--------------Like aa% SQL Server Execution Times: CPU time = 10609 ms, elapsed time = 5585 ms.===================================================================================== Middle =====--------------CharIndex SQL Server Execution Times: CPU time = 5454 ms, elapsed time = 3206 ms.--------------PatIndex %aa% SQL Server Execution Times: CPU time = 5874 ms, elapsed time = 3229 ms.--------------Substring SQL Server Execution Times: CPU time = 797 ms, elapsed time = 450 ms.--------------Like %aa% SQL Server Execution Times: CPU time = 5531 ms, elapsed time = 3205 ms.================================================================================[/code]I have three more machines with sql 05 on them in my house, and tommorow I'll test this code on them too just to see what will happen. I could do it now but it is 2 AM here ;)Sat, 13 Sep 2008 17:26:56 GMTD.Ochttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxNow you're scaring me! :P I wonder if VM's have a problem that way? I've gotta test this at work on Monday... we have some identical boxes some of which have been VM'd and some not.Or, maybe it's a simple as parallelism... my box is a single CPU... could you add the MAXDOP 1 option to the first "group" of SELECTs and post the output from that?Sat, 13 Sep 2008 16:49:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxIt 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.[quote]Heh... I hit the "Run SQL Server FAST" button.[/quote]I wish I had one of those buttons too :DSat, 13 Sep 2008 15:53:07 GMTD.Ochttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspx[quote][b]Domagoj Orec (9/13/2008)[/b][hr]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 returnSQL 2005[/quote]Heh... I hit the "Run SQL Server FAST" button. :PI 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.Sat, 13 Sep 2008 15:39:05 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxJeff is just that good at tuning :hehe:.Sat, 13 Sep 2008 15:25:33 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxJeff 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 returnSQL 2005[code]********************************************************************************Found, no index********************************************************************************===== Far Right =====--------------CharIndexSQL 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 %aaSQL Server Execution Times: CPU time = 8782 ms, elapsed time = 6065 ms.--------------RightSQL Server Execution Times: CPU time = 703 ms, elapsed time = 411 ms.--------------SubstringSQL 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 %aaSQL Server Execution Times: CPU time = 8672 ms, elapsed time = 6026 ms.===================================================================================== Far Left =====--------------CharIndexSQL 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.--------------LeftSQL Server Execution Times: CPU time = 735 ms, elapsed time = 729 ms.--------------SubstringSQL 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 =====--------------CharIndexSQL 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.--------------SubstringSQL 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 =====--------------CharIndexSQL 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 %aaSQL Server Execution Times: CPU time = 8813 ms, elapsed time = 4748 ms.--------------RightSQL Server Execution Times: CPU time = 250 ms, elapsed time = 121 ms.--------------SubstringSQL 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 %aaSQL Server Execution Times: CPU time = 8688 ms, elapsed time = 4730 ms.===================================================================================== Far Left =====--------------CharIndexSQL 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.--------------LeftSQL Server Execution Times: CPU time = 250 ms, elapsed time = 123 ms.--------------SubstringSQL 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 =====--------------CharIndexSQL 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.--------------SubstringSQL 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 =====--------------CharIndexSQL 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 %aaSQL Server Execution Times: CPU time = 8969 ms, elapsed time = 5515 ms.--------------RightSQL Server Execution Times: CPU time = 688 ms, elapsed time = 397 ms.--------------SubstringSQL 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 %aaSQL Server Execution Times: CPU time = 8828 ms, elapsed time = 5536 ms.===================================================================================== Far Left =====--------------CharIndexSQL 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.--------------LeftSQL Server Execution Times: CPU time = 688 ms, elapsed time = 703 ms.--------------SubstringSQL 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 =====--------------CharIndexSQL 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.--------------SubstringSQL 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 =====--------------CharIndexSQL 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 %aaSQL Server Execution Times: CPU time = 8671 ms, elapsed time = 4654 ms.--------------RightSQL Server Execution Times: CPU time = 250 ms, elapsed time = 120 ms.--------------SubstringSQL 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 %aaSQL Server Execution Times: CPU time = 8751 ms, elapsed time = 4662 ms.===================================================================================== Far Left =====--------------CharIndexSQL 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.--------------LeftSQL Server Execution Times: CPU time = 235 ms, elapsed time = 127 ms.--------------SubstringSQL 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 =====--------------CharIndexSQL 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.--------------SubstringSQL 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.================================================================================[/code]and in SQL 2008[code]********************************************************************************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.================================================================================[/code]Sat, 13 Sep 2008 14:37:41 GMTD.Ochttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxS'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.Sat, 13 Sep 2008 10:19:29 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxWell 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.Sat, 13 Sep 2008 09:39:20 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspx[quote][b]Ninja's_RGR'us (9/12/2008)[/b][hr]Holly shit... is the only way to describe those results!!![/quote]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...[code]********************************************************************************Found, no index********************************************************************************===== Far Right =====--------------CharIndexSQL 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 %aaSQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 4335 ms, elapsed time = 4335 ms.--------------RightSQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 1141 ms, elapsed time = 1148 ms.--------------SubstringSQL 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 %aaSQL 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 =====--------------CharIndexSQL 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.--------------LeftSQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 1235 ms, elapsed time = 1239 ms.--------------SubstringSQL 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 =====--------------CharIndexSQL 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.--------------SubstringSQL 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 =====--------------CharIndexSQL 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 %aaSQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 3609 ms, elapsed time = 3660 ms.--------------RightSQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 485 ms, elapsed time = 488 ms.--------------SubstringSQL 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 %aaSQL 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 =====--------------CharIndexSQL 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.--------------LeftSQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 625 ms, elapsed time = 625 ms.--------------SubstringSQL 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 =====--------------CharIndexSQL 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.--------------SubstringSQL 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 =====--------------CharIndexSQL 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 %aaSQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 4354 ms, elapsed time = 4354 ms.--------------RightSQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 1125 ms, elapsed time = 1129 ms.--------------SubstringSQL 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 %aaSQL 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 =====--------------CharIndexSQL 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.--------------LeftSQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 1230 ms, elapsed time = 1230 ms.--------------SubstringSQL 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 =====--------------CharIndexSQL 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.--------------SubstringSQL 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 =====--------------CharIndexSQL 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 %aaSQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 3594 ms, elapsed time = 3634 ms.--------------RightSQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 469 ms, elapsed time = 473 ms.--------------SubstringSQL 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 %aaSQL 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 =====--------------CharIndexSQL 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.--------------LeftSQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 577 ms, elapsed time = 577 ms.--------------SubstringSQL 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 =====--------------CharIndexSQL 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.--------------SubstringSQL 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.================================================================================[/code]And, heres, the results I get on SQL Server 2005...[code]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 indexSQL 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.--------------CharIndexSQL 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 %aaSQL Server Execution Times: CPU time = 4703 ms, elapsed time = 4833 ms.--------------RightSQL Server Execution Times: CPU time = 1031 ms, elapsed time = 2924 ms.--------------SubstringSQL 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 %aaSQL Server Execution Times: CPU time = 5094 ms, elapsed time = 5384 ms.===================================================================================== Far Left =====--------------CharIndexSQL 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.--------------LeftSQL Server Execution Times: CPU time = 1219 ms, elapsed time = 2855 ms.--------------SubstringSQL 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 =====--------------CharIndexSQL 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.--------------SubstringSQL 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 =====--------------CharIndexSQL 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 %aaSQL Server Execution Times: CPU time = 4141 ms, elapsed time = 5223 ms.--------------RightSQL Server Execution Times: CPU time = 593 ms, elapsed time = 3758 ms.--------------SubstringSQL 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 %aaSQL Server Execution Times: CPU time = 4203 ms, elapsed time = 4409 ms.===================================================================================== Far Left =====--------------CharIndexSQL 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.--------------LeftSQL Server Execution Times: CPU time = 609 ms, elapsed time = 1576 ms.--------------SubstringSQL 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 =====--------------CharIndexSQL 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.--------------SubstringSQL 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 =====--------------CharIndexSQL 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 %aaSQL Server Execution Times: CPU time = 4891 ms, elapsed time = 5074 ms.--------------RightSQL Server Execution Times: CPU time = 1110 ms, elapsed time = 1128 ms.--------------SubstringSQL 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 %aaSQL Server Execution Times: CPU time = 5218 ms, elapsed time = 5468 ms.===================================================================================== Far Left =====--------------CharIndexSQL 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.--------------LeftSQL Server Execution Times: CPU time = 1172 ms, elapsed time = 1193 ms.--------------SubstringSQL 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 =====--------------CharIndexSQL 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.--------------SubstringSQL 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 =====--------------CharIndexSQL 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 %aaSQL Server Execution Times: CPU time = 4172 ms, elapsed time = 4308 ms.--------------RightSQL Server Execution Times: CPU time = 468 ms, elapsed time = 472 ms.--------------SubstringSQL 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 %aaSQL Server Execution Times: CPU time = 4453 ms, elapsed time = 4556 ms.===================================================================================== Far Left =====--------------CharIndexSQL 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.--------------LeftSQL Server Execution Times: CPU time = 563 ms, elapsed time = 602 ms.--------------SubstringSQL 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 =====--------------CharIndexSQL 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.--------------SubstringSQL 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.================================================================================[/code]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.Sat, 13 Sep 2008 09:01:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxHolly shit... is the only way to describe those results!!![code]********************************************************************************Found, no index********************************************************************************===== Far Right =====--------------CharIndexSQL 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 %aaSQL Server Execution Times: CPU time = 22468 ms, elapsed time = 22595 ms.--------------RightSQL Server Execution Times: CPU time = 922 ms, elapsed time = 925 ms.--------------SubstringSQL 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 %aaSQL Server Execution Times: CPU time = 22375 ms, elapsed time = 22615 ms.===================================================================================== Far Left =====--------------CharIndexSQL 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.--------------LeftSQL Server Execution Times: CPU time = 1015 ms, elapsed time = 1020 ms.--------------SubstringSQL 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 =====--------------CharIndexSQL 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.--------------SubstringSQL 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 =====--------------CharIndexSQL 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 %aaSQL Server Execution Times: CPU time = 23828 ms, elapsed time = 24016 ms.--------------RightSQL Server Execution Times: CPU time = 500 ms, elapsed time = 497 ms.--------------SubstringSQL 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 %aaSQL Server Execution Times: CPU time = 23719 ms, elapsed time = 24139 ms.===================================================================================== Far Left =====--------------CharIndexSQL 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.--------------LeftSQL Server Execution Times: CPU time = 500 ms, elapsed time = 509 ms.--------------SubstringSQL 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 =====--------------CharIndexSQL 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.--------------SubstringSQL 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 =====--------------CharIndexSQL 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 %aaSQL Server Execution Times: CPU time = 23734 ms, elapsed time = 24037 ms.--------------RightSQL Server Execution Times: CPU time = 953 ms, elapsed time = 994 ms.--------------SubstringSQL 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 %aaSQL Server Execution Times: CPU time = 24000 ms, elapsed time = 24198 ms.===================================================================================== Far Left =====--------------CharIndexSQL 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.--------------LeftSQL Server Execution Times: CPU time = 1125 ms, elapsed time = 1120 ms.--------------SubstringSQL 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 =====--------------CharIndexSQL 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.--------------SubstringSQL 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 =====--------------CharIndexSQL 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 %aaSQL Server Execution Times: CPU time = 21516 ms, elapsed time = 21693 ms.--------------RightSQL Server Execution Times: CPU time = 453 ms, elapsed time = 462 ms.--------------SubstringSQL 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 %aaSQL Server Execution Times: CPU time = 22594 ms, elapsed time = 22714 ms.===================================================================================== Far Left =====--------------CharIndexSQL 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.--------------LeftSQL Server Execution Times: CPU time = 516 ms, elapsed time = 532 ms.--------------SubstringSQL 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 =====--------------CharIndexSQL 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.--------------SubstringSQL 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.================================================================================[/code]Fri, 12 Sep 2008 21:15:19 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxBy 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.Fri, 12 Sep 2008 19:26:18 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspx"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...[code]--===== 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)[/code]... and then, a couple of tests ;) Come to your own conclusions... :P Output has been shunted to a variable so we testing LIKE and not the ability of the video card... :P[code]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[/code]Fri, 12 Sep 2008 19:21:44 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxJust 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 TestNameAddressSET LoadType = 'T'WHERE CHARINDEX('TEST',firstname) > 0or CHARINDEX('TEST',middlename) > 0or CHARINDEX('TEST',lastname) > 0...UPDATE TestNameAddressSET City = nullWHERE CHARINDEX('XX',City) = 1or CHARINDEX('##',City) = 1or 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.Fri, 12 Sep 2008 15:17:12 GMTCarla Wilson-484785http://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspx[quote][b]Carla Wilson (9/12/2008)[/b][hr]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.[/quote]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. :DFri, 12 Sep 2008 07:41:52 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxThanks 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.Fri, 12 Sep 2008 07:32:49 GMTCarla Wilson-484785http://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspx[quote][b]jcrawf02 (9/11/2008)[/b][hr]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:[url]http://www.sqlservercentral.com/articles/TSQL/62867/[/url], 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.[code]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 inIF OBJECT_ID('tempdb..#MyHead') IS NOT NULL BEGIN DROP TABLE #MyHead ENDcreate table #MyHead (PK int identity(1,1), monthnames varchar(255))--original solutionselect 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)))) thirdmonthfrom #concatmonth--new solutionDECLARE @currentLocation int, @currentString varchar(255)SET @currentLocation = 1SET @currentString = (SELECT monthname FROM #concatmonth)WHILE charindex(':',@currentString,@currentLocation)>0BEGIN 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 #myHeadSELECT @currentStringSELECT * FROM #MyHead ORDER BY PK[/code][/quote]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;););)Fri, 12 Sep 2008 05:44:54 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspx[quote][b]Jeff Moden (9/11/2008)[/b][hr]Manie, great primer on PatIndex![quote]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.[/quote]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][url]http://www.sqlservercentral.com/articles/TSQL/62867/[/url][font="Arial Black"]Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays[/font][url]http://www.sqlservercentral.com/articles/T-SQL/63003/[/url][/quote]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.Fri, 12 Sep 2008 00:36:46 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspx[quote][b]martin.moss (9/11/2008)[/b][hr][quote]The PATINDEX function provide you with the exact position of a pattern in the expression e.g. if you want to know where "f" is in the expression "abcdef" then the answer will be 5 using the PATINDEX function.[/quote]Returns 6 doesnt it?[/quote]So sorry, that must have been a typo but I'm sure you get the gist of what I mean.Fri, 12 Sep 2008 00:20:06 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxManie, great primer on PatIndex![quote]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.[/quote]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][url]http://www.sqlservercentral.com/articles/TSQL/62867/[/url][font="Arial Black"]Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays[/font][url]http://www.sqlservercentral.com/articles/T-SQL/63003/[/url]Thu, 11 Sep 2008 19:35:06 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic567477-1380-1.aspxManie, good article! Myself, I don't think that I've ever used patindex, but I use the other string functions heavily. It's amazing how many people don't do string manipulation: I recently taught a group of skilled programmers an Intro to SQL Server/Database Theory class (they were Cobol & Oracle users mainly) and they were amazed what I could do with basic string manipulation.Thu, 11 Sep 2008 10:14:57 GMTWayne West