SQL Server Performance

Use part of Clustered Index?

Discussion in 'Performance Tuning for DBAs' started by benwilson, Jul 28, 2005.

  1. benwilson New Member

    Heya,
    I have the below table with a clustered index on ApplicationResultId and ProductName. We regularly do queries based just on the product name. Is SQL server able to use the clustered index to look at just ProductName, or should i create an index on ProductName as well??


    CREATE TABLE [dbo].[tbl_ProductResult](
    [ApplicationResultId] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
    [ProductName] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
    [BranchBSB] [nvarchar](6) COLLATE Latin1_General_CI_AS NULL,
    [InterestOption] [nvarchar](10) COLLATE Latin1_General_CI_AS NULL,
    [PaymentFrequency] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
    [UniqueId] [bigint] IDENTITY(1,1) NOT NULL
    CONSTRAINT [PK_tbl_ProductResult] PRIMARY KEY CLUSTERED
    (
    [ApplicationResultId] ASC,
    [ProductName] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]

    Thanks in advance,
    Ben

    'I reject your reality and substitute my own' - Adam Savage
  2. gurucb New Member

    As I understand you are using productname with where clause...

    then iwould think it makes sense to create a non clustered index on productname.

  3. benwilson New Member

    Thanks for that.<br />yes, thats right- i would use ProductName in the where clause. I will add it in and see how i go <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />'I reject your reality and substitute my own' - Adam Savage
  4. Luis Martin Moderator

    After create non cluster, look execution plan to find out if is used.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  5. Adriaan New Member

    You could look at the order of the columns in the clustered index. If you put ProductName before ApplicationResultId I guess the clustered index would be organized in the same way as a clustered index on ProductName alone would be.

    Of course you may run into other problems going that way (index fragmentation) so tread carefully.
  6. SQL_Guess New Member

    Interesting that you are using (ApplicationResultId] ASC, [ProductName] ASC) as a composite primary key, when you have ([UniqueId] [bigint] IDENTITY(1,1) NOT NULL) which is (imho) obivously there for uniqueness. Does that mean (1) UniqueId is unnecessary; (2) (ApplicationResultId] ASC, [ProductName] ASC) is not always unqiue; or (3) you have that alternate key for other reasons?

    Panic, Chaos, Disorder ... my work here is done --unknown
  7. Adriaan New Member

    quote:Originally posted by SQL_Guess

    Interesting that you are using (ApplicationResultId] ASC, [ProductName] ASC) as a composite primary key, when you have ([UniqueId] [bigint] IDENTITY(1,1) NOT NULL) which is (imho) obivously there for uniqueness. Does that mean (1) UniqueId is unnecessary; (2) (ApplicationResultId] ASC, [ProductName] ASC) is not always unqiue; or (3) you have that alternate key for other reasons?

    Panic, Chaos, Disorder ... my work here is done --unknown
    That is a good point, but we don't know if ApplicationResultId is perhaps an fake FK (one that can be NULL).
  8. Adriaan New Member

    quote:That is a good point, but we don't know if ApplicationResultId is perhaps an fake FK (one that can be NULL).
    Incorrect assumption, my fault entirely ... the table definition says ApplicationResultId can NOT be null.

    But of course it is an NVARCHAR(50) column, so it is fine that there's a substitute PK.
  9. benwilson New Member

    Thanks all,<br />First, let me say that i inherited the database with all its quirks <img src='/community/emoticons/emotion-1.gif' alt=':)' /> (it might ber argued that i am adding my own quirks to it now though!) The standard used throughout is to have primary keys based on meaningful data (in this case, a the name of a product we offer and an ApplicationResult number) but to have an identifier column, UniqueId, that is used by the application that accesses the data...i guess it could be argued that UniqueId is not strictly necessary, but getting rid of it at this stage would be a HUGE task!<br /><br />I will add the index this morning and see how i go!<br /><br />Thanks again<br />Ben<br /><br />'I reject your reality and substitute my own' - Adam Savage
  10. benwilson New Member

    ok, so performance seems to have declined on the procedure i am tuning after i added the index, even though it is not being used in the execution plan! This procedure is weird- everything that should be improving its performance seems to be hurting it!

    'I reject your reality and substitute my own' - Adam Savage
  11. gurucb New Member

    Put in execution plan for the query it might be doing bookmark lookup that might be affecting performance.

  12. Adriaan New Member

    Statistics may be old. Do you update statistics as part of maintenance plan?
  13. benwilson New Member

    Hi,<br />All the indexes are rebuilt once a week, and i believe the statistics are recalculated when the indexes are built so they are at worst a week old.<br /><br />Here is the execution plan for the proc i am tuning:<br /><pre id="code"><font face="courier" size="2" id="code">StmtText<br /> |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1034]=Convert([tbl_ProductResultSubmission].[SubmissionDate]), [Expr1035]=upper(substring([tbl_Applicant].[FirstName], 1, 1))+'. '+[tbl_Applicant].[LastName], [Expr1036]=substring([tbl_ProductResult].[ResultStatus], 1, 3), [Expr1037]=Convert([Expr1002]), [Expr1038]=Convert([Expr1004]), [Expr1039]=Convert([Expr1003]), [Expr1041]=[Expr1041]))<br /> |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ProductResultSubmission].[AssignedTerminalId]))<br /> |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ProductResultSubmission].[SubmissionDate] DESC))<br /> | |--Filter(WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />Convert([@ShowWithdrawn_])=1 OR ([@ShowWithdrawn_]=NULL OR Convert([@ShowWithdrawn_])=0 AND [tbl_ApplicationWithdraw].[TransactionId]=NULL)))<br /> | |--Merge Join(Right Outer Join, MERGE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ApplicationWithdraw].[TransactionId])=([tbl_ApplicationResult].[TransactionId]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ApplicationResult].[TransactionId]=[tbl_ApplicationWithdraw].[TransactionId]))<br /> | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FNChris].[dbo].[tbl_ApplicationWithdraw].[PK_tbl_ApplicationWithdraw]), ORDERED FORWARD)<br /> | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_Application].[TransactionId] ASC))<br /> | |--Hash Match(Left Outer Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_Application].[TransactionId])=([tbl_Applicant].[TransactionId]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_Application].[TransactionId]=[tbl_Applicant].[TransactionId]))<br /> | |--Filter(WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />(Convert([@ShowSettled_])=1 OR ([@ShowSettled_]=NULL OR Convert([@ShowSettled_])=0 AND [tbl_ApplicationSettlement].[LoanPrincipal]=NULL)) AND ((([@LenderDecision_]=NULL OR ([@LenderDecision_]='Approved' AND [Expr1002]&lt;&gt;NULL)) OR ([@LenderDecision_]='Declined' AND [Expr1004]&lt;&gt;NULL)) OR ((([@LenderDecision_]='Pending' AND [Expr1002]=NULL) AND [Expr1004]=NULL) AND [tbl_ApplicationSettlement].[LoanPrincipal]=NULL))))<br /> | | |--Merge Join(Right Outer Join, MERGE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ApplicationSettlement].[TransactionId])=([tbl_Application].[TransactionId]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_Application].[TransactionId]=[tbl_ApplicationSettlement].[TransactionId]))<br /> | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FNChris].[dbo].[tbl_ApplicationSettlement].[PK_tbl_ApplicationSettlement]), ORDERED FORWARD)<br /> | | |--Filter(WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[@ApplicantName_]=NULL OR [Expr1068]))<br /> | | |--Nested Loops(Left Semi Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[@ApplicantName_]=NULL)OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_Application].[TransactionId]), DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1068] = [PROBE VALUE]))<br /> | | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_Application].[TransactionId] ASC))<br /> | | | |--Filter(WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ProductResult].[ProductName]=[@ProductName_] OR ([@ProductName_]=NULL AND [Expr1067])))<br /> | | | |--Nested Loops(Left Semi Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ProductResult].[ProductName]=[@ProductName_] OR IsFalseOrNull([@ProductName_]=NULL))OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ProductResultSubmission].[ProductName]), DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1067] = [PROBE VALUE]))<br /> | | | |--Hash Match(Right Outer Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_RegisteredProduct].[ProductName])=([tbl_ProductResult].[ProductName]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_RegisteredProduct].[ProductName]=[tbl_ProductResult].[ProductName]))<br /> | | | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FNChris].[dbo].[tbl_RegisteredProduct].[PK_tbl_RegisteredProducts]))<br /> | | | | |--Hash Match(Left Outer Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ProductResult].[ApplicationResultId], [tbl_ProductResult].[ProductName])=([Expr1078], [Expr1079]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1078]=[tbl_ProductResult].[ApplicationResultId] AND [Expr1079]=[tbl_ProductResult].[ProductName]))<br /> | | | | |--Merge Join(Right Outer Join, MERGE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ProductResultSubmissionDecision].[ApplicationResultId], [tbl_ProductResultSubmissionDecision].[ProductName])=([tbl_ProductResultSubmission].[ApplicationResultId], [tbl_ProductResultSubmission].[ProductName]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ProductResultSubmissionDecision].[ApplicationResultId]=[tbl_ProductResultSubmission].[ApplicationResultId] AND [tbl_ProductResultSubmissionDecision].[ProductName]=[tbl_ProductResultSubmission].[ProductName]))<br /> | | | | | |--Stream Aggregate(GROUP BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ProductResultSubmissionDecision].[ApplicationResultId], [tbl_ProductResultSubmissionDecision].[ProductName]) DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1002]=MAX(If ([tbl_ProductResultSubmissionDecision].[LenderDecision]='Approval') then [tbl_ProductResultSubmissionDecision].[DecisionDate] else NULL), [Expr1003]=MAX(If ([tbl_ProductResultSubmissionDecision].[LenderDecision]='Query') then [tbl_ProductResultSubmissionDecision].[DecisionDate] else NULL), [Expr1004]=MAX(If ([tbl_ProductResultSubmissionDecision].[LenderDecision]='Declined') then [tbl_ProductResultSubmissionDecision].[DecisionDate] else NULL)))<br /> | | | | | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FNChris].[dbo].[tbl_ProductResultSubmissionDecision].[PK_tbl_ProductResultSubmissionDecision]), ORDERED FORWARD)<br /> | | | | | |--Merge Join(Inner Join, MERGE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ProductResult].[ApplicationResultId], [tbl_ProductResult].[ProductName])=([tbl_ApplicationResult].[ApplicationResultId], [tbl_ProductResultSubmission].[ProductName]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ProductResult].[ApplicationResultId]=[tbl_ApplicationResult].[ApplicationResultId] AND [tbl_ProductResultSubmission].[ProductName]=[tbl_ProductResult].[ProductName]))<br /> | | | | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FNChris].[dbo].[tbl_ProductResult].[PK_tbl_ProductResult]), ORDERED FORWARD)<br /> | | | | | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_Application].[LastApplicationResultId] ASC, [tbl_ProductResultSubmission].[ProductName] ASC))<br /> | | | | | |--Hash Match(Inner Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_TerminalProfileManagementDependency].[Profile])=([tbl_Terminal_2].[Profile]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_Terminal_2].[Profile]=[tbl_TerminalProfileManagementDependency].[Profile]))<br /> | | | | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FNChris].[dbo].[tbl_TerminalProfileManagementDependency].[PK_TerminalProfileManagementDependency]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_TerminalProfileManagementDependency].[TerminalId]=[@TerminalId_]))<br /> | | | | | |--Hash Match(Inner Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_Terminal_2].[TerminalId])=([tbl_Transaction].[DistributorId]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_Transaction].[DistributorId]=[tbl_Terminal_2].[TerminalId]))<br /> | | | | | |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FNChris].[dbo].[tbl_Terminal] AS [tbl_Terminal_2]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[@ReferenceNo_]=NULL OR [tbl_Terminal_2].[TerminalReferenceNo]=[@ReferenceNo_]))<br /> | | | | | |--Hash Match(Inner Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ProductResultSubmission].[ApplicationResultId])=([tbl_ApplicationResult].[ApplicationResultId]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ProductResultSubmission].[ApplicationResultId]=[tbl_ApplicationResult].[ApplicationResultId]))<br /> | | | | | |--Filter(WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />STARTUP EXPR(([@AssignmentStatus_]=NULL OR [@AssignmentStatus_]='Unassigned') OR [@AssignmentStatus_]='Assigned')))<br /> | | | | | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FNChris].[dbo].[tbl_ProductResultSubmission].[PK_tbl_ProductResultSubmission]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />(([tbl_ProductResultSubmission].[SubmissionDate]&lt;&gt;NULL AND (([@AssignmentStatus_]=NULL OR ([@AssignmentStatus_]='Unassigned' AND [tbl_ProductResultSubmission].[AssignedTerminalId]=NULL)) OR ([@AssignmentStatus_]='Assigned' AND [tbl_ProductResultSubmission].[AssignedTerminalId]=[@TerminalId_]))) AND ([@FromDate_]=NULL OR [tbl_ProductResultSubmission].[SubmissionDate]&gt;=Convert([@FromDate_]))) AND ([@ToDate_]=NULL OR [tbl_ProductResultSubmission].[SubmissionDate]&lt;=[@DateFormatToDate])))<br /> | | | | | |--Merge Join(Inner Join, MERGE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ApplicationResult].[TransactionId])=([tbl_Transaction].[TransactionId]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ApplicationResult].[TransactionId]=[tbl_Transaction].[TransactionId]))<br /> | | | | | |--Merge Join(Inner Join, MERGE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ApplicationResult].[TransactionId], [tbl_ApplicationResult].[ApplicationResultId])=([tbl_Application].[TransactionId], [tbl_Application].[LastApplicationResultId]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_Application].[LastApplicationResultId]=[tbl_ApplicationResult].[ApplicationResultId] AND [tbl_Application].[TransactionId]=[tbl_ApplicationResult].[TransactionId]))<br /> | | | | | | |--Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FNChris].[dbo].[tbl_ApplicationResult].[IX_tbl_ApplicationResult_TransactionId]), ORDERED FORWARD)<br /> | | | | | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FNChris].[dbo].[tbl_Application].[PK_tbl_Application]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />Convert([tbl_Application].[Demo])&lt;&gt;1) ORDERED FORWARD)<br /> | | | | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FNChris].[dbo].[tbl_Transaction].[PK_tbl_Transaction]), ORDERED FORWARD)<br /> | | | | |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1078]=Convert([tbl_ProductResultFee].[ApplicationResultId]), [Expr1079]=Convert([tbl_ProductResultFee].[ProductName])))<br /> | | | | |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1007]=If ([Expr1081]=0) then NULL else [Expr1082]))<br /> | | | | |--Stream Aggregate(GROUP BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ProductResultFee].[ApplicationResultId], [tbl_ProductResultFee].[ProductName]) DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1081]=COUNT_BIG([tbl_ProductResultFee].[FeeAmount]), [Expr1082]=SUM([tbl_ProductResultFee].[FeeAmount])))<br /> | | | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FNChris].[dbo].[tbl_ProductResultFee].[PK_tbl_ProductResultFee]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ProductResultFee].[FeeType]='Initial' AND [tbl_ProductResultFee].[FeeCode]&lt;&gt;'NABFEE3') ORDERED FORWARD)<br /> | | | |--Row Count Spool<br /> | | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FNChris].[dbo].[tbl_TerminalProduct].[PK_tbl_TerminalProduct]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_TerminalProduct].[TerminalId]=[@TerminalId_] AND [tbl_TerminalProduct].[ProductName]=[tbl_ProductResultSubmission].[ProductName]) ORDERED FORWARD)<br /> | | |--Row Count Spool<br /> | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FNChris].[dbo].[tbl_Applicant].[PK_tbl_Applicant]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_Applicant].[TransactionId]=[tbl_Application].[TransactionId]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />like([tbl_Applicant].[FirstName]+' '+[tbl_Applicant].[MiddleName]+' '+[tbl_Applicant].[LastName], '%'+[@ApplicantName_]+'%', NULL)) ORDERED FORWARD)<br /> | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FNChris].[dbo].[tbl_Applicant].[PK_tbl_Applicant]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />like([tbl_Applicant].[ApplicantType], 'Primary', NULL)))<br /> |--Hash Match(Cache, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ProductResultSubmission].[AssignedTerminalId]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_ProductResultSubmission].[AssignedTerminalId]=[tbl_ProductResultSubmission].[AssignedTerminalId]))<br /> |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1041]=substring([tbl_Terminal_3].[ContactFirstName], 1, 1)+'. '+[tbl_Terminal_3].[ContactLastName]))<br /> |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1040]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FNChris].[dbo].[tbl_Terminal] AS [tbl_Terminal_3]))<br /> |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[FNChris].[dbo].[tbl_Terminal].[PK_tbl_Terminal] AS [tbl_Terminal_3]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[tbl_Terminal_3].[TerminalId]=[tbl_ProductResultSubmission].[AssignedTerminalId]) ORDERED FORWARD)</font id="code"></pre id="code"><br />I am still pretty new to this kind of thing, so cant make too much sense of it...if anyone sees something obvious that i could attend to it would be great if you could let me know.<br /><br />'I reject your reality and substitute my own' - Adam Savage
  14. Adriaan New Member

    What does the query itself look like? Looks like you might be able to cut out parts of it in turn, to see what is the worst performing parts.
  15. benwilson New Member

    the procedure itself is pretty ugly:<br /><pre id="code"><font face="courier" size="2" id="code">CREATE PROCEDURE [dbo].[Run_LenderTerminalRunsheet] @TerminalId_ NVARCHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, @ProductName_ NVARCHAR(50) = NULL, @AssignmentStatus_ NVARCHAR(10) = NULL,<br />@ApplicantName_ NVARCHAR(100) = NULL, @ReferenceNo_ NVARCHAR (9) = NULL, @FromDate_ NVARCHAR(10) = NULL, @ToDate_ NVARCHAR(10) = NULL, <br />@ShowWithdrawn_ BIT = NULL, @ShowSettled_ BIT = NULL, @LenderDecision_ NVARCHAR(10) = NULL<br />AS<br />SET CONCAT_NULL_YIELDS_NULL OFF<br />SET NOCOUNT ON<br />SET DATEFORMAT DMY<br /><br /><br />DECLARE @DateFormatToDate DATETIME<br /><br />SET @DateFormatToDate = CONVERT (CHAR, @ToDate_, 103)<br /><br />SET @DateFormatToDate = @DateFormatToDate + 1<br /><br /><br /><br />SELECT dbo.tbl_Application.TransactionId, dbo.tbl_Application.ApplicationNo, CONVERT(char(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, dbo.tbl_ProductResultSubmission.SubmissionDate, 112) <br /> AS SubmissionDate, dbo.tbl_ProductResultSubmission.SubmissionDate AS OrderBySubmissionDate, UPPER(LEFT(dbo.view_PrimaryApplicantNameAndDOB.FirstName, 1)) <br /> + '. ' + dbo.view_PrimaryApplicantNameAndDOB.LastName AS DisplayName, LEFT(dbo.tbl_ProductResult.ResultStatus, 3) AS ResultStatus, <br /> CONVERT(Char(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, dbo.view_ProductResultSubmissionDecisionDate.ApprovedDate, 112) AS ApprovedDate, CONVERT(CHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, <br /> dbo.view_ProductResultSubmissionDecisionDate.DeclinedDate, 112) AS DeclinedDate, CONVERT(char(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, <br /> dbo.view_ProductResultSubmissionDecisionDate.QueryDate, 112) AS QueryDate, dbo.tbl_ApplicationSettlement.LoanPrincipal AS SettlementAmount, <br /> dbo.tbl_ProductResult.LoanPrincipal, Filtered_ProductResultInitialFeeSum.InitialFeeSum, dbo.tbl_ApplicationWithdraw.CancelledBy, <br /> dbo.tbl_ProductResult.ProductName, dbo.tbl_RegisteredProduct.ProductShortName, dbo.tbl_Transaction.InProgress,<br /> (SELECT LEFT(ContactFirstName, 1) + '. ' + ContactLastName AS Expr1<br /> FROM dbo.tbl_Terminal AS tbl_Terminal_3<br /> WHERE (TerminalId = dbo.tbl_ProductResultSubmission.AssignedTerminalId)) AS AssignedName<br />FROM dbo.tbl_RegisteredProduct RIGHT OUTER JOIN<br /> dbo.view_ProductResultSubmissionDecisionDate RIGHT OUTER JOIN<br /> (SELECT ApplicationResultId, ProductName, SUM(FeeAmount) AS InitialFeeSum<br /> FROM dbo.tbl_ProductResultFee<br /> WHERE (FeeType = N'Initial' ) AND NOT( FeeCode = N'NABFEE3' )<br /> GROUP BY ApplicationResultId, ProductName) Filtered_ProductResultInitialFeeSum RIGHT OUTER JOIN<br /> dbo.tbl_Application INNER JOIN<br /> dbo.tbl_ProductResult ON dbo.tbl_Application.LastApplicationResultId = dbo.tbl_ProductResult.ApplicationResultId INNER JOIN<br /> dbo.tbl_ProductResultSubmission ON dbo.tbl_ProductResult.ApplicationResultId = dbo.tbl_ProductResultSubmission.ApplicationResultId AND <br /> dbo.tbl_ProductResult.ProductName = dbo.tbl_ProductResultSubmission.ProductName ON <br /> Filtered_ProductResultInitialFeeSum.ApplicationResultId COLLATE Latin1_General_CI_AS = dbo.tbl_ProductResult.ApplicationResultId AND <br /> Filtered_ProductResultInitialFeeSum.ProductName COLLATE Latin1_General_CI_AS = dbo.tbl_ProductResult.ProductName ON <br /> dbo.view_ProductResultSubmissionDecisionDate.ApplicationResultId = dbo.tbl_ProductResultSubmission.ApplicationResultId AND <br /> dbo.view_ProductResultSubmissionDecisionDate.ProductName = dbo.tbl_ProductResultSubmission.ProductName ON <br /> dbo.tbl_RegisteredProduct.ProductName = dbo.tbl_ProductResult.ProductName RIGHT OUTER JOIN<br /> dbo.tbl_ApplicationResult ON dbo.tbl_Application.LastApplicationResultId = dbo.tbl_ApplicationResult.ApplicationResultId AND <br /> dbo.tbl_Application.TransactionId = dbo.tbl_ApplicationResult.TransactionId LEFT OUTER JOIN<br /> dbo.tbl_ApplicationSettlement ON dbo.tbl_Application.TransactionId = dbo.tbl_ApplicationSettlement.TransactionId LEFT OUTER JOIN<br /> dbo.tbl_Transaction LEFT OUTER JOIN<br /> dbo.tbl_Terminal tbl_Terminal_2 LEFT OUTER JOIN<br /> dbo.tbl_TerminalProfileManagementDependency ON tbl_Terminal_2.Profile = dbo.tbl_TerminalProfileManagementDependency.Profile ON <br /> dbo.tbl_Transaction.DistributorId = tbl_Terminal_2.TerminalId ON <br /> dbo.tbl_Application.TransactionId = dbo.tbl_Transaction.TransactionId LEFT OUTER JOIN<br /> dbo.view_PrimaryApplicantNameAndDOB ON <br /> dbo.tbl_Application.TransactionId = dbo.view_PrimaryApplicantNameAndDOB.TransactionId LEFT OUTER JOIN<br /> dbo.tbl_ApplicationWithdraw ON dbo.tbl_ApplicationResult.TransactionId = dbo.tbl_ApplicationWithdraw.TransactionId<br /><br />WHERE NOT(dbo.tbl_Application.Demo = 1) AND tbl_ProductResultSubmission.SubmissionDate IS NOT NULL AND dbo.tbl_TerminalProfileManagementDependency.TerminalId = @TerminalId_<br />AND ((@ProductName_ IS NULL AND dbo.tbl_ProductResultSubmission.ProductName IN (SELECT dbo.tbl_TerminalProduct.ProductName FROM tbl_TerminalProduct WHERE dbo.tbl_TerminalProduct.TerminalId = @TerminalId_)) OR dbo.tbl_ProductResult.ProductName = @ProductName_)<br />AND (@AssignmentStatus_ IS NULL OR (@AssignmentStatus_ = 'Unassigned' AND tbl_ProductResultSubmission.AssignedTerminalId IS NULL) OR (@AssignmentStatus_ = 'Assigned' AND tbl_ProductResultSubmission.AssignedTerminalId = @TerminalId_))<br />AND (@ApplicantName_ IS NULL OR (dbo.tbl_Application.TransactionId IN (SELECT DISTINCT TransactionId FROM tbl_Applicant WHERE FirstName + N' ' + MiddleName + N' ' + LastName LIKE '%' + @ApplicantName_ + '%')))<br />AND (@ReferenceNo_ IS NULL OR (tbl_Terminal_2.TerminalReferenceNo = @ReferenceNo_))<br />AND (@ShowWithdrawn_ = 1 OR ((@ShowWithdrawn_ IS NULL OR @ShowWithdrawn_ = 0) AND dbo.tbl_ApplicationWithdraw.TransactionId IS NULL))<br />AND (@ShowSettled_ = 1 OR ((@ShowSettled_ IS NULL OR @ShowSettled_ = 0) AND dbo.tbl_ApplicationSettlement.LoanPrincipal IS NULL))<br />AND (@FromDate_ IS NULL OR (tbl_ProductResultSubmission.SubmissionDate &gt;= @FromDate_))<br />AND (@ToDate_ IS NULL OR (tbl_ProductResultSubmission.SubmissionDate &lt;= @DateFormatToDate))<br />AND (@LenderDecision_ IS NULL OR ((@LenderDecision_ = 'Approved' AND ApprovedDate IS NOT NULL) OR (@LenderDecision_ = 'Declined' AND DeclinedDate IS NOT NULL) OR (@LenderDecision_ = 'Pending' AND ApprovedDate IS NULL AND DeclinedDate IS NULL AND dbo.tbl_ApplicationSettlement.LoanPrincipal IS NULL)))<br /><br />ORDER BY dbo.tbl_ProductResultSubmission.SubmissionDate DESC</font id="code"></pre id="code"><br /><br />'I reject your reality and substitute my own' - Adam Savage
  16. Adriaan New Member

    Where to start on this one ...

    Start chopping off parts and see how long it takes. Once you find a part that - once removed - improves the response time, you know where to start improving.

    Couple of issues that jump out:

    (1) Why use views? Unless they are appropriately indexed view, those would be the first parts I'd look at.
    The views can't use the criteria that you supply for the main query, so they may take too much time to evaluate for the number of results they supply back to the main query.

    (2) I haven't looked very closely, but I would put the ON clauses immediately behind the JOIN clause to which they apply.
    It may well be that you can't do that; you'll just have to check whether you can.

    (3) Don't use a correlated subquery if you might as well join the source table.

    What I've done below is: return a minimum of data, remove the criteria, merge the correlated subquery into the main query. If the response time on this is good enough (you could use SET ROWCOUNT 100 to see if response times are really bad) - if not, then my next step would be to throw out the views. If that doesn't help, move around the ON clauses and use the Index Tuning Wizard on the basic query, next add the criteria again and check the ITW.


    SELECT dbo.tbl_Application.TransactionId,
    LEFT(tbl_Terminal_3.ContactFirstName, 1) + '. ' + tbl_Terminal_3.ContactLastName) AS AssignedName
    FROM dbo.tbl_RegisteredProduct
    LEFT JOIN dbo.tbl_Terminal AS tbl_Terminal_3
    ON dbo.tbl_ProductResultSubmission.AssignedTerminalId = tbl_Terminal_3.TerminalId
    RIGHT JOIN dbo.view_ProductResultSubmissionDecisionDate
    RIGHT JOIN
    (SELECT ApplicationResultId, ProductName, SUM(FeeAmount) AS InitialFeeSum
    FROM dbo.tbl_ProductResultFee WHERE (FeeType = N'Initial' )
    AND NOT( FeeCode = N'NABFEE3' )
    GROUP BY ApplicationResultId, ProductName) Filtered_ProductResultInitialFeeSum
    RIGHT JOIN dbo.tbl_Application
    INNER JOIN dbo.tbl_ProductResult
    ON dbo.tbl_Application.LastApplicationResultId
    = dbo.tbl_ProductResult.ApplicationResultId
    INNER JOIN dbo.tbl_ProductResultSubmission
    ON dbo.tbl_ProductResult.ApplicationResultId = dbo.tbl_ProductResultSubmission.ApplicationResultId
    AND dbo.tbl_ProductResult.ProductName
    = dbo.tbl_ProductResultSubmission.ProductName
    ON Filtered_ProductResultInitialFeeSum.ApplicationResultId COLLATE Latin1_General_CI_AS
    = dbo.tbl_ProductResult.ApplicationResultId
    AND Filtered_ProductResultInitialFeeSum.ProductName COLLATE Latin1_General_CI_AS
    = dbo.tbl_ProductResult.ProductName
    ON dbo.view_ProductResultSubmissionDecisionDate.ApplicationResultId
    = dbo.tbl_ProductResultSubmission.ApplicationResultId
    AND dbo.view_ProductResultSubmissionDecisionDate.ProductName
    = dbo.tbl_ProductResultSubmission.ProductName
    ON dbo.tbl_RegisteredProduct.ProductName
    = dbo.tbl_ProductResult.ProductName
    RIGHT JOIN dbo.tbl_ApplicationResult
    ON dbo.tbl_Application.LastApplicationResultId
    = dbo.tbl_ApplicationResult.ApplicationResultId
    AND dbo.tbl_Application.TransactionId
    = dbo.tbl_ApplicationResult.TransactionId
    LEFT JOIN dbo.tbl_ApplicationSettlement
    ON dbo.tbl_Application.TransactionId
    = dbo.tbl_ApplicationSettlement.TransactionId
    LEFT JOIN dbo.tbl_Transaction
    LEFT JOIN dbo.tbl_Terminal tbl_Terminal_2
    LEFT JOIN dbo.tbl_TerminalProfileManagementDependency
    ON tbl_Terminal_2.Profile = dbo.tbl_TerminalProfileManagementDependency.Profile
    ON dbo.tbl_Transaction.DistributorId = tbl_Terminal_2.TerminalId
    ON dbo.tbl_Application.TransactionId = dbo.tbl_Transaction.TransactionId
    LEFT JOIN dbo.view_PrimaryApplicantNameAndDOB
    ON dbo.tbl_Application.TransactionId
    = dbo.view_PrimaryApplicantNameAndDOB.TransactionId
    LEFT JOIN dbo.tbl_ApplicationWithdraw
    ON dbo.tbl_ApplicationResult.TransactionId
    = dbo.tbl_ApplicationWithdraw.TransactionId
  17. benwilson New Member

    Thanks Adriaan,
    The views arent indexed, so as you suggest, maybe they would be a good starting point (however making them part of the query is likely to make it even more ugly :-( so maybe indexing the views is the way to go?!?!)

    Will have more of a play with your suggestions today if time permits and will let you know how i get on.

    Ben

    'I reject your reality and substitute my own' - Adam Savage
  18. benwilson New Member

    Hi Adriaan,
    I have only just had time to get back to this - and it looks like a couple of hours is all i may get in the near future :-(

    in you suggestion 3, you say to get rid of the correlated subquery...am i right in thinking you mean to get rid of this:
    AND ((@ProductName_ IS NULL AND dbo.tbl_ProductResultSubmission.ProductName IN (SELECT dbo.tbl_TerminalProduct.ProductName FROM tbl_TerminalProduct WHERE dbo.tbl_TerminalProduct.TerminalId = @TerminalId_)) OR dbo.tbl_ProductResult.ProductName = @ProductName_) and to join tbl_TerminalProduct???

    I have tried just doing that, and it seems to have slowed the query down...am i missing something? hmmmm...maybe it is a case of needing different indexes if i join this table too! I will keep playing!

    Ben

    'I reject your reality and substitute my own' - Adam Savage
  19. Adriaan New Member

    Hi Ben,

    It's been a while - but I guess you're correct about my suggestion #3.
  20. PAMUR New Member

    Hi,


    Adriaan's suggestion is really good. Am facing the same kind of situation.

    Try every thing as suggested by Adriaan and then

    * Study base tables used for volume, Selects, Updates, inserts -- No. and Frequency
    * Create Indexes based on the above stats
    * Place Voluminous and mostly selected - Hits most, participating in join on different Physical files.


    This is a painfull task but may be needed.

  21. benwilson New Member

    quote:Originally posted by Adriaan

    Hi Ben,

    It's been a while - but I guess you're correct about my suggestion #3.
    Yep, it has been a while, but between all the other work that has been dumped on me, and getting hit by a car while riding to work last week, i havent had too much time! unfortunately it looks like it is something i am going to have to work on whenever i can grab a spare hour or 2...

    'I reject your reality and substitute my own' - Adam Savage
  22. satya Moderator

  23. benwilson New Member

    quote:Originally posted by satya

    Hope you're keeping fit and to recover soon.
    Thanks...i was pretty lucky- apart from some cracked ribs and some bruises, i seem to be pretty much ok now!

    'I reject your reality and substitute my own' - Adam Savage

Share This Page