SQL Server Performance Forum – Threads Archive

Performance problem in 2005

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

---

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.

---

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)

---

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

---

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.

---

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

---

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.

---

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

---

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

---

]]>