Large Cartesian Product

I just spoke with someone at straightpaths.com who suggested I try this forum. I'm creating a "paths to victory" solution for a series of games where each game has 8 players who will finish 1-8 over a 10-game series. In the interest of working with reasonable numbers, I'm going to isolate the possible outcomes for final two games to identify the winning possibilities for each player with respect to actual results gathered from games 1-8. I'm trying to store the 8!^2 or about 1.6BB outcomes for the two games. I'm running desktop developer edition of the server with MSSMS v18.12. What is the most optimal way to generate the data and commit the result? I have the tempdb and development database on a disk with 400Gb of free storage. I currently have a table with the 40320 (8!) records that enumerate outcomes for 8 players in one game, 1) ABCDEFGH...40320) HGFEDCBA, cross joined on itself to give me possible outcomes for 2 games. Any thoughts on how to go about committing this data would be welcome. Also, if you have ideas on how to optimize this to expand to outcomes for more than two games I'd be interested in hearing this. Thank you!

New contributor
user254433 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
• Unfortunately your question is a bit unclear at the moment. Please update it and try to elaborate (ideally with examples and punctuation) on what the problem is you're trying to solve. If I understand your correctly, it sounds like you've figured out how to generate a dataset that represents all possible outcomes for all players across 2 games. What I'm unclear on is when you say "Any thoughts on how to go about committing this data" - what do you mean by this? You can certainly store the results into a table, but it won't be realtime should any of the inputs change (like number of players).
– J.D.
Jun 21 at 18:17
• @J.D. hi jd, thank you for asking. i mean it quite literally, how do i get sqlserve to commit the data. the statement below ran for over 2hrs. the table permute_8 is uniquely indexed on combined columns p1 - p8. my log files are nearly 300GB. the results were never stored as i finally had to cancel the process insert dbo.permute_8_2n(path_w1, path_w2) select concat(a1.p1, a1.p2, a1.p3, a1.p4, a1.p5, a1.p6, a1.p7, a1.p8), concat(a2.p1, a2.p2, a2.p3, a2.p4, a2.p5, a2.p6, a2.p7, a2.p8) from dbo.permute_8 a1 cross join dbo.permute_8 a2 Jun 21 at 19:42
• Ah yea so you're having a performance issue. Please update your post (so others see the details) noting that, and with your query from your previous comment. You'll also want to include the execution plan in your post as well, which you can upload at Paste The Plan.
– J.D.
Jun 21 at 20:09
• One thing I can advise off the bat is running a function like CONCAT() against ~1.6 billion rows is going to take a while. You're better off saving the results to a table first then calling that function against the table for only the subset of rows you need when you read from the table (assuming you're not going to read the entire table back at one time).
– J.D.
Jun 21 at 20:10
• Bit too vague, but here are some tips: Pre-allocate the database size for the data. Insert into an empty table. Ensure that table is on a different disk and filegroup from the one you are reading. Use TABLOCK hints on both the table to insert and the tables to read. Consider a Clustered Columnstore Index. Consider why you need this cross-join in the first place, and whether there is a better way of storing the data in the first place, such as querying the original tables using a view. Jun 21 at 20:10