September 3, 2013 at 6:23 am
T.Ashish (8/27/2013)
Hi Chris,Thanks again for your help.
I could not see any performance difference with/without function. I have pasted the function for you. Meanwhile I'm looking into third point you suggested.
CREATE FUNCTION dbo.get_job_seeker_ref (
@userType int, @suppressSupplierFlag BIT, @roleFlag BIT, @status INT, @isMspCoordinator BIT, @jobSeekerRef VARCHAR(14))
RETURNS TABLE AS
RETURN
SELECT CASE WHEN (@userType = 1 and @isMspCoordinator = 0 and @suppressSupplierFlag = 1 and @roleFlag = 0 and @status IN( 0, 1, 7 , 8, 5, 20, 30, 31, 41, 47, 10, 54, 56, 62, 72, 11, 73, 74, 75, 76, 82, 83, -1, 71))
THEN
('XXXX' + substring(@jobSeekerRef,len(@jobSeekerRef)- 9, 14))
ELSE @jobSeekerRef
END AS job_seeker_ref
"Meanwhile I'm looking into third point you suggested."
What are your findings?
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
September 3, 2013 at 6:46 am
Chris,
I split the OR condition of query in two parts and combined results of both queries with UNION ALL. New query is 50% faster. I have attached new plan for you.
and
(
(wo.service_type = 2 and wo.start_date <= '2013-11-27 00:00:00.0' )
OR -- I used UNION ALL
( wo.start_date <= '2013-11-27 00:00:00.0' and wo.end_date >= '2013-07-30 00:00:00.0' )
)
New IO stats :
Table 'job_posting_visib'. Scan count 40, logical reads 160, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'site_locale'. Scan count 0, logical reads 120, 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 160, physical reads 6, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'work_order'. Scan count 42, logical reads 8383, physical reads 9, read-ahead reads 687, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'worker'. Scan count 1798, logical reads 14875, physical reads 422, read-ahead reads 8, 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 '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 'job_posting'. Scan count 0, logical reads 10220, physical reads 5, read-ahead reads 7719, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'company_name'. Scan count 1, logical reads 253, physical reads 0, read-ahead reads 39, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'cost_center'. Scan count 0, logical reads 5801, physical reads 2, read-ahead reads 57, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'job_seeker'. Scan count 0, logical reads 8137, physical reads 63, read-ahead reads 3681, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'bu'. Scan count 0, logical reads 5769, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'site'. Scan count 1, logical reads 2243, physical reads 0, read-ahead reads 33, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'cost_center_locale'. Scan count 1, logical reads 211, physical reads 0, read-ahead reads 56, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'bu_locale'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'labor_type'. 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 '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.
September 3, 2013 at 7:18 am
T.Ashish (9/3/2013)
Chris,I split the OR condition of query in two parts and combined results of both queries with UNION ALL. New query is 50% faster. I have attached new plan for you.
and
(
(wo.service_type = 2 and wo.start_date <= '2013-11-27 00:00:00.0' )
OR -- I used UNION ALL
( wo.start_date <= '2013-11-27 00:00:00.0' and wo.end_date >= '2013-07-30 00:00:00.0' )
)
New IO stats :
Table 'job_posting_visib'. Scan count 40, logical reads 160, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'site_locale'. Scan count 0, logical reads 120, 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 160, physical reads 6, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'work_order'. Scan count 42, logical reads 8383, physical reads 9, read-ahead reads 687, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'worker'. Scan count 1798, logical reads 14875, physical reads 422, read-ahead reads 8, 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 '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 'job_posting'. Scan count 0, logical reads 10220, physical reads 5, read-ahead reads 7719, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'company_name'. Scan count 1, logical reads 253, physical reads 0, read-ahead reads 39, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'cost_center'. Scan count 0, logical reads 5801, physical reads 2, read-ahead reads 57, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'job_seeker'. Scan count 0, logical reads 8137, physical reads 63, read-ahead reads 3681, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'bu'. Scan count 0, logical reads 5769, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'site'. Scan count 1, logical reads 2243, physical reads 0, read-ahead reads 33, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'cost_center_locale'. Scan count 1, logical reads 211, physical reads 0, read-ahead reads 56, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'bu_locale'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'labor_type'. 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 '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.
How about posting the code as well.
September 3, 2013 at 9:00 am
The first plan is less than optimal; Grant Fritchey writes about "Reason for Early Termination" here[/url].
You've got a potentially redundant table in your FROM list - dbo.company_name - look at where it's referenced in the output, the condition precludes it's use. Check if any other tables can be removed from the query.
If it were me, I'd prepare a few of the table pairs as single temp tables to simplify the job of the query optimiser (for the purpose of tuning only, I often restore them afterwards). I think there are four pairs of tables, parent-child, like this:
inner join dbo.cost_center cc on wo.primary_cost_center_id = cc.cost_center_id
left join dbo.cost_center_locale ccd on cc.cost_center_id = ccd.cost_center_id and ccd.locale = @0
inner join dbo.bu on wo.bu_id = bu.bu_id
left join dbo.bu_locale bd on bu.bu_id = bd.bu_id and bd.locale = @3
inner join dbo.site on wo.site_id = site.site_id
left join dbo.site_locale sd on site.site_id = sd.site_id and sd.locale = @4
left join dbo.job_posting jp on jp.job_posting_id = j.job_posting_id
left join dbo.labor_type lt on lt.labor_type_id = jp.labor_type_id
The WHERE clause is quite complex. I'd work on it in isolation from as much as possible of the bulk of the query.
Most of the index usage shown in the plan is clustered indexes. Even though the costs of the majority of them are 0 or close to 0, you may well get some mileage from adding ordinary indexes where appropriate.
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
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply