SQL Server Performance

Different execution times on (nearly) same query

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by mindas, May 22, 2008.

  1. mindas New Member

    I have an SQL SELECT query addressing three tables which performs in a couple of seconds or so. After a bulk insert, strange things began to happen - the same query performs very differently with just a single varying parameter (....AND table1.foo='foo' VS ...AND table1.foo='bar'). While the foo='foo' query still performs in a couple of seconds, the foo='bar' one only returns results in about 90 seconds. Execution plans for both of the queries are obviously different.
    To tackle this, we tried loads of things: moving data to different host/database, fresh reindexing, adding single and combined indexes, adding/refreshing stats, following Engine Tuning Advisor, etc. The only way of how we could make the foo='bar' query work quickly was to remove the primary key constraint on one of the tables (YES - remove the primary key). Literally, switching PK on/off makes a dramatic difference. This, however, does not sound sane nor feasible in our situation - we can't remove the key constraint due to numerous reasons. We can't also change the queries as they're auto-generated by Hibernate. On the other hand, why should we - once an identical query runs fine, it's a problem of SQL server, and not of app code.
    Had anyone came up with a similar situation and have any tips/suggestions? I appreciate any help.
  2. FrankKalis Moderator

    Welcome to the forum!
    It would be helpful if you could post your code.
  3. mindas New Member

    Hi Frank, and thanks very much for your reply! The SQL statement is as follows:
    SELECT DISTINCT category2_.id AS x0_0_ FROM item_info iteminfo0_ INNER JOIN itemInfos_categories categories1_ ON iteminfo0_.id=categories1_.item_info_id INNER JOIN categories category2_ ON categories1_.category_id=category2_.id WHERE (iteminfo0_.active_version!=-1) AND (iteminfo0_.is_deleted=0) AND (iteminfo0_.coll_schema_id IN('1')) AND (iteminfo0_.id IN( SELECT iteminfo3_.id FROM item_info iteminfo3_, itemInfos_categories categories4_, categories category5_ WHERE iteminfo3_.id=categories4_.item_info_id AND categories4_.category_id=category5_.id AND category5_.id='292' ) )
    The query performance is very different (see my earlier post) if the '292' is changed to another number (which is perfectly valid and returns a list of results).
    Just to repeat myself, we can't really rewrite the query nor we want to - as it is performing well in most of the cases. Thanks again for your help.
  4. mindas New Member

    Also, forgot to mention that the primary key that makes slow query quick is the key on categories table, id field.
  5. FrankKalis Moderator

    Is this an instantiation of the query with actual values? I guess so...
    I guess you are experiencing an SQL Server feature called parameter sniffing. See if this applies to your code: http://groups.google.ch/group/microsoft.public.sqlserver.programming/msg/1e4a2438bed08aca
    In such cases I generall prefer to mask the input parameters and assign their values to local variables in the procedure and then continue to work with these local variables. Reasoning behind this is that the optimizer won't be guessing anymore on any a correlation between the input parameters and their potential use in a WHERE clause. By doing so you can achieve much more consistent performance of the query. That is, it isn't blindingly fast for some combinations of parameters and dead slow for other combinations. From my experience this is more in line with the business user expectation.
  6. mindas New Member

    Thank you very much, Frank! It is indeed an interesting experience for me, developer, living in a different continent of software world.
    I googled on 'parameter sniffing' and ran some tests to prove whether this case is exactly what I'm experiencing. I've created two really simple stored procedures "test", and "test2":
    CREATE PROCEDURE test @catid varchar(20)ASBEGINDECLARE @mycatid nvarchar(20)SET @mycatid = @catidSELECT ... ... AND cateegory5_.id=@mycatid) )CREATE PROCEDURE test2 @catid varchar(20)ASBEGINSELECT ... ... AND cateegory5_.id=@catid) )ENDEND
    SP "test" supposedly employs the technique which allows beating "parameter sniffing" while "test2" is the same query wrapped into a SP. Correct me if I'm wrong.
    The results, are, however, quite surprising. "test" is now slow for both queries (where it used to be quick, and where it used to be slow) while for "test2" is snappy for both cases. Does it prove the opposite - that parameter sniffing really helps and wasn't executed on slow "naked" query but executed when wrapped in SP? Feeling quite confused...
  7. FrankKalis Moderator

    From your description I was guessing about parameter sniffing. It might or might not apply to your issue.
    What do the execution plans say?
  8. FrankKalis Moderator

    Btw, the input parameter in your procedure is varchar(20) while your local variable is of type nvarchar(20). You should get into the habit of using the same data type in both cases and it should also match the type of the column you are refering to. If nothing else it helps avoiding implicit conversion which can - under very specific circumstances - lead to a performance degradation. I have not a link at hand right now, but I think there are several KB article related to this issue.
  9. MartinSmithh New Member

    When the 292 is changed to the problem value how many rows are retuned from this part of the query. How does this compare with when 292 is used?

    SELECT iteminfo3_.id FROM
    item_info iteminfo3_, itemInfos_categories categories4_, categories category5_
    WHERE iteminfo3_.id=categories4_.item_info_id
    AND categories4_.category_id=category5_.id
    AND category5_.id='292'
  10. mindas New Member

    Hi, Martin
    The number of rows does differ, yet not dramatically (497 rows VS 791 rows).
    Anyway, I gave up on tuning the DB and had to rewrite the query - moved the whole thing from Hibernate to native JDBC, also reduced the number of joins which apparently helped. The whole story left me a bit of bad smell of the SQL Server, but I believe every product has its dark corners.
    Thanks a lot for your reply.
  11. gbd77rc New Member

    Remember that IN cause will put in an OR statement inbetween all 791 results, so if the OR fails after 497 rows then the query will take longer. This is one of the reasons why sub queries in WHERE clause can be a performance killer! This is even worse on Sybase (or it used to be, been a long time since I worked on that platform) as it check every OR operator before returning false or true.
    Its good you fixed the performance problem, sorry you feel that it was SQL Server issue, but would you let a VB Script developer work on Java software without some training [:D] SQL server has been one of the better database platforms I have worked on. Yes it has some short comings, but generally it works without much effort.
  12. Adriaan New Member

    To elaborate on Frank's point ...
    Note that if the id column is numeric, you should not use a string type variable for the @catid parameter.
    If the id column has an alphabetic data type but only numeric values, then performance is likely to improve if you change the column to a numeric data type (and use the same type for @catid).

Share This Page