|
Re: batch job that run long [message #322136 is a reply to message #322117] |
Thu, 22 May 2008 10:25   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
The MOST effective way to speed up a process is to simply not do the action where the majority of the time is being spent.
SELECT UNIT_RETAIL, MULTI_UNIT1, MULTI_UNIT_RETAIL1, MULTI_UNIT2,
MULTI_UNIT_RETAIL2, MULTI_UNIT3, MULTI_UNIT_RETAIL3, MULTI_UNIT4,
MULTI_UNIT_RETAIL4, MULTI_UNIT5, MULTI_UNIT_RETAIL5, MULTI_UNIT6,
MULTI_UNIT_RETAIL6, MULTI_UNIT7, MULTI_UNIT_RETAIL7, MULTI_UNIT8,
MULTI_UNIT_RETAIL8, MULTI_UNIT9, MULTI_UNIT_RETAIL9, MULTI_UNIT10,
MULTI_UNIT_RETAIL10, MULTI_UNIT11, MULTI_UNIT_RETAIL11, MULTI_UNIT12,
MULTI_UNIT_RETAIL12, MULTI_UNIT13, MULTI_UNIT_RETAIL13, MULTI_UNIT14,
MULTI_UNIT_RETAIL14, MULTI_UNIT15, MULTI_UNIT_RETAIL15, MULTI_UNIT16,
MULTI_UNIT_RETAIL16, ACTIVE_DATE
FROM
OD_ZONE_LVL IT WHERE IT.ITEM = :B2 AND IT.ZONE_ID = :B1 AND IT.ZONE_GROUP_ID
NOT IN(99, 9999) AND IT.ACTIVE_DATE = (SELECT MAX(ITA.ACTIVE_DATE) FROM
OD_ZONE_LVL ITA WHERE ITA.ITEM = IT.ITEM AND ITA.ZONE_ID = IT.ZONE_ID AND
ITA.ACTIVE_DATE <= :B3 AND ITA.ZONE_GROUP_ID NOT IN(99, 9999) AND
ITA.OD_LAST_UPDATE_DATETIME = (SELECT MAX(ITB.OD_LAST_UPDATE_DATETIME) FROM
OD_ZONE_LVL ITB WHERE ITB.ITEM = ITA.ITEM AND ITB.ZONE_ID = ITA.ZONE_ID AND
ITB.ACTIVE_DATE = ITA.ACTIVE_DATE AND ITB.ZONE_GROUP_ID = ITB.ZONE_GROUP_ID)
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 886266 51.83 50.21 0 0 0 0
Fetch 886266 784.77 1178.16 299083 39589445 0 886265
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1772533 836.60 1228.38 299083 39589445 0 886265
Doing the same SQL close to 1,000,000 time seems excessive at 1st glance.
|
|
|
|