SQL Server Performance

Query Optimization

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by virmahi, Dec 23, 2008.

  1. virmahi New Member

    Hi This query is taking long time to run...may be because it has lot of joins. I am new to tuning. Please guide me as where to start on it and what can be done to make it faster.
    SELECT P.CityCode [City Code],
    CASE B.CALLTYPE WHEN ''CSH'' THEN (B.CALLTYPE + CASE A.CLCALLACTION WHEN ''PKP'' THEN ''-P'' WHEN ''DLY'' THEN ''-D'' END)
    ELSE B.CALLTYPE END [Call Type], A.CLNATURE [B/R], A.CLCALLNO [Call Id], CONVERT(CHAR(10),A.CLACTDATE, ' + @SQLDateFormat + ') [Action Date], A.CLEXPCLTM [TAT Time], P.CustBrCode AS [Branch Code],
    CASE B.CALLTYPE WHEN ''ATMREPL'' THEN A.CLATMID ELSE ABC.CUSTCUSTNAME END [Client Name],
    CASE B.CALLTYPE WHEN ''ATMREPL'' THEN ISNULL(PQR.LOCDESC,ABC.LOCDESC) ELSE ABC.LOCDESC END [Area],
    ISNULL(A.CLAMT,0) [Amount INR], CASE WHEN A.CLCALLSTATUS = ''OP'' THEN 0 WHEN A.CLCALLSTATUS = ''AS'' THEN 0 ELSE
    CASE B.CALLTYPE+A.CLCALLACTION WHEN ''CSHPKP'' THEN (ISNULL(A.CLAMT,0) + ISNULL(A.CLDIFFAMT,0)) WHEN ''CSHCHQPKP'' THEN (ISNULL(A.CLAMT,0) + ISNULL(A.CLDIFFAMT,0)) WHEN ''CSHDLY'' THEN (ISNULL(A.CLAMT,0) - ISNULL(A.CLDIFFAMT,0)) WHEN ''ATMREPLATM'' THEN (ISNULL(A.CLAMT,0) + ISNULL(A.CLDIFFAMT,0)) WHEN ''CSHWBNKPKP'' THEN (ISNULL(A.CLAMT,0) + ISNULL(A.CLDIFFAMT,0)) WHEN ''CSHDBNKDLY'' THEN (ISNULL(A.CLAMT,0) - ISNULL(A.CLDIFFAMT,0)) ELSE 0 END END [Actual Amt],
    ISNULL(A.CLDIFFAMT,0) [Diff Amt], X.RtCode [Route Code], A.REQSLNO [Ack No], CASE A.CLCALLSTATUS WHEN ''OP'' THEN ''Open'' WHEN ''AS'' THEN ''Assigned'' WHEN ''AT''THEN ''Attended'' WHEN ''SK'' THEN ''Skipped'' WHEN ''CN'' THEN ''Cancelled'' WHEN ''CL'' THEN ''Closed'' END [Call Status], A.LOGINAUTHUSERID [Auth1],
    B.CALLTYPE [Call Type Code], CONVERT(CHAR(10),A.CLGENDATE ,'+ @SQLDateFormat +') [Gen Date], A.CLCALLSTATUS [Status],
    A.CLCALLACTION [Call Action], ABC.CUSTCUSTOMERCODE [Client Code], A.CLBNKLGNO [Bank Login],
    A.CLBNKFXLGTM [Fax Time], A.CLNOOFCHQS [No Of Chqs],
    A.CLCHQNO [Chq No],A.CLDDTCNO [DDTC No], A.CLDDTCAMT [DDTC Amt],
    (SELECT USERNAME FROM USER_HD WHERE USER_HD.USERID = A.LOGINAUTHUSERID) [Verified By], (SELECT USERNAME FROM USER_HD WHERE USER_HD.USERID = A.CLEXECUSERID) [Exec By],
    A.CLGENTIME [Gen Tm],P.CUSTCODE [Bank Code], A.CLATMID [ATM ID], PQR.LOCDESC [ATM Area], A.CLCALLSTATUS [Status Of Call],
    B.CALLTYPE [Type of Call] , CASE WHEN XYZ.vn_cnt > 0 THEN ''True'' else ''False'' END [MultiAcs],
    CASE WHEN XYZ.vn_cnt > 0 THEN ''True'' else ''False'' END [MultiAcYN] ,
    A.clOFFCD [OFF Cd] FROM CLLOG A INNER JOIN USER_CLTYPE B ON (A.CLCALLTYPE=B.CALLTYPE ) INNER JOIN CALLMASTER C ON (B.CALLNAME=C.CALLNAME AND A.CLCALLTYPE=C.CALLTYPE AND A.CLCALLACTION = C.CALLACTION)
    LEFT OUTER JOIN (SELECT T.ATMID,T.ATMLOCCD,ATMCOMPCD,ATMOFFCD,LOCDESC FROM MATM TINNER JOIN MLOCATION K ON T.ATMLOCCD=K.LOCCODE AND T.ATMOFFCD=K.OFFCODE AND T.ATMCITY=K.CITYCODE )PQR
    ON ( A.CLATMID=PQR.ATMID AND A.COMPCODE=PQR.ATMCOMPCD AND A.CLOFFCD=PQR.ATMOFFCD) LEFT OUTER JOIN (SELECT CUSTCODE,CUSTBRCODE,CUSTCUSTOMERCODE,COMPCODE,E.OFFCODE,E.CITYCODE,E.LOCCODE,LOCDESC,CUSTCUSTNAME,CALLTYPECODE FROM MCUSTCUSTOMER E
    INNER JOIN MLOCATION G ON E.LOCCODE =G.LOCCODE AND E.OFFCODE=G.OFFCODE AND E.CITYCODE=G.CITYCODE )ABC
    ON ( A.CLCUSTCD= ABC.CUSTCODE AND A.CLCUSTBRCD=ABC.CUSTBRCODE AND A.COMPCODE=ABC.COMPCODE AND A.CLOFFCD=ABC.OFFCODE AND A.CLCUSTCUSTCD=ABC.CUSTCUSTOMERCODE) INNER JOIN (
    SELECT F.CITYCODE, F.CITYDESC,CUSTBRNAME,CUSTCODE,CUSTBRCODE,COMPCODE,OFFCODE FROM MCUSTOMERBRANCH D INNER JOIN MCITY F ON D.CITYCODE=F.CITYCODE)P
    ON (A.CLCUSTCD=P.CUSTCODE AND A.CLCUSTBRCD=P.CUSTBRCODE AND A.COMPCODE=P.COMPCODE AND A.CLOFFCD=P.OFFCODE)LEFT OUTER JOIN (SELECT COUNT(*) VN_CNT, CUSTCUSTOMERCODE FROM MCUSTCUSTACCOUNT GROUP BY CUSTCUSTOMERCODE ) XYZ
    on XYZ.custcustomercode =ABC.CUSTCUSTOMERCODE LEFT OUTER JOIN (SELECT R.RTCODE,Q.FIX,Q.CLCALLNO,Q.CLACTCD,Q.CLCUSTCD,Q.CLCUSTBRCD,Q.CLOFFCD,Q.COMPCODE,Q.CLGENDATE,Q.CLCUSTCUSTCD FROM CLEXEC QINNER JOIN MROUTE R ON Q.RTCODE=R.RTCODE AND Q.COMPCODE=R.COMPCODE AND Q.CLOFFCD=R.OFFCODE)X
    ON (A.CLCALLNO=X.CLCALLNO AND A.CLACTCD=X.CLACTCD AND A.CLCUSTCD=X.CLCUSTCD AND A.CLCUSTBRCD=X.CLCUSTBRCD AND A.CLOFFCD=X.CLOFFCD AND A.COMPCODE=X.COMPCODE AND A.CLGENDATE=X.CLGENDATE AND (A.CLCUSTCUSTCD=X.CLCUSTCUSTCD OR A.CLCUSTCUSTCD IS NULL))
    WHERE CAST(CONVERT(DATETIME,A.CLACTDATE,' + @SQLDateFormat + ' ) AS CHAR(11)) BETWEEN CONVERT(DATETIME,''' + @CallFromDate + ''',' + @SQLDateFormat + ')
    AND CONVERT(DATETIME,''' + @CallToDate + ''', ' + @SQLDateFormat + ')
    AND A.COMPCODE=''' + @CompCode + '''
    AND B.USERID=''' + @UserID + ''' AND P.CITYCODE IN ( '+ @CityCode + ')
    AND B.CALLNAME IN (' + @CallTypeCode + ') AND A.CLCUSTBRCD IN (' + @BankCode + ')
    AND A.CLNATURE IN(' + @Frequency + ') AND A.CLCALLSTATUS IN (' + @CallStatus + ')
    AND ' + @RouteCode + 'AND A.LOGINAUTHUSERID IS NOT NULL
    AND A.CLCALLSTATUS <> ''CL''
    AND A.ClOffCd IN (' + @ClOffCd + ')
    AND A.ClCustCd IN (' + @ClCustCd + ')
    ORDER BY CLACTDATE, B.CALLNAME, A.CLCALLNO'
    Regards,Mahi
  2. satya Moderator

    Welcome to the forums.
    Are you running this as adhoc sql or as a Stored procedure?
  3. virmahi New Member

    This sql query is beig run inside a procedure. But I have put in the constant values for the variables that came from procedure arguements and trying to run this and optimize.
  4. preethi Member

    First of all, the query is not pasted with proper formatting. I tried to find the query, but as you have some inline SQL statements it is very difficult to find the query properly.
    Then, you are using a lot of tables, derived tables and case statements. When you join a lot of tables, you are guaranteed for performance degration.
    Thirdly, We are not sure of the indexes and sizes of the tables.
    Finally, we may have to look at the execution plan to see what is causing the issue.
    As a general rule, I will say, you need to reduce the number of tables using in a single query, by running multiple small number of queries and keep the data into temp tables. Also make sure you have proper indexes, including a clustered index on each table.
    Executionm plan will give you a starting point to see where the clustered index scans/table scans are taking place.
  5. virmahi New Member

    Hi Preethi, Thanks for looking into the query. I have pasted a formatted query only but once pasted here, it comes unformatted...don't know what could be the reason.
    I am trying to attach the execution plan for this query but don't know how.
    The size of some tables like ClLog is big and holds around 20 lakh records.
    There are clustered indexes on each table. I am new to optimization in sql server. Kindly advice if creating viws instead of inner querys will help or I wil have to create temp tables.
    |--Compute Scalar(DEFINE:([Expr1033]=CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(1) THEN [CSL_PRODUCTION].[dbo].[RtWiseCustomer].[RtCode] as [RT].[RtCode] ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(0) THEN [CSL_PRODUCTION].[dbo].[MROUTE].[RtCode] as [R].[RtCode] ELSE NULL END END, [Expr1042]=CASE WHEN [CSL_PRODUCTION].[dbo].[User_CLType].[CallType] as .[CallType]='CSH' THEN [CSL_PRODUCTION].[dbo].[User_CLType].[CallType] as .[CallType]+CASE WHEN [CSL_PRODUCTION].[dbo].[ClLog].[ClCallAction] as [A].[ClCallAction]='PKP' THEN '-P' ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClLog].[ClCallAction] as [A].[ClCallAction]='DLY' THEN '-D' ELSE NULL END END ELSE [CSL_PRODUCTION].[dbo].[User_CLType].[CallType] as .[CallType] END, [Expr1044]=CASE WHEN [CSL_PRODUCTION].[dbo].[User_CLType].[CallType] as .[CallType]='ATMREPL' THEN [CSL_PRODUCTION].[dbo].[ClLog].[ClAtmId] as [A].[ClAtmId] ELSE [CSL_PRODUCTION].[dbo].[MCUSTCUSTOMER].[CustCustName] as [E].[CustCustName] END, [Expr1045]=CASE WHEN [CSL_PRODUCTION].[dbo].[User_CLType].[CallType] as .[CallType]='ATMREPL' THEN isnull([CSL_PRODUCTION].[dbo].[MLocation].[LocDesc] as [K].[LocDesc],[CSL_PRODUCTION].[dbo].[MLocation].[LocDesc] as [G].[LocDesc]) ELSE [CSL_PRODUCTION].[dbo].[MLocation].[LocDesc] as [G].[LocDesc] END, [Expr1047]=CASE WHEN [CSL_PRODUCTION].[dbo].[ClLog].[ClCallStatus] as [A].[ClCallStatus]='OP' THEN (0.00) ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClLog].[ClCallStatus] as [A].[ClCallStatus]='AS' THEN (0.00) ELSE CASE WHEN ([CSL_PRODUCTION].[dbo].[User_CLType].[CallType] as .[CallType]+[CSL_PRODUCTION].[dbo].[ClLog].[ClCallAction] as [A].[ClCallAction])='CSHPKP' THEN isnull([CSL_PRODUCTION].[dbo].[ClLog].[ClAmt] as [A].[ClAmt],(0.00))+isnull([CSL_PRODUCTION].[dbo].[ClLog].[ClDiffAmt] as [A].[ClDiffAmt],(0.00)) ELSE CASE WHEN ([CSL_PRODUCTION].[dbo].[User_CLType].[CallType] as .[CallType]+[CSL_PRODUCTION].[dbo].[ClLog].[ClCallAction] as [A].[ClCallAction])='CSHCHQPKP' THEN isnull([CSL_PRODUCTION].[dbo].[ClLog].[ClAmt] as [A].[ClAmt],(0.00))+isnull([CSL_PRODUCTION].[dbo].[ClLog].[ClDiffAmt] as [A].[ClDiffAmt],(0.00)) ELSE CASE WHEN ([CSL_PRODUCTION].[dbo].[User_CLType].[CallType] as .[CallType]+[CSL_PRODUCTION].[dbo].[ClLog].[ClCallAction] as [A].[ClCallAction])='CSHDLY' THEN isnull([CSL_PRODUCTION].[dbo].[ClLog].[ClAmt] as [A].[ClAmt],(0.00))-isnull([CSL_PRODUCTION].[dbo].[ClLog].[ClDiffAmt] as [A].[ClDiffAmt],(0.00)) ELSE CASE WHEN ([CSL_PRODUCTION].[dbo].[User_CLType].[CallType] as .[CallType]+[CSL_PRODUCTION].[dbo].[ClLog].[ClCallAction] as [A].[ClCallAction])='ATMREPLATM' THEN isnull([CSL_PRODUCTION].[dbo].[ClLog].[ClAmt] as [A].[ClAmt],(0.00))+isnull([CSL_PRODUCTION].[dbo].[ClLog].[ClDiffAmt] as [A].[ClDiffAmt],(0.00)) ELSE CASE WHEN ([CSL_PRODUCTION].[dbo].[User_CLType].[CallType] as .[CallType]+[CSL_PRODUCTION].[dbo].[ClLog].[ClCallAction] as [A].[ClCallAction])='CSHWBNKPKP' THEN isnull([CSL_PRODUCTION].[dbo].[ClLog].[ClAmt] as [A].[ClAmt],(0.00))+isnull([CSL_PRODUCTION].[dbo].[ClLog].[ClDiffAmt] as [A].[ClDiffAmt],(0.00)) ELSE CASE WHEN ([CSL_PRODUCTION].[dbo].[User_CLType].[CallType] as .[CallType]+[CSL_PRODUCTION].[dbo].[ClLog].[ClCallAction] as [A].[ClCallAction])='CSHDBNKDLY' THEN isnull([CSL_PRODUCTION].[dbo].[ClLog].[ClAmt] as [A].[ClAmt],(0.00))-isnull([CSL_PRODUCTION].[dbo].[ClLog].[ClDiffAmt] as [A].[ClDiffAmt],(0.00)) ELSE (0.00) END END END END END END END END, [Expr1051]=CASE WHEN [Expr1025]>(0) THEN 'True' ELSE 'False' END, [Expr1052]=CASE WHEN [Expr1025]>(0) THEN 'True' ELSE 'False' END))
    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([E].[CustCustomerCode]))
    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([A].[ClOffCd], [A].[ClCustCd], [A].[ClCustBrCd], [A].[ClCustCustCd]))
    | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([A].[ClOffCd], [A].[ClAtmId]))
    | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([A].[ClExecUserId]))
    | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([A].[LoginAuthUserId]))
    | | | | |--Filter(WHERE:(CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(1) THEN [CSL_PRODUCTION].[dbo].[RtWiseCustomer].[RtCode] as [RT].[RtCode] ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(0) THEN [CSL_PRODUCTION].[dbo].[MROUTE].[RtCode] as [R].[RtCode] ELSE NULL END END='000010' OR CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(1) THEN [CSL_PRODUCTION].[dbo].[RtWiseCustomer].[RtCode] as [RT].[RtCode] ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(0) THEN [CSL_PRODUCTION].[dbo].[MROUTE].[RtCode] as [R].[RtCode] ELSE NULL END END='000011' OR CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(1) THEN [CSL_PRODUCTION].[dbo].[RtWiseCustomer].[RtCode] as [RT].[RtCode] ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(0) THEN [CSL_PRODUCTION].[dbo].[MROUTE].[RtCode] as [R].[RtCode] ELSE NULL END END='000012' OR CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(1) THEN [CSL_PRODUCTION].[dbo].[RtWiseCustomer].[RtCode] as [RT].[RtCode] ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(0) THEN [CSL_PRODUCTION].[dbo].[MROUTE].[RtCode] as [R].[RtCode] ELSE NULL END END='000013' OR CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(1) THEN [CSL_PRODUCTION].[dbo].[RtWiseCustomer].[RtCode] as [RT].[RtCode] ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(0) THEN [CSL_PRODUCTION].[dbo].[MROUTE].[RtCode] as [R].[RtCode] ELSE NULL END END='000014' OR CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(1) THEN [CSL_PRODUCTION].[dbo].[RtWiseCustomer].[RtCode] as [RT].[RtCode] ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(0) THEN [CSL_PRODUCTION].[dbo].[MROUTE].[RtCode] as [R].[RtCode] ELSE NULL END END='000015' OR CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(1) THEN [CSL_PRODUCTION].[dbo].[RtWiseCustomer].[RtCode] as [RT].[RtCode] ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(0) THEN [CSL_PRODUCTION].[dbo].[MROUTE].[RtCode] as [R].[RtCode] ELSE NULL END END='000016' OR CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(1) THEN [CSL_PRODUCTION].[dbo].[RtWiseCustomer].[RtCode] as [RT].[RtCode] ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(0) THEN [CSL_PRODUCTION].[dbo].[MROUTE].[RtCode] as [R].[RtCode] ELSE NULL END END='000017' OR CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(1) THEN [CSL_PRODUCTION].[dbo].[RtWiseCustomer].[RtCode] as [RT].[RtCode] ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(0) THEN [CSL_PRODUCTION].[dbo].[MROUTE].[RtCode] as [R].[RtCode] ELSE NULL END END='000018' OR CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(1) THEN [CSL_PRODUCTION].[dbo].[RtWiseCustomer].[RtCode] as [RT].[RtCode] ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(0) THEN [CSL_PRODUCTION].[dbo].[MROUTE].[RtCode] as [R].[RtCode] ELSE NULL END END='000019' OR CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(1) THEN [CSL_PRODUCTION].[dbo].[RtWiseCustomer].[RtCode] as [RT].[RtCode] ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(0) THEN [CSL_PRODUCTION].[dbo].[MROUTE].[RtCode] as [R].[RtCode] ELSE NULL END END='000020' OR CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(1) THEN [CSL_PRODUCTION].[dbo].[RtWiseCustomer].[RtCode] as [RT].[RtCode] ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(0) THEN [CSL_PRODUCTION].[dbo].[MROUTE].[RtCode] as [R].[RtCode] ELSE NULL END END='000021' OR CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(1) THEN [CSL_PRODUCTION].[dbo].[RtWiseCustomer].[RtCode] as [RT].[RtCode] ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(0) THEN [CSL_PRODUCTION].[dbo].[MROUTE].[RtCode] as [R].[RtCode] ELSE NULL END END='000022' OR CASE WHEN [CSL_PRODUCTION].[dbo].[ClExec].[Fix] as [Q].[Fix]=(1) THEN [CSL..
    | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Q].[Offcode], [Q].[ClCustCustCd]))
    | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([C].[CallName], [C].[CallType]))
    | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([D].[CityCode]))
    | | | | | | | |--Hash Match(Inner Join, HASH:([C].[CallType], [C].[CallAction])=([A].[ClCallType], [A].[ClCallAction]), RESIDUAL:([CSL_PRODUCTION].[dbo].[ClLog].[ClCallType] as [A].[ClCallType]=[CSL_PRODUCTION].[dbo].[CallMaster].[CallType] as [C].[CallType] AND [CSL_PRODUCTION].[dbo].[ClLog].[ClCallAction] as [A].[ClCallAction]=[CSL_PRODUCTION].[dbo].[CallMaster].[CallAction] as [C].[CallAction]))
    | | | | | | | | |--Clustered Index Seek(OBJECT:([CSL_PRODUCTION].[dbo].[CallMaster].[PK_CallMaster] AS [C]), SEEK:([C].[CallName] >= 'CASH DELIVERY' AND [C].[CallName] p>
    | | | | | | | | |--Compute Scalar(DEFINE:([Expr1043]=CONVERT(char(10),[CSL_PRODUCTION].[dbo].[ClLog].[ClActDate] as [A].[ClActDate],103), [Expr1046]=isnull([CSL_PRODUCTION].[dbo].[ClLog].[ClAmt] as [A].[ClAmt],(0.00)), [Expr1048]=isnull([CSL_PRODUCTION].[dbo].[ClLog].[ClDiffAmt] as [A].[ClDiffAmt],(0.00)), [Expr1049]=CASE WHEN [CSL_PRODUCTION].[dbo].[ClLog].[ClCallStatus] as [A].[ClCallStatus]='OP' THEN 'Open' ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClLog].[ClCallStatus] as [A].[ClCallStatus]='AS' THEN 'Assigned' ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClLog].[ClCallStatus] as [A].[ClCallStatus]='AT' THEN 'Attended' ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClLog].[ClCallStatus] as [A].[ClCallStatus]='SK' THEN 'Skipped' ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClLog].[ClCallStatus] as [A].[ClCallStatus]='CN' THEN 'Cancelled' ELSE CASE WHEN [CSL_PRODUCTION].[dbo].[ClLog].[ClCallStatus] as [A].[ClCallStatus]='CL' THEN 'Closed' ELSE NULL END END END END END END, [Expr1050]=CONVERT(char(10),[CSL_PRODUCTION].[dbo].[ClLog].[ClGenDate] as [A].[ClGenDate],103)))
    | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Q].[ClActCd], [Q].[ClCallNo], [Q].[ClCustCd], [Q].[ClCustBrCd], [Q].[ClGenDate], [Q].[ClCustCustCd], [R].[OffCode], [Expr1518]) WITH UNORDERED PREFETCH)
    | | | | | | | | |--Hash Match(Inner Join, HASH:([R].[OffCode], [R].[RtCode])=([Q].[ClOffCd], [Q].[RtCode]), RESIDUAL:([CSL_PRODUCTION].[dbo].[ClExec].[ClOffCd] as [Q].[ClOffCd]=[CSL_PRODUCTION].[dbo].[MROUTE].[OffCode] as [R].[OffCode] AND [CSL_PRODUCTION].[dbo].[ClExec].[RtCode] as [Q].[RtCode]=[CSL_PRODUCTION].[dbo].[MROUTE].[RtCode] as [R].[RtCode]))
    | | | | | | | | | |--Clustered Index Scan(OBJECT:([CSL_PRODUCTION].[dbo].[MROUTE].[PK_MROUTE] AS [R]), WHERE:([CSL_PRODUCTION].[dbo].[MROUTE].[CompCode] as [R].[CompCode]='CSL'))
    | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([D].[CustCode], [D].[CustBrCode], [D].[OffCode], [Expr1517]) OPTIMIZED WITH UNORDERED PREFETCH)
    | | | | | | | | | |--Index Seek(OBJECT:([CSL_PRODUCTION].[dbo].[MCustomerBranch].[nPK_MCustomerBrCity] AS [D]), SEEK:(([D].[CityCode], [D].[CompCode], [D].[CustBrCode]) >= ('022', 'CSL', 'ABC022') AND ([D].[CityCode], [D].[CompCode], [D].[CustBrCode]) ='ABC022' AND [CSL_PRODUCTION].[dbo].[MCustomerBranch].[CustBrCode] as [D].[CustBrCode]p>
    | | | | | | | | | |--Index Seek(OBJECT:([CSL_PRODUCTION].[dbo].[ClExec].[_dta_index_ClExec_16_646293362__K3_K4_K19_K6_K20_K22_K5_K18_K1_K2_K7] AS [Q]), SEEK:([Q].[ClCustCd]=[CSL_PRODUCTION].[dbo].[MCustomerBranch].[CustCode] as [D].[CustCode] AND [Q].[ClCustBrCd]=[CSL_PRODUCTION].[dbo].[MCustomerBranch].[CustBrCode] as [D].[CustBrCode]), WHERE:([CSL_PRODUCTION].[dbo].[MCustomerBranch].[OffCode] as [D].[OffCode]=[CSL_PRODUCTION].[dbo].[ClExec].[ClOffCd] as [Q].[ClOffCd] AND [CSL_PRODUCTION].[dbo].[ClExec].[CompCode] as [Q].[CompCode]='CSL' AND [CSL_PRODUCTION].[dbo].[ClExec].[ClCustBrCd] as [Q].[ClCustBrCd]>='ABC022' AND [CSL_PRODUCTION].[dbo].[ClExec].[ClCustBrCd] as [Q].[ClCustBrCd]p>
    | | | | | | | | |--Filter(WHERE:([CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='ABC022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='ABC261' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='ABR022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='ABR261' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='AMR022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='AU0022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='AXC022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='AXC261' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='AXR261' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='BA0022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='BH0261' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='BP0022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='CBC022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='CBC261' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='CF0022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='CIC022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='CRC022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='CTC022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='CTC261' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='CTR022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='CTR261' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='DBC022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='DBC261' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='DBR022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='DC0022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='DCR022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='GAC022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='HBC022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='HBC261' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='HBR022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='HCC022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='HCF022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='HD0022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='HD0261' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='HPC261' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='HPL022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='ICC022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='ICC261' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='ICR022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='ICR261' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='IDC022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='IDC261' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='IDR022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='IDR261' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='IGC022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='II0022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='IIR022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='IN0022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='IP0022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='IP0261' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]='IR0022' OR [CSL_PRODUCTION].[dbo].[ClLog].[ClCus..
    | | | | | | | | |--Clustered Index Seek(OBJECT:([CSL_PRODUCTION].[dbo].[ClLog].[PK_ClLog] AS [A]), SEEK:([A].[ClActCd]=[CSL_PRODUCTION].[dbo].[ClExec].[ClActCd] as [Q].[ClActCd] AND [A].[ClCallNo]=[CSL_PRODUCTION].[dbo].[ClExec].[ClCallNo] as [Q].[ClCallNo] AND [A].[ClOffCd]=[CSL_PRODUCTION].[dbo].[MROUTE].[OffCode] as [R].[OffCode] AND [A].[ClCustCd]=[CSL_PRODUCTION].[dbo].[ClExec].[ClCustCd] as [Q].[ClCustCd] AND [A].[ClCustBrCd]=[CSL_PRODUCTION].[dbo].[ClExec].[ClCustBrCd] as [Q].[ClCustBrCd] AND [A].[CompCode]='CSL' AND [A].[ClGenDate]=[CSL_PRODUCTION].[dbo].[ClExec].[ClGenDate] as [Q].[ClGenDate]), WHERE:(CONVERT_IMPLICIT(datetime,CONVERT(char(11),CONVERT(datetime,[CSL_PRODUCTION].[dbo].[ClLog].[ClActDate] as [A].[ClActDate],103),0),0)>='2008-12-05 00:00:00.000' AND CONVERT_IMPLICIT(datetime,CONVERT(char(11),CONVERT(datetime,[CSL_PRODUCTION].[dbo].[ClLog].[ClActDate] as [A].[ClActDate],103),0),0)p>
    | | | | | | | |--Clustered Index Seek(OBJECT:([CSL_PRODUCTION].[dbo].[MCity].[PK_MCity] AS [F]), SEEK:([F].[CityCode]=[CSL_PRODUCTION].[dbo].[MCustomerBranch].[CityCode] as [D].[CityCode]), WHERE:([CSL_PRODUCTION].[dbo].[MCity].[CityCode] as [F].[CityCode]='022' OR [CSL_PRODUCTION].[dbo].[MCity].[CityCode] as [F].[CityCode]='0261') ORDERED FORWARD)
    | | | | | | |--Clustered Index Seek(OBJECT:([CSL_PRODUCTION].[dbo].[User_CLType].[PK_User_CLType] AS ), SEEK:(.[UserId]='HO0022' AND .[CallName]=[CSL_PRODUCTION].[dbo].[CallMaster].[CallName] as [C].[CallName] AND .[CallType]=[CSL_PRODUCTION].[dbo].[CallMaster].[CallType] as [C].[CallType]), WHERE:([CSL_PRODUCTION].[dbo].[User_CLType].[CallName] as .[CallName]='CASH DELIVERY' OR [CSL_PRODUCTION].[dbo].[User_CLType].[CallName] as .[CallName]='CASH PICKUP') ORDERED FORWARD)
    | | | | | |--Index Seek(OBJECT:([CSL_PRODUCTION].[dbo].[RtWiseCustomer].[_dta_index_RtWiseCustomer_16_661577395__K4_K3_K2_1] AS [RT]), SEEK:([RT].[CompCode]='CSL' AND [RT].[CustCustCode]=[CSL_PRODUCTION].[dbo].[ClExec].[ClCustCustCd] as [Q].[ClCustCustCd] AND [RT].[OffCode]=[CSL_PRODUCTION].[dbo].[ClExec].[Offcode] as [Q].[Offcode]) ORDERED FORWARD)
    | | | | |--Clustered Index Seek(OBJECT:([CSL_PRODUCTION].[dbo].[User_HD].[PK_User_HD] AS [UH1]), SEEK:([UH1].[UserId]=[CSL_PRODUCTION].[dbo].[ClLog].[LoginAuthUserId] as [A].[LoginAuthUserId]) ORDERED FORWARD)
    | | | |--Clustered Index Seek(OBJECT:([CSL_PRODUCTION].[dbo].[User_HD].[PK_User_HD] AS [UH2]), SEEK:([UH2].[UserId]=[CSL_PRODUCTION].[dbo].[ClLog].[ClExecUserId] as [A].[ClExecUserId]) ORDERED FORWARD)
    | | |--Nested Loops(Inner Join, OUTER REFERENCES:([T].[ATMLOCCD], [T].[ATMCITY]))
    | | |--Clustered Index Scan(OBJECT:([CSL_PRODUCTION].[dbo].[MATM].[PK_MATM] AS [T]), WHERE:([CSL_PRODUCTION].[dbo].[ClLog].[ClAtmId] as [A].[ClAtmId]=[CSL_PRODUCTION].[dbo].[MATM].[ATMID] as [T].[ATMID] AND [CSL_PRODUCTION].[dbo].[ClLog].[ClOffCd] as [A].[ClOffCd]=[CSL_PRODUCTION].[dbo].[MATM].[ATMOFFCD] as [T].[ATMOFFCD] AND [CSL_PRODUCTION].[dbo].[MATM].[ATMCOMPCD] as [T].[ATMCOMPCD]='CSL'))
    | | |--Clustered Index Seek(OBJECT:([CSL_PRODUCTION].[dbo].[MLocation].[PK_MLocation] AS [K]), SEEK:([K].[LocCode]=[CSL_PRODUCTION].[dbo].[MATM].[ATMLOCCD] as [T].[ATMLOCCD] AND [K].[OffCode]=[CSL_PRODUCTION].[dbo].[ClLog].[ClOffCd] as [A].[ClOffCd]), WHERE:([CSL_PRODUCTION].[dbo].[MATM].[ATMCITY] as [T].[ATMCITY]=[CSL_PRODUCTION].[dbo].[MLocation].[CityCode] as [K].[CityCode]) ORDERED FORWARD)
    | |--Nested Loops(Inner Join, OUTER REFERENCES:([E].[LocCode], [E].[CityCode]))
    | |--Clustered Index Seek(OBJECT:([CSL_PRODUCTION].[dbo].[MCUSTCUSTOMER].[PK_MCUSTCUSTOMER] AS [E]), SEEK:([E].[CustCustomerCode]=[CSL_PRODUCTION].[dbo].[ClLog].[ClCustCustCd] as [A].[ClCustCustCd] AND [E].[CompCode]='CSL'), WHERE:([CSL_PRODUCTION].[dbo].[ClLog].[ClCustCd] as [A].[ClCustCd]=[CSL_PRODUCTION].[dbo].[MCUSTCUSTOMER].[CustCode] as [E].[CustCode] AND [CSL_PRODUCTION].[dbo].[ClLog].[ClCustBrCd] as [A].[ClCustBrCd]=[CSL_PRODUCTION].[dbo].[MCUSTCUSTOMER].[CustBrCode] as [E].[CustBrCode] AND [CSL_PRODUCTION].[dbo].[ClLog].[ClOffCd] as [A].[ClOffCd]=[CSL_PRODUCTION].[dbo].[MCUSTCUSTOMER].[OffCode] as [E].[OffCode] AND [CSL_PRODUCTION].[dbo].[MCUSTCUSTOMER].[CustBrCode] as [E].[CustBrCode]>='ABC022' AND [CSL_PRODUCTION].[dbo].[MCUSTCUSTOMER].[CustBrCode] as [E].[CustBrCode]p>
    | |--Clustered Index Seek(OBJECT:([CSL_PRODUCTION].[dbo].[MLocation].[PK_MLocation] AS [G]), SEEK:([G].[LocCode]=[CSL_PRODUCTION].[dbo].[MCUSTCUSTOMER].[LocCode] as [E].[LocCode] AND [G].[OffCode]=[CSL_PRODUCTION].[dbo].[ClLog].[ClOffCd] as [A].[ClOffCd]), WHERE:([CSL_PRODUCTION].[dbo].[MCUSTCUSTOMER].[CityCode] as [E].[CityCode]=[CSL_PRODUCTION].[dbo].[MLocation].[CityCode] as [G].[CityCode]) ORDERED FORWARD)
    |--Compute Scalar(DEFINE:([Expr1025]=CONVERT_IMPLICIT(int,[Expr1519],0)))
    |--Stream Aggregate(DEFINE:([Expr1519]=Count(*)))
    |--Clustered Index Seek(OBJECT:([CSL_PRODUCTION].[dbo].[MCustCustAccount].[PK_MCustCustAccount]), SEEK:([CSL_PRODUCTION].[dbo].[MCustCustAccount].[CustCustomerCode]=[CSL_PRODUCTION].[dbo].[MCUSTCUSTOMER].[CustCustomerCode] as [E].[CustCustomerCode]) ORDERED FORWARD)
  6. preethi Member

    Sorry for the delayed response. I tried to understand the execution plan, it is difficult with this format.. you can get the execution plan in xml format, which may allow use to save and view properly. additionally, knowing the cost is something important to see which parts cause the issues.
    USE set statistics xml on to geerate the execution plan in xml format.

Share This Page