July 29, 2013 at 3:23 am
Hi All,
Here is one more query for you people to check and optimize.
It is taking 1-1.5 mins in execution on local server.
Query and I/O stats are pasted and execution plan is attached for your reference.
Can you suggest some rewriting or indexes on it:
selectts.time_sheet_ref as document_ref,
ts.status as status,
wk.display_name as wk_name,
(selectmax(action_time)
from dbo.pmo_dashboard_action pda
where pda.object_id = pd.object_id) as last_action_date,
ts.time_sheet_id as object_id,
ts.revision_time,
ts.last_submit_time,
ts.end_date,
isnull(bd.name,bu.name) as bu_name,
isnull(sd.name,site.name) as site_name,
company_name.name as supplier_name,
wo.new_requisition_owner_id as owner_id,
p.display_name as owner_p ,
ts.supplier_code as supplier_code,
'' as recipient_id
fromqa3_offline.dbo.pmo_dashboard pd
inner join dbo.time_sheet ts on ts.time_sheet_id = pd.object_id
inner join dbo.work_order wo on ts.work_order_id = wo.work_order_id
inner join dbo.worker wk on wk.worker_id = ts.worker_id
inner join dbo.person p on p.person_id = wo.new_requisition_owner_id
inner join dbo.bu on bu.bu_id = ts.bu_id
inner join dbo.site on site.site_id = ts.site_id
inner join dbo.company_name on company_name.company_code = ts.supplier_code
and cast(getdate() as date) between company_name.start_date AND company_name.end_date
left join dbo.bu_locale bd on bu.bu_id = bd.bu_id and bd.locale='en_US'
left join dbo.site_locale sd on site.site_id = sd.site_id and sd.locale = 'en_US'
wherets.buyer_code = 'BACG' and
(pd.activity_id = 10172) and
(isnull('CRITICAL','ALL')='ALL'
or
pd.threshold_level = 'CRITICAL'
)
union
selectts.time_sheet_ref as document_ref,
ts.status as status,
wk.display_name as wk_name,
(selectmax(action_time)
from dbo.pmo_dashboard_action pda
where pda.object_id = pd.object_id) as last_action_date,
ts.time_sheet_id as object_id,
ts.revision_time,
ts.last_submit_time,
ts.end_date,
isnull(bd.name,bu.name) as bu_name,
isnull(sd.name,site.name) as site_name,
company_name.name as supplier_name,
wo.new_requisition_owner_id as owner_id,
p.display_name as owner_p ,
ts.supplier_code as supplier_code,
'' as recipient_id
fromqa3_offline.dbo.pmo_dashboard pd
inner join dbo.time_sheet ts on ts.time_sheet_id = pd.object_id
inner join dbo.work_order wo on ts.work_order_id = wo.work_order_id
inner join dbo.worker wk on wk.worker_id = ts.worker_id
inner join dbo.person p on p.person_id = wo.new_requisition_owner_id
inner join dbo.bu on bu.bu_id = ts.bu_id
inner join dbo.site on site.site_id = ts.site_id
inner join dbo.company_name on company_name.company_code = ts.supplier_code
and cast(getdate() as date) between company_name.start_date AND company_name.end_date
left join dbo.bu_locale bd on bu.bu_id = bd.bu_id and bd.locale='en_US'
left join dbo.site_locale sd on site.site_id = sd.site_id and sd.locale = 'en_US'
wherets.buyer_code = 'BACG' and
(isnull(10172,0)=0 ) and
(isnull('CRITICAL','ALL')='ALL'
or
pd.threshold_level = 'CRITICAL'
)
(4662 row(s) affected)
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 '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 'person'. Scan count 0, logical reads 18948, 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 'bu'. Scan count 2, logical reads 9030, physical reads 0, read-ahead reads 25, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'site'. Scan count 2, logical reads 4938, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'worker'. Scan count 2, logical reads 13851, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'bu_locale'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'site_locale'. Scan count 2, logical reads 538, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'pmo_dashboard_action'. Scan count 4663, logical reads 111480, physical reads 0, read-ahead reads 45, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'work_order'. Scan count 1, logical reads 45638, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'time_sheet'. Scan count 0, logical reads 265919, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'pmo_dashboard'. Scan count 1, logical reads 32760, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'company_name'. Scan count 1, logical reads 285, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
July 29, 2013 at 3:49 am
Use one query instead of two almost-identical ones.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 29, 2013 at 3:58 am
It was 1 query. I split OR part mentioned below into 2 queries and used union. Now it is performing 65% better then earlier.
but still consuming a lot of time. I think now only an Index can help.
Earlier query was having:
(isnull(10172,0)=0
or
pd.activity_id = 10172
)
July 29, 2013 at 4:00 am
Why do you have
isnull(10172,0)=0
in your query at all?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 29, 2013 at 5:04 am
The optimizer often does a better job with UNION than OR, but that applies to conditions like:
thiscol = @value OR thatcol = @value
Which does not seem to be the case here. And when it does, you should not duplicate the full query, but put the condition in a derived table which you use in the rest of the query:
JOIN (SELECT ...
FROM sometable
WHERE thiscol = @value
UNION
SELECT sometable
WHERE thatcol = @value) AS u ON ....
But as Chris alludes, this is not your scenario here. You query includes these conditions:
(isnull(10172,0)=0 ) and
(isnull('CRITICAL','ALL')='ALL'
Which is kind of nonsense. I suspect that the real queries has variables/parameters. Restore the original query and add OPTION (RECOMPILE) at the end of the query. A query with conditions like these benefits from a recompile everytime, since depending on the parameter value, different plans are called for.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 29, 2013 at 5:49 am
Parameters goes like this:
(isnull(@activity_id,0)=0
or
pd.activity_id = @activity_id)
and
(isnull(@threshold,'ALL')='ALL'
or
pd.threshold_level = @threshold)
I hope I will find some other way to write it, as same way it is use in many other places.
July 29, 2013 at 6:17 am
Parameter sniffing is likely to affect your query. Goggle it and look for articles by known folks - I think Gail has one of the best articles.
More importantly, there are a quite a few index scans without predicates which suggests that there's scope for adjusting your indexing in favour of this query. I think this is your best shot.
You may well discover that with tuned indexes, using OR will outperform UNION in this case - don't forget to test it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 29, 2013 at 10:16 am
T.Ashish (7/29/2013)
Parameters goes like this:(isnull(@activity_id,0)=0
or
pd.activity_id = @activity_id)
and
(isnull(@threshold,'ALL')='ALL'
or
pd.threshold_level = @threshold)
As I said in my previous post, you should use OPTION(RECOMPILE) for this type of query. If you have indexes on activity_id and threshold_level, they may be very good for your query, if the parameter is for a specific activity or threshold - but uselss if the parameter is 0 or ALL.
OPTION(RECOMPILE) may not do everything for you, but you should start there.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply