Post reply

Help me: Many Tables to join.

  • January 23, 2012 at 10:10 am

    #1437220

    The Plan I posted is from my QA database.

  • January 23, 2012 at 11:20 am

    #1437267

    There are a few thing I noticed:

    a) In your WHERE clause you use CAST to convert receiptdate into a char value and compare it with @FromDate and @ToDate.

    It's already in the code. Why don't you use it?

    I'd expect to see an index seek if there's no CAST involved.

    b) There's an RID Lookup against tfsReceiptDetails. The missing columns (Amount, RemainingAmount, tSubContractId) might need to go into the INCLUDE section of the IX_tfsReceiptDetails_tReceiptID index)

    c) You're joining a view. What's the query behind it?

    d) Except for tFSPromissoryNotes, the other two tables in the function are already used in the outer query.

    To summarize it: there's plenty room for improvement. I'd probably start with separating the data that need to be aggregated into a separate query (indexed temp table) and join the rest of the tables afterwards. The function would not be part of this step at all.

    The next step would be rewriting the function either into a inline-Table-valued function or even just using CROSS APPLY and a select to the missing table.

    After that, I'd check if there are any missing indexes.

    And I wouldn't cast any data in the WHERE clause...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • January 23, 2012 at 11:50 am

    #1437287

    Rafi,

    The first thing I noticed about the execution plan was a clustered index scan (same as a table scan) on the tfsReceipt table. If you have an index on receiptdate it can't be used because of:

    CAST(DATEPART(YYYY,receiptdate) AS CHAR(4)) + '/' + RIGHT(CAST(100+DATEPART(MM,receiptdate) AS CHAR(3)),2) + '/' + RIGHT(CAST(100+DATEPART(DD,receiptdate) AS CHAR(3)),2) between... <same CAST for @FromDate and @ToDate>

    When you do this do a column in the table you have what is called a non-SARGable condition. The optimizer doesn't know what to do with the date column and can't use an index search.

    Is there a specific reason you can't just do something like:

    WHERE r.receiptdate BETWEEN @FromDate And @ToDate

    You would have a much better chance of using any index on receiptdate.

    Todd Fifield

  • January 23, 2012 at 12:29 pm

    #1437321

    DBA Rafi (1/23/2012)

    The Plan I posted is from my QA database.

    Hi Rafi

    This is the estimated plan, can you post the actual plan please?

    Cheers

    ChrisM

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • January 30, 2012 at 12:59 am

    #1439855

    Thanks All for your Great Help. I glad about it.

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply