| Speed up this SQL [message #520211] |
Fri, 19 August 2011 05:48  |
 |
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 #520424 is a reply to message #520212] |
Mon, 22 August 2011 08:16  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
