Generating n-Tuples with SQL
-
Jesse McLain (5/17/2012)
chris.stuart (5/17/2012)
I always enjoy reading the articles, but this one got me a bit stumped. Where would you actually use this?:unsure:I used this method in an algorithm to identify unique keys in raw data: http://www.sqlservercentral.com/scripts/T-SQL/62086/
(I tried to include the link as IFCode, but it didn't work properly.)
Jesse - My humblest apologies for not identifying this article in my Googling... I believe I've successfully transformed it to a link to help others locate it easily.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
dwain.c (5/17/2012)
Seriously, I'm smiling (:-D) because I figure that if I can stir up the emotions of the staunchly rigid and proper SQL aristocracy then I've done my job.ABSOLUTELY AGREED! I just hope you're not grouping me in the "staunchly rigid and proper SQL aristocracy" categories.
--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) -
Jesse McLain (5/17/2012)
chris.stuart (5/17/2012)
I always enjoy reading the articles, but this one got me a bit stumped. Where would you actually use this?:unsure:I used this method in an algorithm to identify unique keys in raw data: http://www.sqlservercentral.com/scripts/T-SQL/62086/
(I tried to include the link as IFCode, but it didn't work properly.)
No worries, just wanted to share and show an example of practical use. Another (less practical) usage is in a poker app that I started and shelved. It required me to generate all possible five card hands, and I used this method to do so. When I tried to generate all possible 7 card hands I blew out tempdb on my laptop - I wrestled with working around for awhile before more pressing matters took priority. That effort really speaks to Jeff Moden's point about alternative solutions - even if I could have done it spacewise, performance was horrible. A C# app writing to text files would have been much better. Still, it was a good exercise in recursive CTEs.
-
dwain.c (5/17/2012)
I get it! Change RBAR in your avatar to Dwain.C and there you have it.Heh... considering the tagline in your signature, I do have to question why you haven't really explored the RBAR-on-steroids side of recursive CTEs compared to plain RBAR. 😉 Recursive CTE's have the equivalent (IMHO) of loops, cursors, and certainly, RBAR. 🙂
--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 (5/17/2012)
dwain.c (5/17/2012)
Seriously, I'm smiling (:-D) because I figure that if I can stir up the emotions of the staunchly rigid and proper SQL aristocracy then I've done my job.ABSOLUTELY AGREED! I just hope you're not grouping me in the "staunchly rigid and proper SQL aristocracy" categories.
Of course not! I was referring to that other guy. :ermm:
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Jeff Moden (5/17/2012)
dwain.c (5/17/2012)
I get it! Change RBAR in your avatar to Dwain.C and there you have it.Heh... considering the tagline in your signature, I do have to question why you haven't really explored the RBAR-on-steroids side of recursive CTEs compared to plain RBAR. 😉 Recursive CTE's have the equivalent (IMHO) of loops, cursors, and certainly, RBAR. 🙂
That tagline is there for a reason and as long as you don't suggest you'll revoke my no-RBAR card again it stays there. Suffice it to say that there's a method to my madness. I just haven't been participating in this community long enough for it to be exposed as of yet.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Jesse McLain (5/17/2012)
Jesse McLain (5/17/2012)
chris.stuart (5/17/2012)
I always enjoy reading the articles, but this one got me a bit stumped. Where would you actually use this?:unsure:I used this method in an algorithm to identify unique keys in raw data: http://www.sqlservercentral.com/scripts/T-SQL/62086/
(I tried to include the link as IFCode, but it didn't work properly.)
No worries, just wanted to share and show an example of practical use. Another (less practical) usage is in a poker app that I started and shelved. It required me to generate all possible five card hands, and I used this method to do so. When I tried to generate all possible 7 card hands I blew out tempdb on my laptop - I wrestled with working around for awhile before more pressing matters took priority. That effort really speaks to Jeff Moden's point about alternative solutions - even if I could have done it spacewise, performance was horrible. A C# app writing to text files would have been much better. Still, it was a good exercise in recursive CTEs.
Who says poker isn't a practical app? You know how much money can be made at that game?
Interesting to note about running out of tempdb. The dynamic programming solution to the knapsack problem also achieves reduction in CPU time by trading off against memory usage.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Jeff Moden (5/17/2012)
Shifting gears, just changing it to a While Loop using the exact same logic will make it more efficient.It's not like you to make such a bold statement without code proof.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
I think you can use CUBE/ROLLUP for this.
SELECT CASE WHEN n1.strcol IS NULL THEN 0 ELSE 1 END +CASE WHEN n2.strcol IS NULL THEN 0 ELSE 1 END +
CASE WHEN n3.strcol IS NULL THEN 0 ELSE 1 END ,
ISNULL(n1.strcol+' ','')+ISNULL(n2.strcol+' ','')+ISNULL(n3.strcol+' ','')
FROM @t n1
INNER JOIN @t n2 ON n2.strcol>n1.strcol
INNER JOIN @t n3 ON n3.strcol>n2.strcol
GROUP BY CUBE(n1.strcol,n2.strcol,n3.strcol)
HAVING COALESCE(n1.strcol,n2.strcol,n3.strcol) IS NOT NULL
ORDER BY 1,2;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
SQL Kiwi (5/17/2012)
Jeff Moden (5/17/2012)
Shifting gears, just changing it to a While Loop using the exact same logic will make it more efficient.It's not like you to make such a bold statement without code proof.
Heh... I have the proof. I just haven't posted it. Time for someone else to prove it besides me. 🙂
--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 (5/17/2012)
Heh... I have the proof. I just haven't posted it. Time for someone else to prove it besides me. 🙂Well that doesn't really help anyone else much! Anyway, my point was it isn't like you not to back up claims with proof, that's all.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
SQL Kiwi (5/17/2012)
Jeff Moden (5/17/2012)
Heh... I have the proof. I just haven't posted it. Time for someone else to prove it besides me. 🙂Well that doesn't really help anyone else much! Anyway, my point was it isn't like you not to back up claims with proof, that's all.
Considering that I'm not seeing anyone else giving it a try, I may end up being the one having to do it after all.
--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) -
SQL Kiwi (5/17/2012)
Jeff Moden (5/17/2012)
Shifting gears, just changing it to a While Loop using the exact same logic will make it more efficient.It's not like you to make such a bold statement without code proof.
"I call that bold talk for a one-eyed fat man." - Lucky Ned Pepper, True Grit
As I recollect, Lucky Ned t'weren't so lucky on that day!
😀
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Jeff Moden (5/17/2012)
SQL Kiwi (5/17/2012)
Jeff Moden (5/17/2012)
Heh... I have the proof. I just haven't posted it. Time for someone else to prove it besides me. 🙂Well that doesn't really help anyone else much! Anyway, my point was it isn't like you not to back up claims with proof, that's all.
Considering that I'm not seeing anyone else giving it a try, I may end up being the one having to do it after all.
I hope it keeps you awake for at least a couple of nights!
All in good fun, I hope you realize. 🙂
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Mark-101232 (5/17/2012)
I think you can use CUBE/ROLLUP for this.SELECT CASE WHEN n1.strcol IS NULL THEN 0 ELSE 1 END +CASE WHEN n2.strcol IS NULL THEN 0 ELSE 1 END +
CASE WHEN n3.strcol IS NULL THEN 0 ELSE 1 END ,
ISNULL(n1.strcol+' ','')+ISNULL(n2.strcol+' ','')+ISNULL(n3.strcol+' ','')
FROM @t n1
INNER JOIN @t n2 ON n2.strcol>n1.strcol
INNER JOIN @t n3 ON n3.strcol>n2.strcol
GROUP BY CUBE(n1.strcol,n2.strcol,n3.strcol)
HAVING COALESCE(n1.strcol,n2.strcol,n3.strcol) IS NOT NULL
ORDER BY 1,2;
Mark - I've never used CUBE and I'm not in my SQL 2008 sandbox to check it out (I will).
Just out of curiosity though, will this code work without change for n-Tuples of all n?
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 16 through 30 (of 51 total)
You must be logged in to reply to this topic. Login to reply