Hi Moug,

Based on your execution stats, the "Department" table is the most problematic.

And indeed, based on your query, its implementation looks very... Bizarre.

On the one hand, you use cross join in order to get the data from Department and from Shift. This creates a Cartesian Product - i.e. "everything times everything".

On the other hand, there's only one possible instance where you actually make use of the Department table, and that's based on the following code snippet:

(case d.ShiftID when 1 then c.name when '2' then 'Adimin' when '3' then 'security' else 'closed' end)

In other words, the only time when you use the Department table is when ShiftID in the Shift table equals to 1. In all other cases, the Department table is completely ignored.

Unfortunately, since CASE in SQLServer doesn't help much with the optimizer, this doesn't help reduce the actual number of results returned.

What I would suggest, in this case, is to have a right join between "Department" and "Shift", using the condition: d.ShiftID = 1. i.e., return data from Department only if Shift.ShiftID=1. (the CASE can remain as it is now)

Like so:

select a.name , a.productNumber ,b.StandardPrice ,d.name as shift
, (case d.ShiftID when 1 then c.name when '2' then 'Adimin' when '3' then 'security' else 'closed' end) Deparment
from production.product a inner join Purchasing.ProductVendor b
on a.ProductID = b.ProductID
 , humanresources.Department c RIGHT JOIN humanresources.Shift d
ON d.ShiftID = 1

Also, I don't quite understand why would you want a Cross Join between all your departments and all your shifts and all your products. Can you please exp

Hi All,

Am very new to query tuning, As first step am trying to tune a view in test environment.. On checking the logical reads case statement did 92% of logical reads though the table is just 3 rows, here is sample query from adventureworks DB, please advise a way to tune this query ..With this I can learn to tune the statement in my staging environment.. Also suggest me some books or blogs who are expertise in tuning.. Thanks for your time

select a.name , a.productNumber ,b.StandardPrice ,d.name as shift
, (case d.ShiftID when 1 then c.name when '2' then 'Adimin' when '3' then 'security' else 'closed' end) Deparment
from production.product a inner join Purchasing.ProductVendor b on a.ProductID = b.ProductID , humanresources.Department c , humanresources.Shift d

Stats:

Table 'Department'. Scan count 1, logical reads 2761, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 45, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Shift'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ProductVendor'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

How long does it take to execute on your server?

Is it worth to think about tuning it? 

The above statement is just a sample one, the original in staging environment takes around 25 minutes.. The script is different but i believe if I can tune the case statement in this I can try the same in staging environment

Did you try creating a mapping table and doing a join to it instead of case when statement and see if its any better

Also I dont understand why you've two cross join in the end. Can you elaborate on reason for that?

This below query part

...
 , humanresources.Department c , humanresources.Shift d

is actually a cross join operation as you've specified no conditions

used cross join to get the department and shift for all product name, means all departments to be mapped to all products.. can not perform a mapping table as there is no common column..

You  would have to post the real query, real table and indexes for optimization assistance.

Performance tuning: http://www.sqlusa.com/articles/query-optimization/

The above statement is just a sample one, the original in staging environment takes around 25 minutes.. The script is different but i believe if I can tune the case statement in this I can try the same in staging environment.. :)

No, you can't. The optimizer may make different decisions when the tables are small. Decisions that makes sense with small tables, but not with big tables. When you want to speed up a query, you need to work with the actual workload. Sometimes you may have situation where performance is good for 100 rows and for a million rows, but miserable for 100000 rows, because that the optimizer is making the wrong decision at 100000 rows.

Not knowing anything about you real case, I can only give some general advice.

You mentioned 92% - beware that the percentages you see in the query plan are only estimates, even when you look at the actual execution plan. And the actual execution is preferrably what you should look at. What particularly should attract your eyes is the thickness of the arrows. The culprit in the query plan may be an operator which is estimated to 1%, but which due to a misestimate accounts for 90% of the actual work.

As observed by others, your query has two cross-joins. This may or may not be right, but if the cross-joins produce zillion rows the only cure is patience. And a generous amount of disk space available.

-> Reply to all <-

First of I would like to thank you all your response to my request.

Am using cross join in this example, as in my original example they want data for report covering all products right from the day 1(hence products cross join with day 1/1/2009 till 2050 cause a  Cartesian product)

After some more work around, i noticed the 92% of logical read of case when statement is caused by the select statement which is in that 1%.. so when i tuned that 1%  department table logical read is drastically reduced. I added a where condition for the product table which reduces the case when logical read.

select a.name , a.productNumber ,b.StandardPrice ,d.name as shift
, (case d.ShiftID when 1 then c.name when '2' then 'Adimin' when '3' then 'security' else 'closed' end) Deparment
from production.product a inner join Purchasing.ProductVendor b on a.ProductID = b.ProductID , humanresources.Department c ,
humanresources.Shift d where a.ProductID < 100

Table 'Department'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Shift'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ProductVendor'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So I conclude that if i squeeze the select statement further more the logical read will be reduced for case when statement... I apologize for not posting the original query due to business sensitivity.. The original query would be more meaningful then the example I posted.. :)

Hello Moug,

Thank you for the update.

You've explained why you need a Cartesian Product between all of your products and all of your shifts (did I get that right?).

However, you didn't explain why you need a Cartesian Product with all of the Departments.

Did you try my suggestion? (right join between Department and Shift ON d.ShiftID=1)

Hi Eitan,

Thanks for suggestion, I test your script it is much much better than mine... my script I wrote is just a ad-hoc created as miniature of the original so only it's very bad :) .. I did run your script it gave the exact result I want, without repeating the columns..Now I understand the problematic part.. will try to work on the same right join in my staging.. please find the stats for your scripts..

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 1042, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ProductVendor'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Department'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Shift'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.