December 13, 2011 at 12:32 am
Hi, I have a query I need to optimize. I already added a bunch of relevant indexes, and reduced it from 22 mins to 18 - but hardly anything to brag about - takes way too long and only returns 180 rows. Here is the whole query below. Any suggestions? (Do you need the exec plan?) TIA
select r.assignment_id, r.people_id, r.display_name, p.external_id, p.person_phone1, p.person_phone2, p.person_phone3, p.person_email,
r.absent_reason, r.division_id, r.division, r.crew_id, r.crew_name,
case when r.people_id in (select people_id from dbo.rpt_reporting_package_daily_attendance r WITH(NOLOCK)
WHERE r.company_id = '0CA4CFF0-C13D-45A9-906E-FFFF7C1DD583'
AND r.building_id = 'FFDF3559-82F8-47BB-874B-A37DF48CC772'
AND r.report_code_shift_id = 1
AND r.week_number = DATEPART(wk, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))
AND r.day_of_week_id = DATEPART(dw, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))
AND r.calendar_year = YEAR(GETDATE())
AND ot_scheduled = 1) THEN 'Yes' ELSE '' END AS is_ot_day,
CASE WHEN r.absent_reason = 'NCNS' AND v.absent_reason = 'NCNS' THEN 'YES' ELSE '' END as back_to_back_ncns
from dbo.rpt_reporting_package_absent_associates r with(nolock)
inner join dbo.people p with(nolock) on r.people_id = p.people_id
left join dbo.v_s_rpt_reporting_package_most_recent_absent v with(nolock) on r.assignment_id = v.assignment_id
WHERE r.company_id = '0CA4CFF0-C13D-45A9-906E-FFFF7C1DD583'
AND r.building_id = 'FFDF3559-82F8-47BB-874B-A37DF48CC772'
AND r.report_code_shift_id = 1
AND r.week_number = DATEPART(wk, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))
AND r.day_of_week_id = DATEPART(dw, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))
AND r.calendar_year = YEAR(GETDATE())
AND r.absent_type in (1, 5)
AND r.scheduled = 1
December 13, 2011 at 12:39 am
sql_jr (12/13/2011)
Hi, I have a query I need to optimize. I already added a bunch of relevant indexes, and reduced it from 22 mins to 18 - but hardly anything to brag about - takes way too long and only returns 180 rows. Here is the whole query below. Any suggestions? (Do you need the exec plan?) TIA
Yes, we need it. Also the DDLs & sample data.
December 13, 2011 at 12:45 am
OK, so how would I post the Execution Plan here? (Let me think)
The plan indicates the Sort operation is 89% cost of the query, 8% Index Seek, etc.
Any general things to eliminate the Sort? I'll work on the other stuff. Thx
The Sort shows the following properties:
[iss_reports_dev].[dbo].[rpt_reporting_package_daily_attendance].people_id Ascending, [iss_reports_dev].[dbo].[rpt_reporting_package_daily_attendance].work_date Descending
December 13, 2011 at 12:51 am
sql_jr (12/13/2011)
OK, so how would I post the Execution Plan here? (Let me think)The plan indicates the Sort operation is 89% cost of the query, 8% Index Seek, etc.
Any general things to eliminate the Sort? I'll work on the other stuff. Thx
The Sort shows the following properties:
[iss_reports_dev].[dbo].[rpt_reporting_package_daily_attendance].people_id Ascending, [iss_reports_dev].[dbo].[rpt_reporting_package_daily_attendance].work_date Descending
It's simple. Attach the *.sqlplan file in your next reply.
December 13, 2011 at 12:57 am
Here you go sir! THANKS! Cool, never used that feature....
December 13, 2011 at 1:11 am
Can you post the actual plan rather than the estimated plan?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 13, 2011 at 1:15 am
ChrisM@home (12/13/2011)
Can you post the actual plan rather than the estimated plan?
Chris, do you have two accounts in SSC 'ChrisM@Work' & 'ChrisM@home'?
December 13, 2011 at 1:21 am
First thing I'd do is get the correlated subquery out of the result set and into the FROM list to ensure that it's not being run once for every row - SS2K8 is pretty good at resolving correlated subqueries into the FROM list, but is more likely to fall over with a complex statement, which you have, and complex query, which you have if the table sources are views.
If rpt_reporting_package_daily_attendance contains more than one row for people_id compared to rpt_reporting_package_absent_associates (resulting in more than 180 rows in the output) then use the OUTER APPLY, otherwise use the LEFT JOIN:
SELECT
r.assignment_id,
r.people_id,
r.display_name,
p.external_id,
p.person_phone1,
p.person_phone2,
p.person_phone3,
p.person_email,
r.absent_reason,
r.division_id,
r.division,
r.crew_id,
r.crew_name,
CASE WHEN da.people_id IS NOT NULL THEN 'Yes' ELSE '' END AS is_ot_day,
CASE WHEN r.absent_reason = 'NCNS' AND v.absent_reason = 'NCNS' THEN 'YES' ELSE '' END as back_to_back_ncns
from dbo.rpt_reporting_package_absent_associates r with(nolock)
inner join dbo.people p with(nolock)
on r.people_id = p.people_id
LEFT JOIN dbo.rpt_reporting_package_daily_attendance da WITH (NOLOCK)
ON da.people_id = r.people_id
AND da.company_id = r.company_id
AND da.building_id = r.building_id
AND da.report_code_shift_id = r.report_code_shift_id
AND da.week_number = r.week_number
AND da.day_of_week_id = r.day_of_week_id
AND da.calendar_year = r.calendar_year
AND da.ot_scheduled = 1
--OUTER APPLY (
--SELECT TOP 1 people_id
--FROM dbo.rpt_reporting_package_daily_attendance a WITH(NOLOCK)
--WHERE a.people_id = r.people_id
--AND a.company_id = r.company_id
--AND a.building_id = r.building_id
--AND a.report_code_shift_id = r.report_code_shift_id
--AND a.week_number = r.week_number
--AND a.day_of_week_id = r.day_of_week_id
--AND a.calendar_year = r.calendar_year
--AND a.ot_scheduled = 1
-- ORDER BY something
--) da
left join dbo.v_s_rpt_reporting_package_most_recent_absent v with(nolock)
on r.assignment_id = v.assignment_id
WHERE r.company_id = '0CA4CFF0-C13D-45A9-906E-FFFF7C1DD583'
AND r.building_id = 'FFDF3559-82F8-47BB-874B-A37DF48CC772'
AND r.report_code_shift_id = 1
AND r.week_number = DATEPART(wk, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))
AND r.day_of_week_id = DATEPART(dw, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))
AND r.calendar_year = YEAR(GETDATE())
AND r.absent_type in (1, 5)
AND r.scheduled = 1
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 13, 2011 at 1:24 am
Hi Dev, yes I do. There's a good reason - if a project here takes a while to resolve, and some take hours or even days, it wouldn't look good on the work account even if the work was done at home 😉
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 13, 2011 at 1:35 am
Thanks!! I have an issue with this part you added:
inner join dbo.people p with(nolock)
on r.people_id = p.people_id
You'd think there'd be a "people" table, but there is none. I'm wondering if I need to reference another db? Or, is it bc its 3:30am here where I am. 🙂
December 13, 2011 at 1:39 am
sql_jr (12/13/2011)
Thanks!! I have an issue with this part you added:inner join dbo.people p with(nolock)
on r.people_id = p.people_id
You'd think there'd be a "people" table, but there is none. I'm wondering if I need to reference another db? Or, is it bc its 3:30am here where I am. 🙂
It's in the original query - get some sleep:-P If you can post the actual plan before you nod off, that would be awesome.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 13, 2011 at 1:41 am
Thx. Yeah, I wanted to do that, but guess what, the db is suddenly in the middle of restore (prod to dev nightly), so I'll have to wait until ZZZZzzzzzzzz Will post it as soon as it's restored.
December 13, 2011 at 8:23 am
Lots of key lookup operations. I'd be concerned that my clustered indexes are not useful. And that SORT operator is the main cost currently. See if you can modify that index to return the data differently or add another index that supports the SORT better.
I didn't even look at the query yet.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply