Home » RDBMS Server » Performance Tuning » Speed up this SQL (Oracle 10g)
Speed up this SQL [message #520211] Fri, 19 August 2011 05:48 Go to next message
r23rob
Messages: 1
Registered: August 2011
Junior Member
Hi,

Please excuse me If I have missed something in this post it is my first question however I am hoping someone may be able to help?
Although my query works it is taking 15 + mins to execute believe there is a more efficient way of checking the date.
Basically I have a workorder which can have many child workorders created if a card has to be remade.
The complication comes in that the parent workorder may or may not be closed of before the child so I need to take the highest finished date of the child or parent.(see sql below)
Can anyone suggest a more effective way of checking the finished date for a child workorder?
Thanks
Rob

Workorder
WORK		PARENTWORK	 CUSTOMER	 STATUS 	QUANTITY	TEST	 FINISHED
ORDERID	ORDERID 		ORDERID 					FLAG 	DATE	
25615					11999		600		10		0	07/05/2011 
25616		25615			11999		600		4		0	07/06/2011 
26666		25616			11999		600		2		0	07/06/2011 



Card_Arc

CARD		WORK		INDEX		EXPORTED	REWORK	INIT		ORIGINAL
ID		ORDERID	NUMBER	KEYVALUE	FLAG		CARDID	WORKORDERID
1217357	25615		1		D SHIRE	1		1217357	25615
1217358	25615		2		J Hunt		1		1217358	25615
1217359	25615		3		R FRED	1		1217359	25615
1217360	25615		4		R BEN		1		1217360	25615
1217361	25615		5		A WILKO	1		1217361	25615
1217362	25615		6		J BLOGS	1		1217362	25615
1217363	25615		7		G SMITH	0		1217363	25615
1217364	25615		8		H HENDRY	0		1217364	25615
1217365	25615		9		R BARKER	0		1217365	25615
1217366	25615		10		A ALBO	0		1217366	25615
1217401	25616		1		D SHIRE	0		1217357	25615
1217402	25616		2		J Hunt		0		1217358	25615
1217403	25616		3		R FRED	0		1217359	25615
1217404	25616		4		R BEN		0		1217360	25615
1217405	26666		3		R FRED	0		1217359	25615
1217406	26666		4		R BEN		0		1217360	25615

SQL-

select 
    ca.originalworkorderid as WoID,
    count(ca.CARDID) as wo_Quan
from customerorder co
join workorder wo
on 
    wo.customerorderid = co.customerorderid
join card_arc ca
on 
    ca.workorderid = wo.workorderid
where 
   (
     select trunc(max(finisheddate)) from workorder wo2
     where (wo2.workorderid = wo.workorderid
     or wo2.workorderid = ca.originalworkorderid)
   ) = to_date('05/07/2011','dd/mm/yyyy')
AND 
    wo.Testflag = 0
AND 
    wo.status <> 700
AND 
    CA.reworkflag = 0
group by ca.originalworkorderid


[RL: code tags added - not sure it helped]

[Updated on: Mon, 22 August 2011 08:04] by Moderator

Report message to a moderator

Re: Speed up this SQL [message #520212 is a reply to message #520211] Fri, 19 August 2011 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any peformances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: Speed up this SQL [message #520424 is a reply to message #520212] Mon, 22 August 2011 08:16 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you have an indexes on
- workorder(finisheddate)
- workorder(workorderid)
- customerorder(customerorderid)
- card_arc(workorderid

Then the following should have a chance of working well.

SELECT ca.originalworkorderid AS WoID,
       count(ca.CARDID) AS wo_Quan
FROM customerorder co
JOIN workorder wo ON wo.customerorderid = co.customerorderid
JOIN card_arc ca  ON ca.workorderid = wo.workorderid
WHERE wo.workorderid IN (
    SELECT workorderid 
    FROM workorder 
    WHERE workorderid IN (
      SELECT workorderid
      FROM   workorder wo2
      WHERE  finisheddate = to_date('05/07/2011','dd/mm/yyyy')
    )
    GROUP BY workorderid 
    HAVING MAX(finisheddate) = to_date('05/07/2011','dd/mm/yyyy')
  ) 
AND   wo.Testflag = 0
AND   wo.status <> 700
AND   CA.reworkflag = 0
GROUP BY ca.originalworkorderid


Ross Leishman
Previous Topic: performance tuning PROBLEM
Next Topic: application performance
Goto Forum:
  


Current Time: Thu Mar 28 08:55:23 CDT 2024