Query with High CPU Cost

[Ovatvvon]

This select query runs on our servers, and runs for a few days without ceasing, causing the CPU to run between 90%-100%. I ran an estimated execution plan against it, and 80% of the cost is from an Index Seek, and about 20% is from the sort. I did a rebuild on the Index, and updated the stats, but the query still causes the CPU's to spike and continues to run, even if I specify to only grab the top 2 records.

Anyone have any thoughts on this? The FROM clause is a bit different, I'm used to seeing Inner/Outer joins, but not sure what straight comma's mean. Don't know if that is playing into it somehow.

Any help is greatly appreciated!

select l_name, f_name, users.EMPLOYEE_ID, min(extn) extn, min(bill_level_code) cost_ctr1, max(bill_level_code) cost_ctr2 

from users, users_extn, extn, bill_detail, bill_level 

where users.user_id = bill_detail.user_id and users_extn.extn_id = extn.extn_id and bill_detail.bill_level_id = bill_level.bill_level_id and bill_detail.bill_level_id != 0 

group by l_name, f_name, employee_id having min(bill_level_code) != max (bill_level_code) order by l_name, f_name

-Ovatvvon :-Q

 

[gmmastros]

The commas in the from clause may be the cause of your problem, but I doubt it. The 'comma business' needs to go away. It's old syntax that really needs to go away. In fact, it's the same as a cross join.

With a cross join, you get the combination of every row to every other row. For example, if table1 has 100 rows, table2 has 50 rows, and Table3 has 200 rows, a cross join between these tables will result in 100 * 50 * 200 rows (1 million rows). I don't know how many rows you have in your tables, but you can see how even modest sized tables will quickly expand to something outrageous.

Normally, you can convert from that style to the ANSI style joins easily.

Ex:

[tt][blue]
Select *
From Table1, Table2
Where Table1.Id = Table2.Id
[/blue][green]
Select *
From Table1
Inner Join Table2
On Table1.Id = Table2.id
[/green][/tt]

Taking a close look at your query.... you appear to be missing a join to users_extn. Fix that and most of your issues will probably go away.

select l_name, 
       f_name, 
       users.EMPLOYEE_ID, 
       min(extn) extn, 
       min(bill_level_code) cost_ctr1, 
       max(bill_level_code) cost_ctr2 
from   users
       Inner Join bill_detail 
         On users.user_id = bill_detail.user_id
       Inner Join bill_level 
         On bill_detail.bill_level_id = bill_level.bill_level_id
         And bill_detail.bill_level_id != 0 
       Inner Join users_extn
         [!]On ????????[/!]
       Inner Join extn
         On users_extn.extn_id = extn.extn_id
group by l_name, f_name, employee_id 
having min(bill_level_code) != max (bill_level_code) 
order by l_name, f_name

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Actually, my advice earlier was correct, but... missing join conditions are difficult to see when you don't know the schema. The problem could be the missing join on the users_extn table, but could also be the extn table. I say this because you should investigate that as a possibility.

select l_name, 
       f_name, 
       users.EMPLOYEE_ID, 
       min(extn) extn, 
       min(bill_level_code) cost_ctr1, 
       max(bill_level_code) cost_ctr2 
from   users
       Inner Join bill_detail 
         On users.user_id = bill_detail.user_id
       Inner Join bill_level 
         On bill_detail.bill_level_id = bill_level.bill_level_id
         And bill_detail.bill_level_id != 0 
       Inner Join extn
         [!]On ????????[/!]
       Inner Join users_extn
         On users_extn.extn_id = extn.extn_id
group by l_name, f_name, employee_id 
having min(bill_level_code) != max (bill_level_code) 
order by l_name, f_name

I feel strongly that it's one or the other. Fixing that will probably fix your CPU utilization.

In either case, can you please let me know how you make out with this. I'm really curious to know what performance implications this has.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom