none
Performance spike! RRS feed

  • Question

  •  

    Is it possible for a stored Procedure on SQL to become completely disfunctional performance wise because of adding an additioanly predicate in the where clause assuming that all is written correctly?

     

    I know there is a billzion different things the author of the Sproc could have done but for the momment please assume the following

     

    1. Nothing has been added anywhere else other then the where clause
    2. Various combinations have been tested not just the new one
    3. no functions in the where clause are being used
    4. indexed columns vs unindexed columns do not seem to matter
    5. the Spoc was working great before and still does as long as there is 3 predicates of any kind.

     

    By 3 predicates I mean:

     

    WHERE (Something = @pSomething)

    AND (Something1 = @pSomething2)

    AND (Something3 = @pSomething3)

    IMPORTANT ADDED: Its important for me to note that server crashes and database performance issues are extreemly common here which is one reason I did many tests becuase its hard for me to tell what is a spike in general performance issues and what is mine specifically. Given general statistical chance, its likely the cause of my problem would be realted to what would normally not be high chance.

    Monday, January 19, 2009 7:39 PM

Answers

All replies

  • Yes, it's very possible.  By adding another column, and not ensuring that said column is a key column in the index that your query is using, you can force the optimizer to resort to a table scan in order to resolve the query.  On a very large table, a table scan can take quite a significant amount of resources to complete.

    To see what the problem might be, examine the execution plans before and after adding the additional predicate.

    Does this help?
    Aaron Alton | thehobt.blogspot.com
    Monday, January 19, 2009 7:59 PM
    Moderator
  •  
    Aaron Alton said:

    Yes, it's very possible.  By adding another column, and not ensuring that said column is a key column in the index that your query is using, you can force the optimizer to resort to a table scan in order to resolve the query.  On a very large table, a table scan can take quite a significant amount of resources to complete.

    To see what the problem might be, examine the execution plans before and after adding the additional predicate.

    Does this help?


    Aaron Alton | thehobt.blogspot.com



    I think I found it but not sure why. Removed the (around the first condition)


    old

    WHERE (Something1 = @pSomething1)

    AND (Something2 = @pSomething2)

    AND (Something3 = @pSomething3)

    AND (Something4 = @pSomething4)

     

    new

    WHERE Something1 = @pSomething1

    AND (Something2 = @pSomething2)

    AND (Something3 = @pSomething3)

    AND (Something4 = @pSomething4)

    Monday, January 19, 2009 8:22 PM
  • What if your fourth condition is:

    AND 1=1

    Does it still spike performance?
    Aaron Alton | thehobt.blogspot.com
    • Marked as answer by SEAN_MCAD Monday, January 19, 2009 9:02 PM
    Monday, January 19, 2009 8:37 PM
    Moderator
  • Didn't see your response before posting.  What you found shouldn't affect the query at all.  Strange.  You'd have to post the exact query in order for us to stand a chance at sorting it out.
    Aaron Alton | thehobt.blogspot.com
    Monday, January 19, 2009 8:38 PM
    Moderator
  • Turns out doing so made the data incorrect anyway.

    I did your suggested test and I see why you suggested it.
    adding

    AND 1=1
    brings the performance back to norm so its mostly likely the developers fault somewhere in the query.

    That elevates the question to another form which I am not perpared to go down as of yet. I might post later.

    Thanks for now!

    Monday, January 19, 2009 9:02 PM
  • Weird.
    Monday, January 19, 2009 9:11 PM
    Moderator
  •  I found something very intresting.
    This table has various unclusted indexed with various combination of tables.

    So when I did a query with the last two AND's it was two fields that were in two different indexes while using either one or the other by itself it was able to run under one of the indexes or the other.

    Now that is starting to make sense to me.
    Monday, January 19, 2009 9:17 PM