asked
Make code faster
Anyone know a way to make this code run quicker? It is joining two huge tables? There might not be a way but just curious if someone seems something select distinct a1.colA, a2.colB, a2.colC, a2.colD , a2.colE, a2.colF, ` `a2.colG, a2.colH, a2.colI, a2.colJ, a2.colK into ##temp_table3 from ##temp_table1 a1 cross join ##temp_table2 a2 week acct reporting 1 23 50 2 23 43 5 23 36 6 23 45 I am using the cross join because I need to get it to report for weeks 3 and 4 that is it's 0...Right now it skipping weeks 3 and 4 in my dataset because there was no reporting.
I am using the cross join to fill table2. So it is necessary unless there is another way to fill it?
What if I added a where clause.. a1.col = a2.col? Wouldn't that make it perform as an inner join?
Yes that would make it an inner join. What I meant was, what are you trying to achieve with the join, do you want to multiply the rowcount to create lots of data, or are you trying to filter data through the join? An inner join will only return/insert rows that occur in both tables a cross join will multiply the rowcount.
I am using the cross join to fill in the missing weeks. A where clause that joins the tables on week doesn't work because he only takes the weeks that are existing and doesn't add the weeks that are not missing.
Ok, now we're getting there. Could you possibly continue editing your answer to supply full table creation scripts, test data (as insert commands), and the expected results. A little more detail about what you are wanting to achieve would also help us provide a better answer.
I don't have the full table creation scripts.. I can explain it: In the above example table a1 is just a list of weeks from 1-50..Table a2 is the actual data like the example i provided. I want to cross join them to use the weeks in table a1 to fill in the missing weeks in a2. So that a2 will show a 0 for weeks with no reporting.
Flat out, you won't be able to make a CROSS JOIN without a WHERE clause run faster. It's everything on everything. You are strictly down to how much memory you have and how fast are your disks. You would have to change what you're doing entirely to even have the opportunity for code or index enhancements.
Well is there another option I have other than the cross join? That is a viable alternative.
What about just an outer join? I'm with @williamD, it's not clear what you're trying to do from this code snippet. Also, in terms of speeding things up, it looks like you're putting a heck of a load on the tempdb with global temp tables, and you say they're large... that's going to be a tuning opportunity right there. Why not just create permanent tables?
