January 31, 2021 at 6:04 pm
I've got a query that recently started performing badly (long running) and at times it will run indefinitely, and other times it runs in 90s (which is still bad). It's basically a combination of 8 selects with UNION ALL...(the query pulls from tables, and a massive view). Indexes have been rebuilt, statistics updated, and there's NO apparent CPU pressure...lots of RAM,
If you run each query individually it will complete in about 90s... but when running it can randomly go out to lunch. A few index tweaks were made and at times, it returns in about 90s (30-35s if we comment out a few XMLPATH calls), and others it retrieves nearly almost all the rows (maybe for the last 5 records) and then just seems to hang and a process/wait stats or resource perspective it appears to literally be doing nothing. The
At one point in our testing we added OPTION MAXDOP 1, RECOMPILE and that gave us more consistent results, but running it a day later and it goes out to lunch all over again. Rerunning it yields the 30-90s run times (which leads me to think it's an IO issue, but the storage admins say it isn't)
The execution plan (while large, appears "fine") with no large keylookups, no implicit conversions, and no large table scans
What could be causing this?
SELECT <<Some Colums>>
from monolith_view a
inner join Employees ee
on a.CtrlNum = ee.CtrlNum
inner join premium_run pr
on pr.SnapshotRun = a.SnapshotRun
left outer join EmpAttributDef ed1
on ed1.CtrlUniqueID = ee.CtrlUniqueID
and ed1.employee_attribute_id = @EmpAttribute1
left outer join EmpAttributDef ed2
on ed2.CtrlUniqueID = ee.CtrlUniqueID
and ed2.employee_attribute_id = @EmpAttribute2
left outer join EmpAttributDef ed3
on ed3.CtrlUniqueID = ee.CtrlUniqueID
and ed3.employee_attribute_id = @EmpAttribute3
left outer join EmpAttributDef ed4
on ed4.CtrlUniqueID = ee.CtrlUniqueID
and ed4.employee_attribute_id = @EmpAttribute4
left outer join EmpAttributDef ed5
on ed5.CtrlUniqueID = ee.CtrlUniqueID
and ed5.employee_attribute_id = @EmpAttribute5
left outer join EmpAttributDef ed6
on ed6.CtrlUniqueID = ee.CtrlUniqueID
and ed6.employee_attribute_id = @EmpAttribute6
left outer join EmpAttributDef ed7
on ed7.CtrlUniqueID = ee.CtrlUniqueID
and ed7.employee_attribute_id = @EmpAttribute7
left outer join EmpAttributDef ed8
on ed8.CtrlUniqueID = ee.CtrlUniqueID
and ed8.employee_attribute_id = @EmpAttribute8
WHERE 1=1
and a.PrevPlanID != a.ActualPlanID
and a.previous_plan_id != 'waive'
AND a.SnapshotRun = @Month
AND a.CtrlNum = @BenefitsID
and (a.BenefitsID=@BenefitsID OR @benefitId = 'ALL')
UNION ALL (7 more times)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 31, 2021 at 9:03 pm
lots of things can cause that - but without table definitions (including indexes and FK's), row counts for each table, view definition as well as any function used within those queries as well as a Actual Explain Plan its rather hard to say what.
but first thing that comes to my mind is rewrite that query so that the filter for all those emp attributes are done outside the main query and into a temp table.
February 1, 2021 at 6:26 am
Firstly, your query is hitting the same table 8 times. This can be vastly improved by using a cross-tab query to only hit the table once. The following 2 queries will have the same results
-- 8 Hits on the EmpAttributDef table
SELECT ee.CtrlUniqueID
, EmpAttribut1 = ed1.AttribValue
, EmpAttribut2 = ed2.AttribValue
, EmpAttribut3 = ed3.AttribValue
, EmpAttribut4 = ed4.AttribValue
, EmpAttribut5 = ed5.AttribValue
, EmpAttribut6 = ed6.AttribValue
, EmpAttribut7 = ed7.AttribValue
, EmpAttribut8 = ed8.AttribValue
from Employees ee
inner join premium_run pr
on pr.SnapshotRun = a.SnapshotRun
left outer join EmpAttributDef ed1
on ed1.CtrlUniqueID = ee.CtrlUniqueID
and ed1.employee_attribute_id = @EmpAttribute1
left outer join EmpAttributDef ed2
on ed2.CtrlUniqueID = ee.CtrlUniqueID
and ed2.employee_attribute_id = @EmpAttribute2
left outer join EmpAttributDef ed3
on ed3.CtrlUniqueID = ee.CtrlUniqueID
and ed3.employee_attribute_id = @EmpAttribute3
left outer join EmpAttributDef ed4
on ed4.CtrlUniqueID = ee.CtrlUniqueID
and ed4.employee_attribute_id = @EmpAttribute4
left outer join EmpAttributDef ed5
on ed5.CtrlUniqueID = ee.CtrlUniqueID
and ed5.employee_attribute_id = @EmpAttribute5
left outer join EmpAttributDef ed6
on ed6.CtrlUniqueID = ee.CtrlUniqueID
and ed6.employee_attribute_id = @EmpAttribute6
left outer join EmpAttributDef ed7
on ed7.CtrlUniqueID = ee.CtrlUniqueID
and ed7.employee_attribute_id = @EmpAttribute7
left outer join EmpAttributDef ed8
on ed8.CtrlUniqueID = ee.CtrlUniqueID
and ed8.employee_attribute_id = @EmpAttribute8
WHERE ...
-- 1 hit on the EmpAttributDef table
SELECT ee.CtrlUniqueID
, EmpAttribut1 = MAX( CASE WHEN ed.employee_attribute_id = @EmpAttribute1 THEN ed.AttribValue ELSE NULL END )
, EmpAttribut2 = MAX( CASE WHEN ed.employee_attribute_id = @EmpAttribute2 THEN ed.AttribValue ELSE NULL END )
, EmpAttribut3 = MAX( CASE WHEN ed.employee_attribute_id = @EmpAttribute3 THEN ed.AttribValue ELSE NULL END )
, EmpAttribut4 = MAX( CASE WHEN ed.employee_attribute_id = @EmpAttribute4 THEN ed.AttribValue ELSE NULL END )
, EmpAttribut5 = MAX( CASE WHEN ed.employee_attribute_id = @EmpAttribute5 THEN ed.AttribValue ELSE NULL END )
, EmpAttribut6 = MAX( CASE WHEN ed.employee_attribute_id = @EmpAttribute6 THEN ed.AttribValue ELSE NULL END )
, EmpAttribut7 = MAX( CASE WHEN ed.employee_attribute_id = @EmpAttribute7 THEN ed.AttribValue ELSE NULL END )
, EmpAttribut8 = MAX( CASE WHEN ed.employee_attribute_id = @EmpAttribute8 THEN ed.AttribValue ELSE NULL END )
from Employees ee
inner join premium_run pr
on pr.SnapshotRun = a.SnapshotRun
left outer join EmpAttributDef ed
on ed.CtrlUniqueID = ee.CtrlUniqueID
and ed.employee_attribute_id IN ( @EmpAttribute1, @EmpAttribute2, @EmpAttribute3, @EmpAttribute4, @EmpAttribute5, @EmpAttribute6, @EmpAttribute7, @EmpAttribute8 )
WHERE ...
GROUP BY ee.CtrlUniqueID
February 1, 2021 at 6:29 am
Secondly, this line in your WHERE clause will ALWAYS cause a full table/index scan.
and (a.BenefitsID=@BenefitsID OR @benefitId = 'ALL')
The reason for this is that SQL has to evaluate every row against the " @benefitId = 'ALL' " predicate.
February 1, 2021 at 8:55 am
All those UNION ALLs could also be an issue. You may want to consider a Divide-n-Conquer path.
Create a #TempTable, then break the massive union all up, inserting each set of results into the temp table. Then do a final select from the temp table. If you have aggregations, do them as early as you can in each process.
February 1, 2021 at 1:47 pm
Rewriting it using the temp table is an option I did consider but was hoping for one of those “a ha” moments ??
The execution plan is not that bad and the largest table involved is about 8,000,000 rows, and a couple others that have a couple of million but nothing crazy. Mostly index seeks, a few scans (but the cost is small and only some of the smaller tables)
Any idea on why it would pull back 99% (33975) of the rows in about 15 seconds but the final few records (let’s say 5 rows) can take several minutes?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 27, 2021 at 7:57 am
This was removed by the editor as SPAM
February 27, 2021 at 6:35 pm
Rewriting it using the temp table is an option I did consider but was hoping for one of those “a ha” moments ??
The "a ha" moment is in 2nd code snippet that DesNorton posted. Have you even tried it?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply