Jeff Moden (7/30/2014)
500*500 is only a quarter million row Cross Join and would probably take less than a quarter second to materialize and just a second or two to insert into an indexed table.For the million row thing, you definitely have to be careful because 1*10^6 squared is 1*10^12 or a Trillion rows. Some of this could be mitigated by using a "Triangular" join, which would produce a bit less than half that of a full up Cross Join but 500 Billion rows is still nothing to sneeze at either duration wise or storage wise.
My recommendation would be to NOT turn this "Matrix" into a table.
The 500 rows was only one Resource at one plant. My cross join was doing more than it should.. but still.. this is working great for one plant. Once I get all the files imported into SQL server I will test run time. I am hoping that since a material can have several versions.. and thus rows.. I will not end up with 1,000,000 * 1,000,000.. or worse since I still have a second company to work with. Do you have a good link for "Triangular" join, never heard of it.
Oh I have also asked if it would help to build a table to show what resources we can leave out of this process, I know our plant scheduling software does not use every resource that plant has in our ERP software.
Oh BTW the 500 * 500 takes 10 seconds to execute (select and insert).. I need your hardware LOL.