SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can you suggest some rewriting or indexes on this query...


Can you suggest some rewriting or indexes on this query...

Author
Message
T.Ashish
T.Ashish
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1567 Visits: 614
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:


select ts.time_sheet_ref as document_ref,
ts.status as status,
wk.display_name as wk_name,
(select max(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
from qa3_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'
where ts.buyer_code = 'BACG' and
(pd.activity_id = 10172) and
(isnull('CRITICAL','ALL')='ALL'
or
pd.threshold_level = 'CRITICAL'
)
union
select ts.time_sheet_ref as document_ref,
ts.status as status,
wk.display_name as wk_name,
(select max(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
from qa3_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'
where ts.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.
Attachments
Plan.sqlplan (7 views, 599.00 KB)
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51080 Visits: 20096
Use one query instead of two almost-identical ones.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
T.Ashish
T.Ashish
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1567 Visits: 614
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
)
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51080 Visits: 20096
Why do you have

isnull(10172,0)=0



in your query at all?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6650 Visits: 875
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.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
T.Ashish
T.Ashish
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1567 Visits: 614
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.
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51080 Visits: 20096
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.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6650 Visits: 875
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.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search