Patindex
-
Cool! My daughter and I watched that show and were especially impressed with that segment! Awesome!
-
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.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
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? 😀
(my wife was on Mythbusters a couple of weeks ago, that was cool)
Heh... yeah... kind of in the vein. 😛
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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!
:-PManie Verster
Developer
Johannesburg
South AfricaI 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) -
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;)
:-PManie Verster
Developer
Johannesburg
South AfricaI 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) -
Manie Verster (9/22/2008)
Oh, Jeff please tell me where I can download that "Run SQL Fast" button. I definitely needt it!:P;)😛 Actually, there kind-of is one... use with care! 😉
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It was my wife's first (and probably only) 😛 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. 😀 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.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 7 posts - 46 through 51 (of 51 total)
You must be logged in to reply to this topic. Login to reply