SQL Server Performance

T-SQL Tuning Issue

Discussion in 'SQL Server 2005 General Developer Questions' started by sqlderby, Sep 2, 2010.

  1. sqlderby Member

    I have following T-SQL that I have been tuned up and come up with 20 seconds from 2 minutes. Made couple of indexes and change joins and columns sequences and created the indexes accordingly.
    First one....
    CREATE TABLE #TblEmplyMstr (in_Emply_Cd INT,EmpID INT, vc_Emply_Frst_Nm VARCHAR(50), vc_LstNm VARCHAR(50))
    CREATE CLUSTERED INDEX CIX_in_Emply_Cd ON #TblEmplyMstr (in_Emply_Cd)

    INSERT INTO #TblEmplyMstr
    SELECT IN_EMPLY_CD,ISNULL(UM.EMPLOYEENEWID,M.IN_EMPLY_CD), vc_Emply_Frst_Nm, vc_LstNm
    FROM Tbl_HMS_Emply_Mstr M
    LEFT OUTER JOIN TBL_HMS_USERMAPPING UM
    ON UM.EMPLOYEEOLDID = M.IN_EMPLY_CD
    -- Clustered INDEXES made on UM.EMPLOYEEOLDID and M.IN_EMPLY_CD columns.
    Second One...
    SELECT ROW_NUMBER() OVER(ORDER BY dt.date, dt.empcode) AS [S.No.],
    em.vc_Emply_Frst_Nm + ' ' + em.vc_LstNm + ' (' + CONVERT(VARCHAR(20), em.EmpID) + ')' AS [Agent Name],
    dt.ordernumber AS [Customer Ref#],
    ISNULL(dt.ANI, 'N/A') AS [ANI#],
    dt.CallType AS [Call Type],
    ds.DispositionType AS [Disposition],
    ISNULL(sds.SubDispositionType, 'N/A') AS [Sub-Disposition],
    tt.TTM_VC_TLK_TM_DSCRPTN AS [TalkTime],
    Ls.LFM_VC_LF_STG_DSCRPTN AS [Life Stage],
    dt.Catalogid AS [Catalog Number],
    IPO.Vc_IPO_Name AS [Item Per Order],
    dt.Personalization AS [Personalization (Y/N)],
    dt.SafeLine AS [Safeline Accepted (Y/N)],
    dt.Sticker AS [Sticker Activation (Y/N)],
    dt.Offer AS [Safeline - Opportunity],
    dt.Offer AS [Safeline - Offer],
    dt.Accept AS [Safeline - Accept],
    dt.Emailid AS ,
    dt.Ordertype AS [Order Type] ,
    RR.RSNFRNTPPLYNG_NM AS [Reasonapplying]
    FROM tbldetailsNew dt
    INNER JOIN #TblEmplyMstr em ON em.in_Emply_Cd = dt.empcode
    INNER JOIN tbldispositionNew AS ds ON ds.DispositionId = dt.dispositionid
    LEFT JOIN tblsubdisposition AS sds ON sds.subdispositionid = dt.subdispositionid
    LEFT JOIN fht_ttm_tlk_tm_mstr AS tt ON dt.talktime = tt.TTM_IN_TLK_TM_ID_PK
    LEFT JOIN FHT_LFM_LF_STG_MSTR AS Ls ON dt.Lifestageid=Ls.LFM_IN_LF_STG_ID_PK
    LEFT JOIN FHT_ITEM_PER_ORDER_MSTR AS IPO ON dt.ItmPerOdrId=IPO.In_IPO_Id_Pk
    LEFT JOIN TBL_RSNFRNTPPLYNG RR ON RR.RSNFRNTPPLYNG_PK = dt.Rsnfrntpplyng
    WHERE dt.date between @sDate AND @eDate
    AND dt.DispositionId = @dID
    AND dt.subdispositionid = @sdID
    AND dt.CallType = @CallType
    Create NonClustered Indexes on empcode, dispositionid, subdispositionid, TALKTIME, Lifestageid, ItmPerOdrId, Rsnfrntpplyng Columns. Indexex on Alll of Joined Table's columns as well.
    AND also created individual NonClustered Indexes on WHERE clause columns in the table...Here is SET STATISTICS IO ON/OFF Results...
    It still giving me Clustered Index Scan for both Queries. Pls help me here to understand the teh T-SQL it tune it better.
    Thanks...
    Table '#TblEmplyMstr_______________________________________________________________________________________________________00000003780E'. Scan count 0, logical reads 40902, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Tbl_HMS_Emply_Mstr'. Scan count 1, logical reads 462, physical reads 1, read-ahead reads 460, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'tbl_HMS_UserMapping'. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'tbl_Rsnfrntpplyng'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'FHT_ITEM_PER_ORDER_MSTR'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'FHT_LFM_LF_STG_MSTR'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'FHT_TTM_TLK_TM_MSTR'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'tblsubdisposition'. Scan count 1, logical reads 4, physical reads 1, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#TblEmplyMstr_______________________________________________________________________________________________________00000003780E'. Scan count 9, logical reads 94, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'tbldispositionNew'. Scan count 1, logical reads 4, physical reads 1, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'tbldetailsnew'. Scan count 9, logical reads 240227, physical reads 10, read-ahead reads 239017, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


  2. ashish287 New Member

    i dont think anyone going to give you exact solution based on the query you posted because :-
    1) quiet long to read
    2) not enough time to understand the logic.
    So,
    better try on your own by :-
    1) check execution plan
    2) Check index fregmentation
    3) run profiler to check if there is any lock or deadlock. If it is then why.
    Now, if you going to ask me how, I will suggest google on these topics and you will get much better answer then mine.
  3. satya Moderator

    I would like to repeat the same by Frank that it depends on number of rows that are returned in the execution plan. Look under estimated rows and also when you execute try to include actual exectuion plan to get exact number of rows.
  4. FrankKalis Moderator

    #1: Sorry, but what else but a scan do you expect from a query that has no WHERE clause and therefore has to touch every row in the outer table?
    #2: How many rows are in the tables? How many rows satisfy the WHERE clause? I might be wrong but I can't see that you have an index on the columns used in the WHERE clause or at least not in a column order that would make the index useful for your query. Looks like the index is not being used at all. Can you check this in the execution plan?

Share This Page