Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»

Patindex Expand / Collapse
Author
Message
Posted Tuesday, September 16, 2008 6:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:11 AM
Points: 36,995, Visits: 31,516
dphillips (9/16/2008)
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!


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. :D


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #570697
Posted Tuesday, September 16, 2008 6:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:11 AM
Points: 36,995, Visits: 31,516
Manie, I gotta say it again... good article. You brought some good people out in the discussion about PatIndex. Nicely done! :)

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #570698
Posted Wednesday, September 17, 2008 8:29 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
Jeff Moden (9/16/2008)
... 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. :) ...

Sort of a Database Mythbusters, eh? :D

(my wife was on Mythbusters a couple of weeks ago, that was cool)
Post #571046
Posted Wednesday, September 17, 2008 8:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:59 AM
Points: 2,670, Visits: 19,241
Wayne West (9/17/2008)
Jeff Moden (9/16/2008)
... 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. :) ...

Sort of a Database Mythbusters, eh? :D

(my wife was on Mythbusters a couple of weeks ago, that was cool)


Oh, man, talk about a setup for a wise crack. What myth about you was your wife busting, exactly?


---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Post #571054
Posted Wednesday, September 17, 2008 8:44 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
It 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.
Post #571057
Posted Wednesday, September 17, 2008 12:52 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:34 AM
Points: 1,566, Visits: 1,851
Cool! My daughter and I watched that show and were especially impressed with that segment! Awesome!
Post #571255
Posted Wednesday, September 17, 2008 2:09 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
It'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, APOLLO, 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.
Post #571311
Posted Wednesday, September 17, 2008 6:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:11 AM
Points: 36,995, Visits: 31,516
Wayne West (9/17/2008)
Jeff Moden (9/16/2008)
... 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. :) ...

Sort of a Database Mythbusters, eh? :D

(my wife was on Mythbusters a couple of weeks ago, that was cool)


Heh... yeah... kind of in the vein. :P

Hey, 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.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #571386
Posted Monday, September 22, 2008 6:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:21 AM
Points: 1,205, Visits: 923
Hi 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.


(1000000 row(s) affected)
********************************************************************************
Found, no index
********************************************************************************
===== Far Right =====
--------------CharIndex

SQL 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 %aa

SQL Server Execution Times:
CPU time = 2641 ms, elapsed time = 1373 ms.
--------------Right

SQL Server Execution Times:
CPU time = 640 ms, elapsed time = 318 ms.
--------------Substring

SQL 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 %aa

SQL Server Execution Times:
CPU time = 2656 ms, elapsed time = 1428 ms.
================================================================================
===== Far Left =====
--------------CharIndex

SQL 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.
--------------Left

SQL Server Execution Times:
CPU time = 469 ms, elapsed time = 461 ms.
--------------Substring

SQL 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 =====
--------------CharIndex

SQL 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.
--------------Substring

SQL 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 =====
--------------CharIndex

SQL 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 %aa

SQL Server Execution Times:
CPU time = 2188 ms, elapsed time = 1089 ms.
--------------Right

SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 122 ms.
--------------Substring

SQL 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 %aa

SQL Server Execution Times:
CPU time = 2188 ms, elapsed time = 1087 ms.
================================================================================
===== Far Left =====
--------------CharIndex

SQL 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.
--------------Left

SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 128 ms.
--------------Substring

SQL 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 =====
--------------CharIndex

SQL 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.
--------------Substring

SQL 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 =====
--------------CharIndex

SQL 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 %aa

SQL Server Execution Times:
CPU time = 2610 ms, elapsed time = 1375 ms.
--------------Right

SQL Server Execution Times:
CPU time = 624 ms, elapsed time = 322 ms.
--------------Substring

SQL 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 %aa

SQL Server Execution Times:
CPU time = 2657 ms, elapsed time = 1354 ms.
================================================================================
===== Far Left =====
--------------CharIndex

SQL 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.
--------------Left

SQL Server Execution Times:
CPU time = 453 ms, elapsed time = 458 ms.
--------------Substring

SQL 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 =====
--------------CharIndex

SQL 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.
--------------Substring

SQL 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 =====
--------------CharIndex

SQL 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 %aa

SQL Server Execution Times:
CPU time = 2219 ms, elapsed time = 1103 ms.
--------------Right

SQL Server Execution Times:
CPU time = 219 ms, elapsed time = 115 ms.
--------------Substring

SQL 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 %aa

SQL Server Execution Times:
CPU time = 2141 ms, elapsed time = 1077 ms.
================================================================================
===== Far Left =====
--------------CharIndex

SQL 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.
--------------Left

SQL Server Execution Times:
CPU time = 220 ms, elapsed time = 120 ms.
--------------Substring

SQL 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 =====
--------------CharIndex

SQL 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.
--------------Substring

SQL 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.
================================================================================

Thanks for all the input to this article guys 'n gals!


Manie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #573407
Posted Monday, September 22, 2008 6:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:21 AM
Points: 1,205, Visits: 923
Sorry, 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;)


Manie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #573410
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse