Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can you suggest some rewriting or indexes on this query... Expand / Collapse
Author
Message
Posted Monday, July 29, 2013 3:23 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, April 20, 2014 10:20 PM
Points: 715, Visits: 520
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.



  Post Attachments 
Plan.sqlplan (4 views, 599.97 KB)
Post #1478495
Posted Monday, July 29, 2013 3:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 6,783, Visits: 12,893
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
Post #1478505
Posted Monday, July 29, 2013 3:58 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, April 20, 2014 10:20 PM
Points: 715, Visits: 520
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
)
Post #1478508
Posted Monday, July 29, 2013 4:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 6,783, Visits: 12,893
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
Post #1478509
Posted Monday, July 29, 2013 5:04 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 7:54 AM
Points: 758, Visits: 633
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
Post #1478529
Posted Monday, July 29, 2013 5:49 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, April 20, 2014 10:20 PM
Points: 715, Visits: 520
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.
Post #1478539
Posted Monday, July 29, 2013 6:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 6,783, Visits: 12,893
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
Post #1478546
Posted Monday, July 29, 2013 10:16 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 7:54 AM
Points: 758, Visits: 633
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
Post #1478664
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse