Need help to improve the performance of temp table

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help to improve the performance of temp table

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.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

38200 Posts

Posted - 2012-11-09 : 16:51:07

What does the execution plan show? What indexes do you have on on the tables?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

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 data

Jack 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 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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources