SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need help to improve the performance of temp table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

moorthy123
Starting Member

USA
5 Posts

Posted - 11/09/2012 :  16:44:24  Show Profile  Reply with Quote
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
37129 Posts

Posted - 11/09/2012 :  16:51:07  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

moorthy123
Starting Member

USA
5 Posts

Posted - 11/09/2012 :  17:04:49  Show Profile  Reply with Quote
We have clustered index on the temp tables.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 11/11/2012 :  02:00:09  Show Profile  Visit jackv's Homepage  Reply with Quote
Could you supply the DDL of the tables , and ideally some sample data

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 11/11/2012 :  18:09:57  Show Profile  Reply with Quote
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
Go to Top of Page

moorthy123
Starting Member

USA
5 Posts

Posted - 11/12/2012 :  12:15:50  Show Profile  Reply with Quote
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)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000