Performance issue with tally solution

Question

Performance issue with tally solution

Viewing 15 posts - 61 through 75 (of 522 total)

You must be logged in to reply to this topic. Login to reply

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 1

[font="Verdana"]Excellent work! Thanks Flo. Some interesting numbers there.[/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Florian Reischl (4/14/2009)
Hi everybody
Sorry for my late answer but I had a busy day at work...
Thank you for all your investigations and your time!!!
I've not been lazy. I did many tests for different cases and added each single of your posted split functions to my test environment. I'm not sure if I covered all business cases, but I did my best...

Heh... dang it...I couldn't wait to get home because I was gonna do the same thing but got home late. Absolutely awesome job, Flo. And thanks for posting the code... this is going to be more fun that trying to catch a vole while wearing silk gloves.

--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)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

I don't know what anyone else thinks, but this is a freakin' awesome thread. I don't believe such a huge collection of talent and code has ever come together on a single task ever before and the code covers the full gambit including CLR's. My hat's off to all of you SQL Ninja's and especially Florian for taking the time to put it all on the same machine and then posting the results and the test code.

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Jeff Moden (4/14/2009)
I don't know what anyone else thinks, but this is a freakin' awesome thread. I don't believe such a huge collection of talent and code has ever come together on a single task ever before and the code covers the full gambit including CLR's. My hat's off to all of you SQL Ninja's and especially Florian for taking the time to put it all on the same machine and then posting the results and the test code.

I was just thinking the same thing, Jeff! What an awesome crew!

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Jeff Moden (4/14/2009)... this is going to be more fun that trying to catch a vole while wearing silk gloves.

You have some weird hobbies, dude.

😛

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

RBarryYoung (4/14/2009)
Jeff Moden (4/14/2009)... this is going to be more fun that trying to catch a vole while wearing silk gloves.
You have some weird hobbies, dude.
😛

BWAA-HAAA!!!! At least I don't wear a helmet while I'm doing it. 😛 Of course, it wouldn't fit over the vaned tinfoil hat, anyway. :hehe:

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

I have to agree with Jeff. This was an awesome thread and it demonstrates what a community can do when it works together for a common purpose. Hats of to Flo for all the hard work put into the testing of the various solutions offered!

Everyone on this thread deserves a round of applause (or maybe the theme music from the end of the original Star Wars, unfortunately, I don't have it!).

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Lynn Pettis (4/14/2009)
Everyone on this thread deserves a round of applause (or maybe the theme music from the end of the original Star Wars, unfortunately, I don't have it!).

Lemme show you how to make a tinfoil hat with vanes in it... picks up all sorts of good stuff. 😀

--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)

Paul White SSC Guru Points: 150467 More actions · Answer 9

Hey Flo,

If I were wearing a hat (tinfoil or otherwise), it would now be off to you.

Awesome stuff as usual.

For anyone not familiar with Flo's work, I would encourage you to check out his recent postings. Admittedly, I've only been here a short while, but Flo's posts are the equal of anything I've seen so far.

Good work mate. 🙂

/Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ChiragNS One Orange Chip Points: 26137 More actions · Answer 10

AWESOME.

Sorry to go off the topic... but this just underlines the need to be able to add forum questions to Briefcase (Have already mentioned this to Steve).

"Keep Trying"

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 11

Hi!

I just can’t say how pride I am for all your laud!!

After I took all your hats back to the wardrobe I have to thank all of you. Those who did their own tests, those who brought some suggestions, those who had some improvements for the existing scripts and sure those who brought their own complete split functions!

It’s just amazing to see how peoples contribution here overshoots all I’ve ever seen!

Have a nice day!

Flo

David Burrows SSC Guru Points: 65142 More actions · Answer 12

It’s just amazing to see how peoples contribution here overshoots all I’ve ever seen!

It's what makes those people and this community so great :w00t:

Far away is close at hand in the images of elsewhere.
Anon.

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 13

Chirag (4/15/2009)
AWESOME.
Sorry to go off the topic... but this just underlines the need to be able to add forum questions to Briefcase (Have already mentioned this to Steve).

Someone (Flo?) could always put an article together - Quick summary of the problem, code for possible solutions (attributed to their respective authors) and the test results.

Of course, splitting a string is a variant of RegEx, which seems to be the main reason for using a CLR solution for something which could, in theory, be done purely in T-SQL.

Derek

Phil Factor SSC-Insane Points: 20244 More actions · Answer 14

Of course, splitting a string is a variant of RegEx, which seems to be the main reason for using a CLR solution for something which could, in theory, be done purely in T-SQL.

See the RegExSplit function in 'CLR Assembly RegEx Functions for SQL Server by Example [/url]' over on Simple-Talk. The article took me blooming ages.

Best wishes,
Phil Factor

Paul White SSC Guru Points: 150467 More actions · Answer 15

Derek Dongray (4/17/2009)
Of course, splitting a string is a variant of RegEx, which seems to be the main reason for using a CLR solution for something which could, in theory, be done purely in T-SQL.

Are you sure?

Splitting a string at a delimiter certainly involes a search, but is that "a variant" of a regular expression? Or is a regular expression "a variant" of a search?

I could equally say that splitting a string is "a variant" of a linear array search. If I was in uber-nitpick mode, I might even say that splitting a string into fixed-length chunks doesn't even go that far.

The second part of your statement seems to miss the point somewhat: the usual solution (done purely in T-SQL) is less efficient that calling string.Split() in a CLR routine.

Note that the CLR solutions posted did not use RegEx. There was a hint of XQuery in the XML version...? Perhaps that was it?

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi