Post reply

Performance related issue after using left join to bring new fields to the query

  • April 6, 2022 at 1:33 pm

    #4011624

     

    i have stored procedure which was running fantastic. When ran with parameters it gives results in 25 seconds.

    I needed to bring new fields to the query used left join

    SELECT AccountNumber , payment_date ,Account_date FROM CORE.AccountData(NOlock) WHERE Prop_TYPE NOT LIKE '%Rond%' AND

    (CASE WHEN CAST([CreatedDate] AS Date) < CAST([ProposalDate] AS Date) THEN CAST([CreatedDate] AS Date)

    ELSE CAST([ProposalDate] AS Date) END) >= dbo.fn_StartDate(getdate())

    this is the new query added it takes 6 to 8 minutes to return results. The query ran separately has 65k count

    please help

     

     

  • April 6, 2022 at 1:42 pm

    #4011629

    There's no left join in that query.

  • April 6, 2022 at 10:40 pm

    #4011868

    I agree with ZZartin, there is no left join in that query... I don't see any joins...

    Now, that being said, execution plan is going to be your friend for performance tuning.  I would review the actual execution plan (not the estimated as bad estimates can result in bad plans) and go from there.

    Those CAST's and the CASE statement are going to be a huge slowdown on your query as they need to run on each and every row. On top of that, functions in the WHERE clause are going to slow stuff down too (from my understanding anyways).  I would recommend storing the value from "dbo.fn_StartDate(getdate())" into a variable and then you are only running that function once!  I would also look at the possibility of removing the CASE and CAST's from your query and that should help performance and shouldn't be that hard to do - it looks to me to be pretty simple by using syntax like "(condition1 AND condition2) OR (condition3 AND condition4)" where conditions 2 and 4 are the CASE statements and conditions 1 and 3 are your results from the case statement... if that made any sense.

    That being said, a 25 second query being "fantastic" sounds slow to me.  Pretty much any query that runs longer than 5 seconds is "too slow" for me.  Slow is fine while I am developing the query/report/whatever, but before it goes to prod, I need it under 5 seconds from start to finish or end users will be unhappy.  And even 5 seconds feels like forever.  The ONLY exception to that is for downtime ETL where it can run for hours if needed as long as it completes prior to company uptime.  We have downtime windows nightly and on most weekends.  I personally would not expect a "SELECT <columns> FROM <table> WHERE <conditions>" query that doesn't have a JOIN and doesn't appear to be that complex to take more than 5 seconds UNLESS the bottleneck isn't with the SQL processing but is with something else such as the network OR you are pulling down a LOT of data across a slow network link.  When I say a lot of data, I mean a lot of bytes.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • April 6, 2022 at 11:21 pm

    #4011891

    You can clean up the existing code some, but the condition:

    Prop_TYPE NOT LIKE '%Rond%'

    is still going to require a table scan or a covering index scan (if a covering index is available).

    I'm assuming that fn_StartDate returns a date, or, if a datetime, that the time is always 00:00:00.

    SELECT AccountNumber, payment_date, Account_date 

    FROM CORE.AccountData(NOlock) 

    WHERE Prop_TYPE NOT LIKE '%Rond%' AND

    ([CreatedDate] >= dbo.fn_StartDate(getdate()) AND ProposalDate >= dbo.fn_StartDate(getdate()))

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • April 6, 2022 at 11:36 pm

    #4011894

    I agree with what the others said.  you've basically guaranteed that there's no chance of index seeks or high performance seek/range scans by embedding columns in functions, doing a leading wildcard search, etc.

    I'll also add that you've used a scalar function in your WHERE clause which virtually guarantees the query will always and only be single threaded even if everything else were perfect.

    If you want help on this, you need to post the following please...

    1. The CREATE TABLE statement for the CORE.AccountData table along with all constraints (including FKs) and indexes.
    2. The code for your dbo.fn_StartDate function.
    3. The actual requirements that the code is trying to solve.
    4. If the code you posted is what you're using as a derived table in another query for the target of the Left Join, you also need to post that outer query along with 1, 2, and 3 above for that query.

    Otherwise, we're just guessing and that's a waste of time for you and us.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

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