question

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