SQL Server Performance

Performance problem in 2005

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Andy_Mil, Aug 19, 2006.

  1. Andy_Mil New Member

    Please, help. After upgrading from 2000 to 2005 I noticed substantial performance degradation. I was able to figure out that the bottleneck is not the retrieving of actual data but compiling the query. Please see the query below:

    select count(trades.quote_id)
    from trades
    where
    trades.quote_id in (select quote_id from prices)
    and trades.quote_id - 1 in (select quote_id from prices)
    and trades.quote_id - 2 in (select quote_id from prices)
    and trades.quote_id - 3 in (select quote_id from prices)
    and trades.quote_id - 4 in (select quote_id from prices)
    and trades.quote_id - 5 in (select quote_id from prices)

    both trades and prices have clustered index on quote_id column, trades has ~10K records and prices has ~4M records. Here is the statistics for this query:

    SQL Server parse and compile time:
    CPU time = 12766 ms, elapsed time = 12768 ms.

    SQL Server Execution Times:
    CPU time = 328 ms, elapsed time = 314 ms.

    I just verified that in 2000 query compilation takes ~100ms. Am I missing some tuning options in 2005 or they "improved" something in the engine?

    Thank you,
    - Andy
  2. satya Moderator

    Before going into details, confirm whether the corresponding indexes and stored procedures have been reindexed & recomplied.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  3. Andy_Mil New Member

    There are no stored procedures involved, but as to indexes, yes they are present. Actually, the problem showed up when I created brand new tables and ran the same query on them (to set aside any conversion issus)
  4. SQL_Guess New Member

    What does the access plan look like?

    Does this provide the same results?

    select count(trades.quote_id)
    from
    trades
    inner join
    (select quote_id from prices) PriceQuotes
    on trades.quote_id = PriceQuotes.quote_id
    or trades.quote_id - 1 = PriceQuotes.quote_id
    or trades.quote_id - 2 = PriceQuotes.quote_id
    or trades.quote_id - 3 = PriceQuotes.quote_id
    or trades.quote_id - 4 = PriceQuotes.quote_id
    or trades.quote_id - 5 = PriceQuotes.quote_id


    there feels like there should be a better way to do this....

    maybe

    select count(trades.quote_id)
    from
    trades
    inner join
    (select quote_id from prices) PriceQuotes
    on PriceQuotes.quote_id between (trades.quote_id) and (trades.quote_id - 5)


    ...


    Panic, Chaos, Disorder ... my work here is done --unknown
  5. Andy_Mil New Member

    Thanks for your reply SQL_Guess, but see I'm not trying to optimize this dummy query, I understand there's a lot of ways to do that.
    My concern is that the query that took just 100ms to compile in SQL Server 2000 now takes 12 seconds in SQL Server 2005, while execution of the query (retrieval of the data) takes the same time in both versions.
  6. SQL_Guess New Member

    Ahh.. ok. Back tot he first question then - what is the access path on SQL2005, and what is it on SQL2000?

    Panic, Chaos, Disorder ... my work here is done --unknown
  7. Andy_Mil New Member

    in 2005 the execution plan shows that 100% of the query execution time takes clustered index seek on prices table. And it is done as many times as many IN clauses are in the query. Unfortunately I don't have access to SQL2000 at this moment so I can't tell you what was the execution plan there.
  8. joechang New Member

    in SQL 2005 there is the option to force parameterization when not using a stored proc

    of course, you should be using a stored proc

    i think what this says is SQL 2005 is trying harder to optimize when is should not, while s2k gave up quickly, which is sometimes the better approach, so bug MS about having more knobs to turn
  9. Andy_Mil New Member

    quote:Originally posted by joechang

    i think what this says is SQL 2005 is trying harder to optimize when is should not, while s2k gave up quickly, which is sometimes the better approach, so bug MS about having more knobs to turn
    that's what I'm thinking too. BTW I have another way to write this query and thus it takes forever to execute (I gave up waiting at 4th minute):
    selecttrades.quote_id
    fromtrades
    inner joinprices on prices.quote_id >= trades.quote_id - 5 AND prices.quote_id <= trades.quote_id
    group bytrades.quote_id
    havingcount(*) = 6

Share This Page