October 5, 2010 at 5:12 pm
By any chance, can we optimize the below query..This is running for 2 hrs..As of now, i really don't have any execution plans.
If i replace "IN" with "Exists", i am saving few minutes. Can someone please optimize the query little more?
SELECT pm.pm_no,
pm.pm_key,
trunc(pm.last_completion_dt),
pm.location_name,
loc.unid,
loc.location_desc,
loc.tva_eq_owner_key
FROM
eam_od_pm_tb pm,
eam_od_location_tb loc
where
pm.site_id in ('ALF','BRF', 'COF','CUF','GAF','JOF','JSF','KIF','SHF','WCF', 'CT', 'PAF') and
pm.pm_status_cd = 'ACTIVE' and
pm.last_completion_dt is not null and
loc.location_key = pm.location_key
and
(select count(*)
from
eam_od_work_order_tb b
where
b.location_key = pm.location_key and
b.wo_status_cd IN ('CLOSE','COMP','INTEST','TESTHLD','REVWCMP') and
b.site_id in ('ALF','BRF', 'COF','CUF','GAF','JOF','JSF','KIF','SHF','WCF', 'CT', 'PAF') and
b.job_plan_no in (select seq2.jp_no
from
eam_od_pm_job_plan_seq_tb seq2
where
seq2.pm_key = pm.pm_key)) > 0 and
(select count(*)
from
eam_od_work_order_tb a
where
a.site_id in ('ALF','BRF', 'COF','CUF','GAF','JOF','JSF','KIF','SHF','WCF', 'CT', 'PAF') and
a.wo_status_cd IN ('CLOSE','COMP','INTEST','TESTHLD','REVWCMP') and
(a.tva_reconcile_cd is null or a.tva_reconcile_cd in ('00','05','08'))and
a.work_type_cd = 'PM' and
a.location_key = pm.location_key and
a.job_plan_no in (select seq.jp_no
from
eam_od_pm_job_plan_seq_tb seq
where
seq.pm_key = pm.pm_key)and
TRUNC(a.actl_finish_dt) = TRUNC(pm.last_completion_dt)) = 0 and
trunc(pm.last_completion_dt) > trunc(sysdate-7)
October 5, 2010 at 5:15 pm
See the link in my sig for help with optimizations. We need a lot of your DDL to help you here.
At first glance, I can imagine those count structures in your where clause will be the first target.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 5, 2010 at 5:15 pm
Double post, sorry.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 5, 2010 at 5:35 pm
It would also be nice to know the volumes in each of the tables involved. What would really help would be an execution plan for this query, if you would please attach one.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 5, 2010 at 5:38 pm
The Dixie Flatline (10/5/2010)
It would also be nice to know the volumes in each of the tables involved. What would really help would be an execution plan for this query, if you would please attach one.
He mentioned it was running for 2 hours already, so I assumed he didn't have one of those. 😉
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 5, 2010 at 5:53 pm
Whups...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 6, 2010 at 12:04 am
Craig Farrell (10/5/2010)
The Dixie Flatline (10/5/2010)
It would also be nice to know the volumes in each of the tables involved. What would really help would be an execution plan for this query, if you would please attach one.He mentioned it was running for 2 hours already, so I assumed he didn't have one of those. 😉
2 hours or not, we still need an execution plan to do a proper job. Estimated is acceptable in a pinch (query doesn't finish)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2010 at 6:06 am
Even without an execution plan, I count five (5) different IN statements and I see a number of functions on columns in the WHERE clauses. Those alone will kill performance.
"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 6, 2010 at 7:04 am
Also, the trunc and sysdate functions suggest this is an ORACLE query.
October 6, 2010 at 7:14 am
Here's what it looks like with a little formatting, which goes a surprisingly long way to pointing out areas for improvement:
SELECT
pm.pm_no,
pm.pm_key,
trunc(pm.last_completion_dt),
pm.location_name,
loc.unid,
loc.location_desc,
loc.tva_eq_owner_key
FROM eam_od_pm_tb pm,
eam_od_location_tb loc
WHERE pm.site_id in ('ALF','BRF', 'COF','CUF','GAF','JOF','JSF','KIF','SHF','WCF', 'CT', 'PAF')
and pm.pm_status_cd = 'ACTIVE'
and pm.last_completion_dt is not null
and loc.location_key = pm.location_key
and trunc(pm.last_completion_dt) > trunc(sysdate-7)
AND (select count(*)
from eam_od_work_order_tb b
where b.location_key = pm.location_key
and b.wo_status_cd IN ('CLOSE','COMP','INTEST','TESTHLD','REVWCMP')
and b.site_id in ('ALF','BRF', 'COF','CUF','GAF','JOF','JSF','KIF','SHF','WCF', 'CT', 'PAF')
and b.job_plan_no in (
select seq2.jp_no
from eam_od_pm_job_plan_seq_tb seq2
where seq2.pm_key = pm.pm_key)
) > 0
AND (select count(*)
from eam_od_work_order_tb a
where a.location_key = pm.location_key
and a.wo_status_cd IN ('CLOSE','COMP','INTEST','TESTHLD','REVWCMP')
and a.site_id in ('ALF','BRF', 'COF','CUF','GAF','JOF','JSF','KIF','SHF','WCF', 'CT', 'PAF')
and a.job_plan_no in (
select seq.jp_no
from eam_od_pm_job_plan_seq_tb seq
where seq.pm_key = pm.pm_key)
and (a.tva_reconcile_cd is null or a.tva_reconcile_cd in ('00','05','08'))
and a.work_type_cd = 'PM'
and TRUNC(a.actl_finish_dt) = TRUNC(pm.last_completion_dt)
) = 0
There are one or two "quick and dirty" means of improving the performance of this query. The two correlated subqueries in the WHERE clause could be changed to WHERE EXISTS (SELECT 1 FROM... and WHERE NOT EXISTS (SELECT 1 FROM...
and trunc(pm.last_completion_dt) > trunc(sysdate-7)
could probably be made SARGable, depending on what TRUNC() does. Getting a report of activity for the last week doesn't have to be this inefficient.
Mostly this query would benefit from being properly written with a knowledge of the tables it references. You could start with the two correlated subqueries, check to see if an INNER JOIN could replace the IN. Better still, check if eam_od_pm_job_plan_seq_tb can slot into the FROM list without affecting the overall rowcount. Use proper JOINs.
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
October 6, 2010 at 7:17 am
Ken McKelvey (10/6/2010)
Also, the trunc and sysdate functions suggest this is an ORACLE query.
I think you're right.
There's a TRUNC function in report builder, but not one in SQL Server and it can't be a user defined function. Would have to be dbo.Trunc in that case.
Sahasam: Is this a Microsoft SQL Server query?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2010 at 7:46 am
Ken McKelvey (10/6/2010)
Also, the trunc and sysdate functions suggest this is an ORACLE query.
Darn. :blush:
Wish I'd caught this, what a waste of a lunchtime.
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
October 6, 2010 at 8:23 am
There are 2 suggestion for this:
1: Remove count(*) and replace it with count(any one INTEGER type column). Here all you are doing is checking if there is any row > 0
2: In the mentioned query, if there are any static TABLES used, make sue of (NOLOCK) which will help reduce the time
e.x: if the same table is being used by some other object at the same time this will be a problem hence use NOLOCK.
NOTE: Use this NOLOCK only if its astatic table, else you will be DIRTY reading.
October 6, 2010 at 8:31 am
vijay1327-891581 (10/6/2010)
NOTE: Use this NOLOCK only if its astatic table, else you will be DIRTY reading.
If it's a static table then NOLOCK won't help since there won't be any locks that will stop you from reading from the table. The only time I use NOLOCK is if I'm certain that I don't need the data returned to be completely accurate.
John
October 6, 2010 at 8:37 am
vijay1327-891581 (10/6/2010)
1: Remove count(*) and replace it with count(any one INTEGER type column). Here all you are doing is checking if there is any row > 0
Since the two subqueries in the WHERE clause are checking only for (non)existence of matching rows, WHERE (NOT) EXISTS (SELECT 1 FROM... will perform much faster than COUNT(? because the process stops as soon as a matching row is intercepted. COUNT(? will have to scan all of them. Also, COUNT(*) has been changed somewhat in SQL2K8 and now performs similarly to COUNT(any one INTEGER type column).
All moot points if the db is Oracle.
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 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply