January 8, 2008 at 7:32 am
Antonio:
I must be missing something. Per your own tests, the Numbers-based string parser was 2-3 times faster than the While loop parser every time. Yet you say its speed doesn't matter. On a low-traffic, "who cares about the performance" server, sure, it wouldn't matter. But in that case, why bother doing anything about performance at all?
Also, what pre-loading of all possible values? I don't pre-load any possible values in the string parser. The Numbers table is simply used to parse the string, it doesn't have anything to do with what values are in the string.
If you look at the query, it uses the numbers to figure out the charindex positions of the delimiter character, and then uses that to select the substrings.
That's why it works for any set of values and any delimiter. You don't have to predefine anything in the string, you just need a set of numbers at least equal to the maximum length of string you want to parse. (The smaller the Numbers table, the faster the function runs, but the shorter the maximum string length.)
So, I think I'm missing something in your analysis of it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2008 at 8:02 am
I have to admit it took me a few read throughs to understand how you were using the numbers table. Its basically used as a list of positions in the string correct?
I also have to agree with G-Squared. It doesn't matter on a single query if there is an extra 1/10 of a second on the query. However I've worked on servers where the same stored procedure ran 1000 times a second. Not to mention I'm willing to be that SQL will optimize the numbers table (put it in cache) more than it will the while statement when its being run multiple times.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
January 8, 2008 at 8:34 am
GSquared:
I must be missing something. Per your own tests, the Numbers-based string parser was 2-3 times faster than the While loop parser every time. Yet you say its speed doesn't matter. On a low-traffic, "who cares about the performance" server, sure, it wouldn't matter. But in that case, why bother doing anything about performance at all?
The speed difference is only in the creation of the values. The while loop takes 3x longer than the numbers query, but with a reasonable list of values, the difference is typically 20-50ms. Since both do their work at query initiation, even a 100ms (worse case) difference is just a fraction of the overall work a typical query.
Also, what pre-loading of all possible values? I don't pre-load any possible values in the string parser. The Numbers table is simply used to parse the string, it doesn't have anything to do with what values are in the string.
Doh! I misread the code (it is pretty slick). I see now that it's using the numbers table as an indexer, not a domain of values. So, my argument about preloading values is unfounded. As I said before, I was sure I'd learn something! :w00t:
January 8, 2008 at 2:57 pm
Ah. Makes sense now. Yeah, a few miliseconds here and there probably won't matter much if the whole query takes several seconds in some cases.
I'll stick with the Numbers version, since there's no reason to not use it that I can see. (Don't make your Numbers table too big. If you need to parse huge strings, have a second function for that which works on a BigNumbers table. I made a mistake earlier when I said I populate it to 10-million. I meant 10-thousand and wasn't paying quite enough attention to what I wrote. If it ends up being used a lot, SQL will keep Numbers in the cache and queries will be quite fast. Make it SmallInt and it will take less disk and less RAM.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2008 at 3:46 pm
GSquared: With my biggest complaint nullified, I'm all for the numbers approach.
And it's just that for my usage (big decision support queries) a 50ms improvement isn't thrilling. It's like having a 50 cent off coupon for dinner... the coupon's perceived benefit all depends on if you're eating at McDonalds or Don Shulas.
January 8, 2008 at 4:05 pm
antonio.collins (1/8/2008)
GSquared: With my biggest complaint nullified, I'm all for the numbers approach.And it's just that for my usage (big decision support queries) a 50ms improvement isn't thrilling. It's like having a 50 cent off coupon for dinner... the coupon's perceived benefit all depends on if you're eating at McDonalds or Don Shulas.
And see I say that 50 cents can count quite a bit if you send one to everyone in the US :). Even some of those big querys can get run quite a bit. I would also love to see if there is a difference in the IO usage between the 2. I doubt its significant even if its there but it would still be interesting to see.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
January 8, 2008 at 4:32 pm
Just my experience... I keep a Tally table of 11k rows... sure, it covers the usual split of a VARCHAR(8000) but I use it for much more... if you consider a single day to be 1 unit, that gives you more than 30 years of days... comes in handy for a lot of things and most System DBA's don't squawk about the size. Some of them get pretty testy about fixed auxilliary date tables.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply