Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
moorthy123
Starting Member
5 Posts |
Posted - 2012-11-09 : 16:44:24
|
Hi all,I have a query in my SP it tooks 14 hrs to complete. it usually insert 8 millions of records into one temp table. Query given below. Please help me to tune this query.SELECT DISTINCT c.IDINTO #REPEATFROM #PREVIOUS p JOIN #CURRENT c ON p.CarrierID = c.CarrierID AND p.PhoneNumber = c.PhoneNumber AND p.MediaTypeID = c.MediaTypeID AND p.TitleID = c.TitleID AND p.VendorID = c.VendorIDUNION SELECT DISTINCT c.IDFROM #PREVIOUS p JOIN #TMP_UNKNOWN t on t.MediaTypeID = p.MediaTypeID JOIN #CURRENT c ON p.CarrierID = c.CarrierID AND p.PhoneNumber = c.PhoneNumber AND p.MediaTypeID = c.MediaTypeID AND p.TitleID = c.TitleID AND (p.VendorID = t.VendorID OR c.VendorID = t.VendorID ) |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
moorthy123
Starting Member
5 Posts |
Posted - 2012-11-09 : 17:04:49
|
We have clustered index on the temp tables. |
 |
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-11-11 : 02:00:09
|
Could you supply the DDL of the tables , and ideally some sample dataJack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2012-11-11 : 18:09:57
|
Look at the ACTUAL execution plan. Do you see any arrows that have a count that's very much larger that the table it's pointing from? If you do, then you have a many-to-many join (also known as an "accidental cross join" that you're DISTINCT is covering up. Without having much more to go on, I reccommend that you find a way to get rid of the DISTINTS and the UNION (which also does a DISTINCT). And, no... using a GROUP BY in place of the DISTINCT isn't going to help a bit.Also, when someone asks you what indexes you have, responding with something like "we have clustered indexes" without mentioning the columns they're on does absolutely nothing for someone to help you.--Jeff Moden |
 |
|
moorthy123
Starting Member
5 Posts |
Posted - 2012-11-12 : 12:15:50
|
we have created index which shown belowCREATE CLUSTERED INDEX PREV_MONTH_IDX ON #PREVIOUS (CarrierID,PhoneNumber,MediaTypeID,TitleID,VendorID)CREATE CLUSTERED INDEX CURNT_MONTH_IDX ON #CURRENT (CarrierID,PhoneNumber,MediaTypeID,TitleID,VendorID)CREATE CLUSTERED INDEX TMP_UNKNOWN_VENDOR_IDX ON #TMP_UNKNOWN (MediaTypeID,VendorID) |
 |
|
|
|
|
|
|