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.
| Author |
Topic |
|
rickster
Starting Member
5 Posts |
 Posted - 2008-01-30 : 10:32:55
|
| Hello,I've encountered some (to me at least) rather odd issues of poor performance in some of our queries. I'll say up front that I'm not great at really digging in and using estimated execution plans and the like just yet...I'm a former programmer moving into a dba role :)The problem did not exist when we were using SQL Server 2000. This has only started since we upgraded recently.In at least two cases, the problem manifests itself in conditions like the following psuedo-code: ...select, from...WHERE (condition1)AND (condition 2, which is a subquery)If *either* of the conditions above is commented out, the query runs quickly. Running the subquery alone works just fine. When both are there, performance jumps from < one second to more than a minute. It seems obvious that the two conditions are interfering with the query performance, but I'm stumped on what to do about it. I've tried moving the subquery conditions to a join (which of course may not always be possible), and the same behavior persists. The joined query works just fine until the additional condition is used.A bit of specifics: the "condition1" in one of the queries is a very simple check on a tinyint field (so I would not think an index would be good here).Here's the psuedo-code fleshed out a bit:WHERE (field1 = 1)AND (thekey IN ( SELECT thekey from thetable where field2 = 'blah')Again, if you comment out either one of those two conditions, the query is very fast. The two of them together, and *blam*.I can probably use temp tables/table vars as workarounds in these two cases, but I suspect I'm missing a basic change in 2005, and it doesn't feel right to change some pretty basic SQL without knowing what's going on...Thanks for any help you can provide!RickEdit: I forgot to mention that when I did run the estimated query plan tool, it did not show a slowdown related to the where conditions. 92% of the cost was in a join condition in a different table (clustered index scan). |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-01-30 : 10:45:07
|
Try transforming query from using IN to using EXISTS:SELECT * FROMTABLE1 T1WHERE (field1 = 1)AND EXISTS(SELECT * from thetable T2 where T1.thekey = T2.thekey AND T2.field2 = 'blah') Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
rickster
Starting Member
5 Posts |
Posted - 2008-01-30 : 10:58:26
|
| Hi, thanks for the quick reply.I tried the EXISTS syntax; it still ran slow, but it seemed to be caused by a GROUP BY/HAVING clause in the subquery (which I did not mention, not thinking it was relevant). It did lead me to the following though, which seems to be working just great. (This particular subquery is there to exclude people in a table more than once, so a count works here).SELECT * FROMTABLE1 T1WHERE (field1 = 1)AND 1 = (SELECT Count(*) from thetable T2 where T1.thefield = T2.the field AND T2.field2 = 'blah')It's really not that different from what I was using, but I can't argue the results. It looks like the GROUP BY/HAVING in the subquery may have been the problem. My other problem query doesn't use any aggregate functions, but I'll try some syntax variations (and see if EXISTS works there.)Thanks!Edit: I should mention that I can't add the GROUP BY logic to the main query due to many other fields being returned that will have differing values. |
|
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2008-01-30 : 14:17:04
|
| Did you try using common table expressions(CTE)? |
|
|
|
rickster
Starting Member
5 Posts |
Posted - 2008-01-30 : 15:27:19
|
quote:
Originally posted by cat_jesus
Did you try using common table expressions(CTE)?
I'm not very familiar with CTE...from looking at the documentation would it be fair to say that it is similar to temp tables as far as reasons to use/how to use? As it turns out, my other problem query was fixed due to a change in business logic requirements that wiped out the need for the subquery :)I certainly need to get more familiar with CTE and temp tables/table vars for that matter, I've never used them much (which I now see caused me to find roundabout solutions sometimes that would have been easy with them.) |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2008-01-30 : 16:48:02
|
| when upgrading a database from SQL 2000 to SQL 2005 it is important to make sure that the compatibility level of the database is switched to version 90 (to enable all sql 2005 features). You will also want to rebuild all indexes and generate new statistics.It is also a good idea to run the SQL 2005 upgrade advisor tool prior to upgrading your database to check for common issues.-ec |
|
|
|
rickster
Starting Member
5 Posts |
Posted - 2008-01-31 : 09:20:50
|
quote:
Originally posted by eyechart
when upgrading a database from SQL 2000 to SQL 2005 it is important to make sure that the compatibility level of the database is switched to version 90 (to enable all sql 2005 features). You will also want to rebuild all indexes and generate new statistics.It is also a good idea to run the SQL 2005 upgrade advisor tool prior to upgrading your database to check for common issues.-ec
Yep, I did run the advisor, but at first the databases on the new server were in version 8 mode...we finished most of our testing before I realized that :PI did not rebuild indexes or stats, I wonder if that could be the problem (once again, one of those things that did not occur to this former developer :)) Thanks. |
|
|
|
|
|
|
|
|
2005 query performance (just upgraded from 2000)