question

sqlLearner 1 avatar image
sqlLearner 1 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.
sql-server-2008t-sqlperformance
7 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you supply the table creation scripts and indexes that are on these tables? The cross join is going to be a cause for the bad performance, is it really necessary to do that?
0 Likes 0 ·
I am using the cross join to fill table2. So it is necessary unless there is another way to fill it?
0 Likes 0 ·
What if I added a where clause.. a1.col = a2.col? Wouldn't that make it perform as an inner join?
0 Likes 0 ·
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.
0 Likes 0 ·
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.
0 Likes 0 ·
Show more comments

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
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.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Well is there another option I have other than the cross join? That is a viable alternative.
0 Likes 0 ·
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?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.