x

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

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 '<a href='
                                                               + T.URL
                                                               + '> BBN Hint - '
                                                               + T.MediaTitle
                                                               + '</a> '
                                                              WHEN T.MediaType = 'Tutor'
                                                              THEN '<a href='
                                                               + T.URL
                                                               + '> BBN Tutor -'
                                                               + T.MediaTitle
                                                               + '</a> '
                                                              WHEN T.MediaType = 'Present Clip'
                                                              THEN '<a href='
                                                               + T.URL
                                                               + '> BBN Present Clip - '
                                                               + T.MediaTitle
                                                               + '</a> '
                                                              WHEN T.MediaType = 'Black Paper'
                                                              THEN '<a href='
                                                               + T.URL
                                                               + '> BBN Black Paper - '
                                                               + T.MediaTitle
                                                               + '</a> '
                                                              WHEN T.MediaType = 'Product'
                                                              THEN '<a href='
                                                               + T.URL
                                                               + '> Store - '
                                                               + T.MediaTitle
                                                               + '</a> '
                                                              ELSE '<a href='
                                                               + T.URL + '> '
                                                               + SUBSTRING(T.URL,
                                                               0, 41)
                                                               + '...</a> '
                                                         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

more ▼

asked Dec 19, 2014 at 01:32 PM in Default

avatar image

ashok2012
420 2 5 12

Can you share the full execution plan?

Dec 19, 2014 at 01:44 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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]

 
more ▼

answered Dec 22, 2014 at 04:31 PM

avatar image

veliz.jaime
51 1 1 2

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Dec 30, 2014 at 11:15 AM

avatar image

Grant Fritchey ♦♦
137k 20 44 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1090
x476
x47

asked: Dec 19, 2014 at 01:32 PM

Seen: 410 times

Last Updated: Dec 30, 2014 at 11:16 AM

Copyright 2017 Redgate Software. Privacy Policy