Performance issue with tally solution

Question

Performance issue with tally solution

Viewing 15 posts - 331 through 345 (of 522 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Paul White (4/29/2009)
Jeff Moden (4/29/2009)
http://www.sqlservercentral.com/articles/T-SQL/63003/
Thanks Jeff,
I have read that before, and use that approach currently.
When Adam asked for ideas to extend the string-split, this came to mind.
It would be interesting to compare the two approaches...
It's a great article by the way.
Paul

Thanks, Paul. I appreciate the feedback... didn't know you already knew about it.

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

Jeff Moden (4/29/2009)
Thanks, Paul. I appreciate the feedback... didn't know you already knew about it.

😎 I didn't know that you didn't know I knew about it...over to you 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Paul White (4/29/2009)
Jeff Moden (4/29/2009)
Thanks, Paul. I appreciate the feedback... didn't know you already knew about it.
😎 I didn't know that you didn't know I knew about it...over to you 😉

Well, I knew the you didn't know that I didn't know that your knew about it. 😛

Hmmmm.... "There's a hole in the bottom of the sea..."

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

Jeff Moden (4/29/2009)
Well, I knew that you didn't know that I didn't know that your knew about it. 😛

I knew you were going to say that...

Jeff Moden (4/29/2009)
Hmmmm.... "There's a hole in the bottom of the sea..."

There's a hole in the bottom of the sea

There's a hole, there's a hole

There's a hole in the bottom of the sea.

There's a log in the hole in the bottom of the sea

There's a log, there's a log

There's a log in the hole in the bottom of the sea.

There's a branch on the log in the hole in the bottom of the sea

There's a branch, there's a branch

There's a branch on the log in the hole in the bottom of the sea.

There's a bump on the branch on the log in the hole in the bottom of the sea

There's a bump, there's a bump

There's a bump on the branch on the log in the hole in the bottom of the sea.

There's a frog on the bump on the branch on the log in the hole in the bottom of the sea

There's a frog, there's a frog

There's a frog on the bump on the branch on the log in the hole in the bottom of the sea.

There's a tail on the frog on the bump on the branch on the log in the hole in the bottom of the sea

There's a tail, there's a tail

There's a tail on the frog on the bump on the branch on the log in the hole in the bottom of the sea.

There's a speck on the tail on the frog on the bump on the branch on the log in the hole in the

bottom of the sea

There's a speck on the tail on the frog on the bump on the branch on the log in the hole in the

bottom of the sea

There's a speck, there's a speck

There's a speck on the tail on the frog on the bump on the branch on the log in the hole in the

bottom of the sea.

There's a fleck on the speck on the tail on the frog on the bump on the branch on the log in

the hole in the bottom of the sea

There's a fleck on the speck on the tail on the frog on the bump on the branch on the log in

the hole in the bottom of the sea

There's a fleck, there's a fleck

There's a fleck on the speck on the tail on the frog on the bump on the branch on the log in

the hole in the bottom of the sea.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Heh... oh my... someone is as crazy as I. Ya gotta be to know songs like that. 😛

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

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

There's a hole in the bottom of the sea
There's a hole in the bottom of the sea
......

Which method did you use to split that prose 😉

Heh... oh my... someone is as crazy as I.

Well I geuessed you were 😀 but to have you confirm it... well :Wow:

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

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

Jeff Moden (4/29/2009)
http://www.sqlservercentral.com/articles/T-SQL/63003/

Thanks Jeff!

Great article! Am I allowed to refer to?

Greets

Flo

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

Florian Reischl (4/30/2009)
Jeff Moden (4/29/2009)
http://www.sqlservercentral.com/articles/T-SQL/63003/
Thanks Jeff!
Great article! Am I allowed to refer to?
Greets
Flo

Absolutely.

I also have some code for you on cleaning a string. I've repaired the performance problem you and I were having in conjunction with using the STUFF function. Do you have the URL of that old post you and I went through 5 or 6 weeks back? The search engine on this forum doesn't even believe that you've used the word "Tally" in any of your posts ever.

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

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

Jeff Moden (4/30/2009)
Florian Reischl (4/30/2009)
Jeff Moden (4/29/2009)
http://www.sqlservercentral.com/articles/T-SQL/63003/
Thanks Jeff!
Great article! Am I allowed to refer to?
Absolutely.

Thank you!

I also have some code for you on cleaning a string. I've repaired the performance problem you and I were having in conjunction with using the STUFF function. Do you have the URL of that old post you and I went through 5 or 6 weeks back?

We had a performance issue in conjunction with STUFF? Maybe, I'm just gone blank... :doze:

The search engine on this forum doesn't even believe that you've used the word "Tally" in any of your posts ever.

Strange. If I search for "reischl tally" I get 31 hits.

Better: Use google and search "reischl tally site:sqlservercentral.com", then you get 74 hits (which still seems to be not enough).

Greets

Flo

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

Strange. If I search for "reischl tally" I get 31 hits.
Better: Use google and search "reischl tally site:sqlservercentral.com", then you get 74 hits (which still seems to be not enough).

I think this is because the search engines are using post content, so unless someone quotes your name, you edit a post or submit an article, your name will not be searchable.

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

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

David Burrows (4/30/2009)
Which method did you use to split that prose 😉

I used a tally table of course!

😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

Florian Reischl (4/30/2009)
We had a performance issue in conjunction with STUFF? Maybe, I'm just gone blank... :doze:

Maybe it's just me and I'm thinking of the wrong person. It was a post about using the Tally table to clean a string using the ol' split and concatenate method which, of course, is going to be quite a bit slower than a loop that uses STUFF because concatenation is so slow. I figured out a really simple way to use the Tally with STUFF. The problem was that I had to figure out a way make it work in the face of a string that was constantly changing length from left to right where the actual position of the bad characters was also constantly changing during the cleaning process.

I wanted to refer to that particular thread and I can't it.

Anyway, here's the code...

[font="Courier New"]--===== Company name is in a variable

DECLARE @CompanyName VARCHAR(256)

SELECT @CompanyName = 'A & B Cleaners'

PRINT @CompanyName --Just for verification... you can remove this line

--===== Using a "Tally" table as a loop driver, remove all characters that

-- are NOT in the in range of A to Z (upper or lower case)

SELECT @CompanyName = STUFF(@CompanyName,PATINDEX('%[^A-Z]%',@CompanyName),1,'')

FROM dbo.Tally t

WHERE t.N <= LEN(@CompanyName)

AND SUBSTRING(@CompanyName,t.N,1) LIKE '[^A-Z]'

--===== Grab just the left six characters of what remains.

SELECT @CompanyName = LEFT(@CompanyName,6)

--===== Display the result (just for verification... you can remove this line)

PRINT @CompanyName[/font]

--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: 1004363 More actions · Answer 13

Almost forgot... the reason why I don't use the code windows anymore is because, although they may work fine with FireFox, they suck when using IE7. The remove blank lines on insert and add blank lines while removing leading space when copying. I've sent all the details to Steve and Tony, but their developers seem unable to fix things so they work like they used to... I'll gladly trade the pretty colors and the occasional smiley face for code that keeps the same format I wrote it in.

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

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

Jeff,

...but their developers seem unable to fix things so they work like they used to.

Ouch.

They're doing all they can to put in a better solution ASAP. The problem was that it became impossible to improve the browser-side 'prettifier' without a lot of effort (Javascript regex solutions can only do so much) and they are planning on using a vastly better server-side solution. They're working on it urgently at this very moment.

You can always use my Prettifier in the meantime!

Best wishes,
Phil Factor

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

Jeff Moden (5/1/2009)
Almost forgot... the reason why I don't use the code windows anymore is because, although they may work fine with FireFox, they suck when using IE7. The remove blank lines on insert and add blank lines while removing leading space when copying. I've sent all the details to Steve and Tony, but their developers seem unable to fix things so they work like they used to... I'll gladly trade the pretty colors and the occasional smiley face for code that keeps the same format I wrote it in.

Hi Jeff

Did you try the good old pettifier here? Maybe it works better with IE.

I'm currently analyzing your STUFF function ;-).

Greets

Flo