question

ashok2012 avatar image
ashok2012 asked

SP taking more than 1 mintue to execute.Can we reduce less than 20 sec? any optimization tips!!!!!

Hi Experts, I have SP getcampaigndetails. It executes 1 mint 15 sec more. But I want to reduce time for atleast below 20 sec. Any optimisation tips more appreciated. I have attached script for your ref: I have given details of main tables involved in this SP Tracking Table have count 1,53,06858 StoreTracking Table have count 39,46,188 Users Table have count 28,19,545 Email Table have count 28,17,752 [link text][1] SP code details: CREATE PROC getcampaigndetails ( @p_MailCampaignID INT , @p_StartRecord INT , @p_EndRecord INT ) AS BEGIN CREATE TABLE #tempec ( ID INT IDENTITY(1, 1) , UID INT , FIRSTNAME VARCHAR(300) , LASTNAME VARCHAR(300) , INSTITUTION VARCHAR(1000) , EMAILADDRESS VARCHAR(500) , DATETIMEHIT DATETIME , URL VARCHAR(MAX) , MEDIATYPE VARCHAR(300) , MEDIAID INT , MEDIATITLE VARCHAR(MAX) , COUNTRYNAME VARCHAR(300) , STATE VARCHAR(300) , PHONE VARCHAR(300) , POSITION VARCHAR(300) , MailCampaign INT ) CREATE CLUSTERED INDEX temptable_idx_cl ON #tempec ([ID]) CREATE NONCLUSTERED INDEX temptable_idx_ncl1 ON #tempec ([MailCampaign]) INCLUDE ([ID]) CREATE NONCLUSTERED INDEX temptable_idx_ncl2 ON #tempec ([MailCampaign]) INCLUDE ([ID], [URL],[MEDIATITLE],[DATETIMEHIT],[UID],[MEDIAID]) INSERT INTO #tempec SELECT * FROM ( SELECT ISNULL(U.UID, 0) AS UID , ISNULL(U.FIRSTNAME, '') AS FIRSTNAME , ISNULL(U.LASTNAME, '') AS LASTNAME , ISNULL(U.INSTITUTION, '') AS INSTITUTION , E.EMAILADDRESS , T.DATETIMEHIT , T.URL , T.MediaType , T.MediaID , CASE WHEN T.URL IS NOT NULL AND CHARINDEX(' soft.com/direct/index.cfm?cid=', T.URL) > 0 THEN 'Redirect=' + SUBSTRING(SUBSTRING(T.URL, CHARINDEX('did=', T.URL) + 4, LEN(T.URL)), 0, CHARINDEX('&uid', SUBSTRING(T.URL, CHARINDEX('did=', T.URL) + 4, LEN(T.URL)))) ELSE CASE WHEN T.URL IS NOT NULL THEN CASE WHEN T.MediaType = 'Hint' THEN ' BBN Hint - ' + T.MediaTitle + ' ' WHEN T.MediaType = 'Tutor' THEN ' BBN Tutor -' + T.MediaTitle + ' ' WHEN T.MediaType = 'Present Clip' THEN ' BBN Present Clip - ' + T.MediaTitle + ' ' WHEN T.MediaType = 'Black Paper' THEN ' BBN Black Paper - ' + T.MediaTitle + ' ' WHEN T.MediaType = 'Product' THEN ' Store - ' + T.MediaTitle + ' ' ELSE ' ' + SUBSTRING(T.URL, 0, 41) + '... ' END END END AS MEDIATITLE , '' AS COUNTRYNAME , '' AS STATE , '' PHONE , ISNULL(U.POSITION, '') AS POSITION , MailCampaign FROM ( SELECT culture , UID , DATETIMEHIT , URL , UrlReferrer , MailCampaign , '' AS MediaType , 0 AS MediaID , '' AS MediaTitle FROM Searchingdb.dbo.tracking WITH ( NOLOCK ) WHERE MailCampaign = @p_MailCampaignID UNION SELECT CASE AH.CULTUREID WHEN 1 THEN 'JA-JP' WHEN 2 THEN 'FR-FR' WHEN 3 THEN 'DE-DE' WHEN 4 THEN 'EN-US' END AS Culture , AH.Uid , AH.HitDate AS DatetimeHit , ' http://presentnews.soft.com/Hints/Default.aspx?hintID=' + CONVERT(NVARCHAR(MAX), AH.HintID) AS URL , AH.URLReferrer , AH.MailCampaign , 'Hint' AS MediaType , A.HintID AS MediaID , A.Title AS MediaTitle FROM Searchingdb.dbo.bbnHintHits AS AH WITH ( NOLOCK ) INNER JOIN Testdb.dbo.HintsCultures AS A ON AH.HintID = A.HintID AND A.CultureID = 4 AND AH.MailCampaign = @p_MailCampaignID UNION SELECT CASE CULTUREID WHEN 1 THEN 'JA-JP' WHEN 2 THEN 'FR-FR' WHEN 3 THEN 'DE-DE' WHEN 4 THEN 'EN-US' END AS Culture , FCH.Uid , FCH.HitDate AS DatetimeHit , ' http://presentnews.soft.com/PresentClips/Default.aspx?presentclipID=' + CONVERT(NVARCHAR(MAX), FCH.PresentClipID) AS URL , FCH.URLReferrer , FCH.MailCampaign , 'Present Clip' AS MediaType , FC.PresentClipsID , FC.PresentClipsTitle AS MediaTitle FROM Searchingdb.dbo.bbnPresentClipHits AS FCH WITH ( NOLOCK ) INNER JOIN Site.dbo.PresentClipsDetails AS FC ON FCH.PresentClipID = FC.PresentClipsID AND FC.PresentClipsCultureID = 4 AND FCH.MailCampaign = @p_MailCampaignID UNION SELECT CASE CULTUREID WHEN 1 THEN 'JA-JP' WHEN 2 THEN 'FR-FR' WHEN 3 THEN 'DE-DE' WHEN 4 THEN 'EN-US' END AS Culture , WH.Uid , WH.HitDate AS DatetimeHit , ' http://presentnews.soft.com/tutors/info/Default.aspx?tutorID=' + CONVERT(NVARCHAR(MAX), W.TutorID) AS URL , WH.URLReferrer , WH.MailCampaign , 'Tutor' AS MediaType , W.TutorID , W.TutorTitle AS MediaTitle FROM Searchingdb.dbo.bbnTutorHits AS WH WITH ( NOLOCK ) INNER JOIN Site.dbo.TutorDetails AS W ON WH.TutorID = W.TutorID AND W.TutorCultureID = 4 AND WH.MailCampaign = @p_MailCampaignID UNION SELECT CASE WP.CULTUREID WHEN 1 THEN 'JA-JP' WHEN 2 THEN 'FR-FR' WHEN 3 THEN 'DE-DE' WHEN 4 THEN 'EN-US' END AS Culture , WPH.Uid , WPH.HitDate AS DatetimeHit , ' http://presentnews.soft.com/BlackPapers/Default.aspx?blackPaperID=' + CONVERT(NVARCHAR(MAX), WPH.BlackPaperID) AS URL , WPH.URLReferrer , WPH.MailCampaign , 'Black Paper' AS MediaType , WP.BlackPapersID AS MediaID , WP.Title AS MediaTitle FROM Searchingdb.dbo.bbnBlackPaperHits AS WPH WITH ( NOLOCK ) INNER JOIN Testdb.dbo.BlackPapersDetails AS WP ON WPH.BlackPaperID = WP.BlackPapersID AND WP.CultureID = 4 AND WPH.MailCampaign = @p_MailCampaignID UNION SELECT ST.culture , ST.Uid , ST.datetimehit , ST.URL , ST.UrlReferrer , ST.MailCampaignID AS MailCampaign , CASE WHEN ST.ITEMID <> 0 THEN 'Product' ELSE '' END AS MediaType , CASE WHEN ST.ITEMID <> 0 THEN I.itemID ELSE 0 END AS MediaID , CASE WHEN ST.ITEMID <> 0 THEN I.ItemName ELSE '' END AS MediaTitle FROM Searchingdb.dbo.StoreTracking AS ST WITH ( NOLOCK ) LEFT OUTER JOIN Products.dbo.Item AS I ON ST.ItemID = I.ItemID WHERE ST.MailCampaignID = @p_MailCampaignID ) AS T LEFT JOIN Logins.dbo.Users AS U WITH ( NOLOCK ) ON U.UID = T.UID LEFT JOIN Logins.dbo.Email AS E WITH ( NOLOCK ) ON U.UID = E.UID WHERE MailCampaign = @p_MailCampaignID ) AS a SELECT DISTINCT UID , FIRSTNAME , LASTNAME , INSTITUTION , EMAILADDRESS , DATETIMEHIT , URL , MEDIATYPE , MEDIAID , MEDIATITLE , COUNTRYNAME , STATE , PHONE , POSITION , ID AS [RowIndexValue] , ( SELECT COUNT(*) FROM #tempec ) AS TotalRecordCount , ( SELECT COUNT(*) FROM #tempec WHERE CHARINDEX(' www.soft.com/direct/', URL) > 0 ) [EmailClicks] , ( SELECT COUNT(*) FROM #tempec WHERE CHARINDEX(' www.soft.com/direct/', URL) <= 0 ) [PageHits] FROM #tempec WITH ( INDEX ( temptable_idx_cl ) ) WHERE [ID] BETWEEN @p_StartRecord AND @p_EndRecord ORDER BY RowIndexValue DROP TABLE #tempec END [1]: /storage/temp/1855-getcampaigndetails.txt
t-sqlstored-proceduresoptimization
1 comment
10 |1200

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

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Can you share the full execution plan?
3 Likes 3 ·
veliz.jaime avatar image
veliz.jaime answered
Change the order. First Insert the data, and then create the indexes.(why do you create 3 indexes? I think you just need one) You can get this values into variables to don't do it so many times: ( SELECT COUNT(*) FROM #tempec ) AS TotalRecordCount , ( SELECT COUNT(*) FROM #tempec WHERE CHARINDEX(' www.soft.com/direct/', URL) > 0 ) [EmailClicks] , ( SELECT COUNT(*) FROM #tempec WHERE CHARINDEX(' www.soft.com/direct/', URL) <= 0 ) [PageHits]
10 |1200

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

Grant Fritchey avatar image
Grant Fritchey answered
UNION in all those instances will require aggregation in order to retrieve distinct lists of data. Is it needed? Can you use UNION ALL? That will be much faster. The DISTINCT keyword used in the final SELECT statement will also require aggregation, and this is aggregation against queries that have already been aggregated by the UNION query. Is that needed? You're loading everything into the temporary table, then filtering the temporary table. Why not reduce that overhead. Run the query as is and apply the filters to the query itself directly. Then you won't be paying the added cost of building the temporary table. And, as Thomas says, if you post the execution plan, we can give you more detailed suggestions for tuning.
10 |1200

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

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.