All Possible Combinations Loop

Question

All Possible Combinations Loop

Viewing 7 posts - 31 through 36 (of 36 total)

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

akberali67 SSC Eights! Points: 995 More actions · Answer 1

That worked great! I did try to run it at my workplace server and got a call in a few minutes from the IT desk to crash the process. 🙂

I ran it in parts at home and then will manually run 15,000 query combinations to test performance nevertheless the solution works. Cant help the system resources, you gotta do what you gotta do but hats off truly for the solution.

It is just a blessing, thank you very very much to all of you and hope you will always be there. Your experience and coding skills are truly outstanding and a skill needed to be learned. Also, hope Microsoft will provision more variables to be run through the rollup and cube functions.

Cheers,

Akber.

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

ChrisM@Work (2/11/2013)
I don't know if this error (Error 8623) is dependant upon system resources, in which case you might get lucky using a server with more resources, or if it's dependant upon SQL Server limitations. I suspect it's the latter but haven't yet found good evidence.

It's a limitation imposed on internal structures during parsing and compilation to prevent really bad things happening (like stack overflow). You should find it goes away if you really double or treble the source data rather than using UNIONs. The internal trees can blow up like balloons when you use UNION with other things like CUBE and ROLLUP. Appropriately enough, it's a combinatorial explosion of possibilities.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Paul White (2/18/2013)
ChrisM@Work (2/11/2013)
I don't know if this error (Error 8623) is dependant upon system resources, in which case you might get lucky using a server with more resources, or if it's dependant upon SQL Server limitations. I suspect it's the latter but haven't yet found good evidence.
It's a limitation imposed on internal structures during parsing and compilation to prevent really bad things happening (like stack overflow). You should find it goes away if you really double or treble the source data rather than using UNIONs. The internal trees can blow up like balloons when you use UNION with other things like CUBE and ROLLUP. Appropriately enough, it's a combinatorial explosion of possibilities.

Thanks for this Paul, I'll give it a whirl at home later and let you know the outcome.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

akberali67 SSC Eights! Points: 995 More actions · Answer 4

Paul, that worked! I dont know how. I am too new to SQL to understand but when I store the data in a table and then run the query it runs in 3 to 4 seconds. Thank you very much! Woooohoooo!!

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

akberali67 (2/18/2013)
Paul, that worked! I dont know how. I am too new to SQL to understand but when I store the data in a table and then run the query it runs in 3 to 4 seconds. Thank you very much! Woooohoooo!!

Well Chris M did the hard yards, but thanks anyway.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

akberali67 SSC Eights! Points: 995 More actions · Answer 6

Chris, thanks again for all the help. Truly thanks doesnt do it but from the bottom of my heart I can not thank you for the help.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

akberali67 (2/18/2013)
Chris, thanks again for all the help. Truly thanks doesnt do it but from the bottom of my heart I can not thank you for the help.

Gosh - thanks! That's very generous! It's kind words like this which make it so worthwhile helping folks on ssc. Thank you Akber, and good luck with your project. Thanks too, to Paul, for working out the final tricky bit. Cheers mate 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden