Performance issue with tally solution

Question

Performance issue with tally solution

Viewing 15 posts - 316 through 330 (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

Adam Machanic (4/28/2009)
David Burrows (4/28/2009)
I split data with consecutive delimiters, ie a column/field with no data
In most of the use cases I'm interested in, "a,,b,,c" with a delimiter of "," should return 3 rows

Adam, David,

I tend I agree with David on this: String.Split() has a handy optional parameter which allows you to ignore blank rows in the results - maybe something similar would be appropriate?

I think most people would expect '1,2,,3,,4' to expand to include blank rows, so that would make a sensible default.

Cue 'most people' telling me I'm wrong...:cool:

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Florian Reischl (4/28/2009)
Hierarchical data to be split? Well. This becomes really freaky... It would require either recursive methods or a much more complex RegEx which also becomes quiet slow.Flo

Hey Flo,

I didn't have anything so complex in mind - I'm not looking for something to replace XML :laugh:

The idea is to be able to split a string twice to produce a table result, but one to this one level, so:

"A~1,2,3`B~4,5,6`"

becomes

A 1

A 2

A 3

B 4

B 5

B 6

~ denotes the start of a record, and ` the end. Items are separated with a third delimiter.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Flo,

Forget the articles.

You're looking at a book!!!

Cheers,

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Adam Machanic SSCoach Points: 15370 More actions · Answer 4

Paul White (4/28/2009)
I didn't have anything so complex in mind - I'm not looking for something to replace XML :laugh:
The idea is to be able to split a string twice to produce a table result, but one to this one level, so:

Hmm, that looks a lot like a replacement for XML to me 🙂

What's the use case?

--
Adam Machanic
whoisactive

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

Adam Machanic (4/28/2009)
Hmm, that looks a lot like a replacement for XML to me 🙂
What's the use case?

Optional 'attributes' of existing entities have been stored rolled up into a string of this sort and kept in a database table. It's the sort of thing that will be replaced by a sparse table, or an expanded relational schema at some stage, but for the time being, it is how it is.

I suppose I could use REPLACE with stringy XML tags to convert the delimiters to end up with a string which could be cast to XML, and then XQuery it, but that just feels horrible.

Cheers,

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Paul White (4/28/2009)
Florian Reischl (4/28/2009)
Hierarchical data to be split? Well. This becomes really freaky... It would require either recursive methods or a much more complex RegEx which also becomes quiet slow.Flo
Hey Flo,
I didn't have anything so complex in mind - I'm not looking for something to replace XML :laugh:
The idea is to be able to split a string twice to produce a table result, but one to this one level, so:
"A~1,2,3`B~4,5,6`"
becomes
A 1
A 2
A 3
B 4
B 5
B 6
~ denotes the start of a record, and ` the end. Items are separated with a third delimiter.
Paul

Hey Paul

This should be not a big problem in .Net but I think this will exclude the set based TSQL solutions. Sure it is possible with a "double-barreled"/"dual-carburetor" tally and a look-up for the previous parent information. I just suspect the performance will be not very good.

Greets

Flo

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

Paul White (4/28/2009)
You're looking at a book!!!

... or maybe something equal to http://www.connectionstrings.com/.

What about http://www.splitstrings.com? For string splitting in all programming languages (including the specials and esoteric!) C#, TSQL, C, Cobol, Whitespace, Lasso, J-Script, Ook ... .

:laugh:

Greets

Flo

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

Aw, just be done with it and create your own internet!

Flinternet? :blink:

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Well, I think I keep the intend to write an/some article(s). 😉

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

@Adam

Sorry for the late feedback. I just tried your new CLR function. It works very good! The performance is alike my multi-character split method.

Thanks!

Flo

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

Paul White (4/29/2009)
Aw, just be done with it and create your own internet!
Flinternet? :blink:
Paul

Flonternet?

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

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

Jack Corbett (4/29/2009)
Flonternet?

:laugh: :laugh: :laugh:

Better, yes.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Jack Corbett (4/29/2009)
Paul White (4/29/2009)
Aw, just be done with it and create your own internet!
Flinternet? :blink:
Paul
Flonternet?

Which character is the delimiter?

😀

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 14

Paul White (4/28/2009)
Florian Reischl (4/28/2009)
Hierarchical data to be split? Well. This becomes really freaky... It would require either recursive methods or a much more complex RegEx which also becomes quiet slow.Flo
Hey Flo,
I didn't have anything so complex in mind - I'm not looking for something to replace XML :laugh:
The idea is to be able to split a string twice to produce a table result, but one to this one level, so:
"A~1,2,3`B~4,5,6`"
becomes
A 1
A 2
A 3
B 4
B 5
B 6
~ denotes the start of a record, and ` the end. Items are separated with a third delimiter.
Paul

http://www.sqlservercentral.com/articles/T-SQL/63003/

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

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi