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 |
|
sqlclarify
Yak Posting Veteran
56 Posts |
 Posted - 2009-05-10 : 01:34:21
|
| Why does this piece of code take so long to execute? What is wrong with this? Is the logic behind this wrong or something? Are we allowed to compare counts in a query?case when (COUNT(CASE WHEN age > '18') THEN personname END) OVER (PARTITION BY class) = COUNT(CASE age < '18') THEN personname END) OVER (PARTITION BY class))then 'majors' AS compositionendCould somebody help me figure out how to make this run quicker. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-10 : 02:17:15
|
did you have a look at execution plan? i guess over partition is causing lots of reads. can you try the below equivalent and see if its any bettercase when SUM(CASE WHEN age > '18' AND personname IS NOT NULL THEN 1 WHEN age < '18' AND personname IS NOT NULL THEN -1 END) OVER (PARTITION BY class)= 0then 'majors' AS compositionend if its still bad, you could try older way by adding an extra join to derived table that has both the counts and then use it for comparison. |
 |
|
|
sqlclarify
Yak Posting Veteran
56 Posts |
Posted - 2009-05-10 : 02:57:20
|
| Ok, thank you for your advice. I will try this once my network gets up and get back to you. Thank you.You always seem to give good advice. Are there any books/websites that you follow in particular? Or a course that you've taken. I'm kind of learning all this on my own.. so I'm open to suggestions:) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-10 : 03:20:23
|
| WelcomeNo not any specific books as such. I use online articles and also gain a lot by reading questions and solutions posted in forums like this. |
|
|
|
sqlclarify
Yak Posting Veteran
56 Posts |
Posted - 2009-05-10 : 12:47:45
|
| Ok, I tried just doing count(personname) over (partition by class) tooEven that is taking so long. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-10 : 12:51:48
|
| did you try creating a derived table for getting counts and then joining on to it? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-10 : 14:56:28
|
| Can you post the entire query, and also the structures of the tables being queried? A CASE expression does not "take long" to execute, it won't affect execution unless it's in the WHERE or HAVING clauses. And what does "take so long" mean anyway? 10 seconds? 1 minute? 1 hour? |
|
|
|
|
|
|
why is this taking so long?