Performance issue with tally solution

Question

Performance issue with tally solution

Viewing 15 posts - 466 through 480 (of 522 total)

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

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

UMG Developer (5/12/2009)
Non-printable characters blow which method up? They appear to work just fine with the method I used. (For CR, LF, and TAB anyhow.)

Try embedding CHAR(11) or CHAR(27).... 😛

[font="Courier New"]Msg 6841, Level 16, State 1, Line 1

FOR XML could not serialize the data for node 'node' because it contains a character (0x000B) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

[/font]

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

UMG Developer SSChampion Points: 13482 More actions · Answer 2

Paul White (5/12/2009)
Try embedding CHAR(11) or CHAR(27).... 😛

Good to know, thanks! Lucky for me I don't have any of those bad characters in this data set. 🙂

UMG Developer SSChampion Points: 13482 More actions · Answer 3

Jeff Moden (5/10/2009)
I'm confused... why would you put over 4 million comments together only to break them apart again?

Jeff Moden (5/12/2009)
Why not just use the rollup code I used in my solution and pick whatever splitter you want... (Flo's "Cursor" solution is nasty fast) instead of fighting with the machine on the other solution where you have to move and... delete things?

Now you know why I wanted to roll everything up before splitting it in the first place. 😉 Of course if I had provided example data, or a better description I could have avoided wasting some of everybody's time. Sorry about that, I promise I will do better in the future. (It is sort of a pain to sanitize the data structure as well as the data.)

I had already gone the Tally method from an article you wrote before, but I was looking for even better ways, and I think we came up with a modified Tally version that is about 2 minutes faster than what I had come up with. (about 12% faster)

I may have to see if I can get them to let me try the CLR splitter out on the dev server, but I think they will be happy enough with the Tally solution that they won't care.

Thanks to all of you for the help and suggestions!

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

UMG Developer (5/12/2009)
I may have to see if I can get them to let me try the CLR splitter out on the dev server, but I think they will be happy enough with the Tally solution that they won't care.

I think the tally solution is probably near enough optimal anyway.

For your data, the perfect solution would be a kind of hybrid CLR aggregate/TVF - something which can stream rows into the input (like an aggregate) but also stream output rows (like a TVF).

Until SQL Server provides streaming TVF aggregate functions (!) or until someone sorts out the oddish way your store that data, that's about it I reckon...

Thanks for an illuminating example.

Cheers,

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

UMG Developer (5/12/2009)
Paul White (5/12/2009)
Jeff Moden (5/12/2009)
If anyone knows what the ".VALUE trick" that UMG was talking about is, I'm all ears, thank you. :w00t:
I believe he is referring to the (...FOR XML PATH('')).value('./[1]') nastiness. 😉
Embed a non-printable character to blow this one up.
Paul,
Non-printable characters blow up which method up? They appear to work just fine with the method I used. (For CR, LF, and TAB anyhow.)

There are only three non-printable control-characters allowed. Guess which three they are? 🙂

[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]

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

RBarryYoung (5/12/2009)
There are only three non-printable control-characters allowed. Guess which three they are? 🙂

ROFL! :w00t:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Paul White (5/12/2009)
RBarryYoung (5/12/2009)
There are only three non-printable control-characters allowed. Guess which three they are? 🙂
ROFL! :w00t:

Heh. Yeah, well they did except those three for a reason. 🙂

[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]

budi.bong Newbie Points: 1 More actions · Answer 8

How can I perform SQL SELECT FROM or given a SqlXml object?

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 9

budi.bong (9/9/2009)
How can I perform SQL SELECT FROM or given a SqlXml object?

Just a suggestion to get the right kind of help... rather than posting this on a thread having nearly nothing to do with you're particular problem, you should start a new post in the correct forum.

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

How can I boil the perfect egg?

:laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Paul White (9/9/2009)
How can I boil the perfect egg?

I've no idea how to boil a perfect egg, but you should use a CLR-split when it's done

:hehe:

Adam Machanic SSCoach Points: 15370 More actions · Answer 12

Paul White (9/9/2009)
How can I boil the perfect egg?

This one I can actually answer 😀

Put the eggs in the pan with cold, salted water

Bring to a boil

Immediately upon hitting boiling, kill the heat

Cover, and leave the eggs in the water for 10 minutes

Remove the eggs from the hot water and put them into an ice bath to stop the cooking process

Peel and eat.

--
Adam Machanic
whoisactive

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

Adam Machanic (9/9/2009)
Paul White (9/9/2009)
How can I boil the perfect egg?
This one I can actually answer 😀
Put the eggs in the pan with cold, salted water
Bring to a boil
Immediately upon hitting boiling, kill the heat
Cover, and leave the eggs in the water for 10 minutes
Remove the eggs from the hot water and put them into an ice bath to stop the cooking process
Peel and eat.

Do you have a Test-Harness for this? 🙂

[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]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 14

What's the sound of one hand clapping?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jack Corbett SSC Guru Points: 184394 More actions · Answer 15

Adam Machanic (9/9/2009)
Paul White (9/9/2009)
How can I boil the perfect egg?
This one I can actually answer 😀
Put the eggs in the pan with cold, salted water
Bring to a boil
Immediately upon hitting boiling, kill the heat
Cover, and leave the eggs in the water for 10 minutes
Remove the eggs from the hot water and put them into an ice bath to stop the cooking process
Peel and eat.

Ah but what if I want a soft boiled egg? :w00t:

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question