| Author |
 Topic  |
|
|
moorthy123
Starting Member
USA
5 Posts |
 Posted - 11/09/2012 : 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.ID
INTO #REPEAT
FROM #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.VendorID
UNION
SELECT DISTINCT
c.ID
FROM #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
USA
38200 Posts |
|
|
moorthy123
Starting Member
USA
5 Posts |
Posted - 11/09/2012 : 17:04:49
|
| We have clustered index on the temp tables. |
 |
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
2179 Posts |
Posted - 11/11/2012 : 02:00:09
|
Could you supply the DDL of the tables , and ideally some sample data
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
|
|
|
Jeff Moden
Aged Yak Warrior
USA
652 Posts |
Posted - 11/11/2012 : 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
USA
5 Posts |
Posted - 11/12/2012 : 12:15:50
|
we have created index which shown below
CREATE 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) |
|
|
| |
Topic |
|
Need help to improve the performance of temp table