Help me: Many Tables to join.

  • SqlServerPass

    SSCommitted

    Points: 1709

    The Plan I posted is from my QA database.

  • LutzM

    SSC Guru

    Points: 107049

    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]

  • tfifield

    SSCrazy Eights

    Points: 9655

    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

  • ChrisM@home

    SSC-Insane

    Points: 24260

    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]

  • SqlServerPass

    SSCommitted

    Points: 1709

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

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

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