October 20, 2014 at 8:56 am
Hi all,
The attached script is taking some 20 mins to fetch some 1.5 million records. Our system times out in 11 mins, means this script will never succeed.
I have tried a few tweaks like splitting OR, adding a few indexes but that is also not helping, and I'm not finding any way to rewrite the script.
I have attached its execution plan also for your review. This script is used for insertion in some other table.
Any sort of help will be highly appreciated.
Regards.
October 20, 2014 at 9:08 am
1) ORs are killers
2) Just spooling 1.5M rows back to a client (especially if that client does row-by-row fetching) can alone be a cause of SIGNIFICANT slowness. Put the output columns into variables and run the SELECT to actually see how long SQL Server takes to process the data.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 20, 2014 at 10:00 am
In addition to the OR, we're doing NOT EQUALS. Between the two you're pretty much guaranteed scans on your indexes.
"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
October 21, 2014 at 11:33 pm
Thanks for review.
Yes you are right, these ORs are main issue. Earlier we had few more ORs, and by removing them I managed to get some 40-45% performance gain (I split the script and used UNION).
Now I'm not finding any way to remove these remaining ORs or to rewrite the script. Thats why I have put up the issue here, and I'm sure you intelligent brains can definitely suggest something.
Regards.
October 22, 2014 at 3:42 am
-- You could try breaking up the query. Here's one way, there are a few possibilities:
SELECT
[wck.wck_id] = wck.wck_id,
[pd.mode_id] = pd.mode_id,
[p.person_id] = p.person_id,
[wck.worker_id] = wck.worker_id,
[wck.worker_order_id] = wck.worker_order_id,
[p.access_site] = p.access_site,
[p.access_bu] = p.access_bu
INTO #person_wck_item_dashboard
FROM dbo.person p
INNER JOIN dbo.wck_item wck
ON wck.buyer_id = p.company_id
AND wck.worker_id <> ''
INNER JOIN offline_DB.dbo.dashboard pd
ON pd.[object_id] = wck.wck_id
AND pd.mode_id IN (870, 1350)
AND pd.activity_id = 10179
WHERE p.role_id <> '5'
AND NOT (p.access_site = 'ALL' AND p.access_bu = 'ALL' AND p.access_cc = 'ALL')
AND p.flag_access = 1
-- You may need an index or two on the temp table #person_wck_item_dashboard
-- try a clustered index on [p.person_id]
SELECT
[object_id] = [wck.wck_id],
mode_id = [pd.mode_id],
person_id = [p.person_id]
FROM #person_wck_item_dashboard pwd
LEFT JOIN dbo.worker w
on w.worker_id = [wck.worker_id]
LEFT JOIN dbo.worker_order wo
on wo.worker_order_id = [wck.worker_order_id]
LEFT JOIN dbo.job_enquiry jp
on jp.job_enquiry_id = wo.job_enquiry_id
WHERE
NOT EXISTS (
SELECT1
FROMoffline_DB.dbo.dashboard_visible pdvs
WHEREpdvs.object_id = [wck.wck_id] -- outer reference wck
AND pdvs.mode_id = [pd.mode_id] -- outer reference pd
AND pdvs.person_id = [p.person_id]) -- outer reference p
--AND (1 = 1
AND ((
([p.access_site] = 'ALL' OR EXISTS (
SELECT 'x'
FROM dbo.person_site ps
WHERE ps.site_id = wo.site_id -- outer reference wo
AND ps.person_id = [p.person_id])) -- outer reference p
AND ([p.access_bu] = 'ALL' OR EXISTS (
SELECT 'x'
FROM dbo.person_bu pb
WHERE pb.bu_id = wo.bu_id -- outer reference wo
AND pb.person_id = [p.person_id])) -- outer reference p
AND EXISTS (
SELECT 'x'
FROM dbo.worker_cc wcc
INNER JOIN dbo.person_cc pcc
ON wcc.cost_center_id = pcc.cost_center_id
WHERE wcc.worker_id = w.worker_id -- outer reference w
AND pcc.person_id = [p.person_id]) -- outer reference p
))
--)
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply