Performance problem in 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Performance problem in 2005

Author

Topic

Andy_Mil
Starting Member

18 Posts

Posted - 2006-08-19 : 13:17:53

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 this dummy 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

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-08-21 : 05:29:40

Your queries shouldn't be compiling every time. Is this part of a Stored Procedure? Stored procedures enable execution plans to be re-used...which cuts out the compile times.
Also qualify your tables with the owner....that will reduce the compile time (somewhat).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 07:24:27

Also, 6 subqueries are slow. Compare to this code

select		count(*)
from		(
			select		trades.quote_id
			from		trades
			inner join	prices on prices.quote_id >= trades.quote_id - 5 AND prices.quote_id <= trades.quote_id
			group by	trades.quote_id
			having		count(*) = 6
		) x

Is there a speed difference?

Peter Larsson
Helsingborg, Sweden

Andy_Mil
Starting Member

18 Posts

Posted - 2006-08-21 : 12:39:45

thanks for you reply, Andrew. I understand that I can save time by avoiding the compilation in some cases, but this is out of the question in this scenario. Here I'm specifically interested why compilation takes that long, especially taking into consideration the fact that it was not noticeable in previous SQL Server version. Qualifying tables with the owner didn't seem to help.

Thanks,
- Andy

Andy_Mil
Starting Member

18 Posts

Posted - 2006-08-21 : 12:45:36

thanks for your reply, Peso. I understand that this is not highly optimized query, but my problem is different. Query execution time (retrieving the data) is 300ms, and I'm perfectly ok with that. Why compilation takes 12 seconds, this is what I don't understand. It took only 100ms to compile such query in SQL Server 2000.
As to the query you've suggested... believe it or not but I couldn't wait till the query was completed. It went into 100% CPU utilization for more than 3 minutes and I just stopped it. I don't understand what's going on...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 13:26:16

If you remove some of the AND's, does the compilation time descrease accordingly?

Peter Larsson
Helsingborg, Sweden

Andy_Mil
Starting Member

18 Posts

Posted - 2006-08-21 : 14:16:03

quote:
Originally posted by Peso

If you remove some of the AND's, does the compilation time descrease accordingly?

yes it does. If I leave only one, it is just 60ms. But if there are two it takes about 1 second, with three - 2 seconds, etc.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 14:18:39

So there is an exponential raise in compilation time for each AND--IN?

Peter Larsson
Helsingborg, Sweden

Andy_Mil
Starting Member

18 Posts

Posted - 2006-08-21 : 14:40:42

well, not exponential. there is a huge jump after adding a second AND and then it seems to be growing in linear fashion

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 15:06:48

It's strange anyway since it is the compilation time that grows.
I would expect the execution time to grow significantly, but not the compilation time. Not in that extent.

Peter Larsson
Helsingborg, Sweden

Andy_Mil
Starting Member

18 Posts

Posted - 2006-08-21 : 15:09:18

That's exactly what I'm curious about...

Andy_Mil
Starting Member

18 Posts

Posted - 2006-08-21 : 15:20:44

BTW Peter, here is another interesting thing. As I said the query you suggested takes forever to execute. However, if I change the index on quote_id column from clustered to non-clustered it works in fraction of a second. But it didn't help my query, anyway...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-21 : 15:24:09

Maybe SQL 2005 is smart enough to see many more possible execution plans.

I have to say that the WHERE clause does not make any sense to me. Maybe there is a better way to do this query if you explain what it is supposed to do.

CODO ERGO SUM

Andy_Mil
Starting Member

18 Posts

Posted - 2006-08-21 : 15:35:02

quote:
Originally posted by Michael Valentine Jones

Maybe SQL 2005 is smart enough to see many more possible execution plans.

I have to say that the WHERE clause does not make any sense to me. Maybe there is a better way to do this query if you explain what it is supposed to do.

I absolutely agree that this query is dummy and doesn't make a lot of sense. I just wrote it here so it is easier to see my problem, my real life queries are more complex. But I repeat the problem is not with this particular query, but with the fact that queries that were compiled in less than a second in SQL2000 now take dozen seconds in SQL2005

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-21 : 15:54:42

Who can say why? If you really want to delve into that, you should contact Microsoft.

Since you don't seem to want to post your actual query, I doubt there is much we can do to help you.

CODO ERGO SUM

Andy_Mil
Starting Member

18 Posts

Posted - 2006-08-21 : 15:58:07

See what I'm trying to find out is there a way to make my SQL2005 work at least not slower than SQL2000. Optimizing my queries might be a good idea but this is not what I need at this point.

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-22 : 13:49:46

Andy,

quote:

See what I'm trying to find out is there a way to make my SQL2005 work at least not slower than SQL2000. Optimizing my queries might be a good idea but this is not what I need at this point.

I think you're wrong. Even if the problem is just with compilation time and not execution time, rewriting the query could reduce the time it takes for compilation. So, as Michael said, you should post your actual query if you're seriously looking for help.

Ken

Andy_Mil
Starting Member

18 Posts

Posted - 2006-08-22 : 14:38:39

Ken,

I would agree with you if it didn't work fine for me in SQL 2000. However it did. I'm pretty positive that there is no way to significantly optimize my queries. They're not so complex but just include many joins with different tables and synonyms, there's no way for me to avoid any of those. And I noticed that adding new joins significantly increases compilation time (in SQL 2005). Most of my queries look as below:

select trades.quote_id from trades
where
trades.quote_id = table1.quote_id and
trades.quote_id = table2.quote_id and
..
trades.quote_id = tableN.quote_id and
table1.fieldA > A1 and
table2.fieldB < B1 and
...
tableN.fieldZ = Z1

So, there's not much to optimize. I expect SQL Server just go and get me the data using those clustered indexes. But for some reason it is trying very hard to get the primitive execution plan...

Thank you,
- Andy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-22 : 14:42:00

Well it sounds like you need to contact MS in order to determine why SQL Server 2005 is much slower for you. We aren't engineers of SQL Server, so we don't know the insides and outs of it. MS should be able to help you with this. The type of thing that we can help with is optimizing your queries regardless of the version.

Tara Kizer

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-22 : 18:26:03

quote:
Originally posted by Andy_Mil
...I'm pretty positive that there is no way to significantly optimize my queries...

In that case, there's probably nothing we can do to help.

Vanity, definitely my favorite sin.

Andy_Mil
Starting Member

18 Posts

Posted - 2006-08-22 : 18:40:31

Thanks for your help, guys. I was thinking that there could be some tuning options that I was not aware of that could fix the issue. It looks like there isn't anything of that kind.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources