Post reply

slow query

  • November 7, 2018 at 2:11 pm

    #368360

    the query has a Where clause that 2 conditions combined via AND. If only the first condition is run it stakes only 2 seconds, only the second condition it takes 2 seconds, but if the whole query is run is takes 7-9 mins.

    any help on this

    Regards
    Atulyan Padmanabhan

  • November 7, 2018 at 2:17 pm

    #2012755

    You'll want to look at the execution plan to determine why your query is performing poorly, but my guess is that you'll want to create a nonclustered index on the columns referenced in your WHERE clause. Also, make sure you're not using any functions on those columns, because 99% of the time the indexes would be ignored.

    Mike Scalise, PMP
    https://www.michaelscalise.com

  • November 9, 2018 at 6:42 am

    #2012950

    atulyan.aries - Wednesday, November 7, 2018 2:11 PM

    the query has a Where clause that 2 conditions combined via AND. If only the first condition is run it stakes only 2 seconds, only the second condition it takes 2 seconds, but if the whole query is run is takes 7-9 mins.

    any help on this

    Regards
    Atulyan Padmanabhan

    Run the query with execution plan and attach here. Is there any parameter used?

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • November 9, 2018 at 9:08 am

    #2012982

    atulyan.aries - Wednesday, November 7, 2018 2:11 PM

    the query has a Where clause that 2 conditions combined via AND. If only the first condition is run it stakes only 2 seconds, only the second condition it takes 2 seconds, but if the whole query is run is takes 7-9 mins.

    any help on this

    Regards
    Atulyan Padmanabhan

    And how are we supposed to help you when we can't see what you see?  The query and the actual execution plan would go a long way to helping us help you.

  • November 19, 2018 at 12:33 am

    #2013921

    I agree with other gurus: the only way to investigate this behaviour is analyze execution plan.
    May be second join cause a full table scan because of missing indexes, or generate a  loop join, or can be some outdated statistics...

  • November 22, 2018 at 1:14 pm

    #2014347

    I agree to all the above posts.
    Apart from those, also would  like to suggest change the priority of the where clauses (i.e. interchange the conditions put the second one first )  and see how much difference in case it makes.
    Check the count of records when the two conditions are fired together and when fired individually.

  • November 22, 2018 at 2:19 pm

    #2014358

    atulyan.aries - Wednesday, November 7, 2018 2:11 PM

    the query has a Where clause that 2 conditions combined via AND. If only the first condition is run it stakes only 2 seconds, only the second condition it takes 2 seconds, but if the whole query is run is takes 7-9 mins.

    any help on this

    Regards
    Atulyan Padmanabhan

    Sounds to me like your criteria is responsible for the creation of an accidental Cartesian product.  You really do need to look at the Actual Execution Plan and figure it out.

    Also, if the query is taking two seconds with only one of the criteria, it sounds like you may have a basic performance issue in the code to begin with depending, of course, on what the purpose of the code is.

    --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 7 posts - 1 through 6 (of 6 total)

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