find the record with the maxvalue
Hi,
I have a procedure that will search for a table and find the max value
of a column, then get the record that has this max value.
Because this table is huge, from above, I have to search twice. Is there any
other method That I can only serach once and get the record?
The table LotJob in the following SPL is the huge table.
Table Lot has 6,000 rows.
Table LotJob has 30,000,000 rows.
Table Route has 5,000 rows
The SPC Code 1 is pretty straitforward. It serace Lot table and get Lot.
Then serch LotJob by Lot, and get MAX(LotJobSequence), and use this max
value to find the record that contains this max value.
it do 2 scan.
SPC Code 2 uses ORDER BY to let the record with the MAX value on the
first row and use FOREACH "once" to get the record.
Because the SPL Code2 only do 1 scan, it should be faster than SPL Code 1.
BUt, it doesn't.
BUT, the SPC Code 1 take less time to execute!!!!
(SPL Code1 13 secs and SPL Code2 30 secs)
CAn you give me some advice?? Thanks.
Jack
>>>>>>>>>>>>> SPL codes 1 >>>>>>>>>>
CREATE PROCEDURE lott02a(
,i_Lot CHAR(16) -- Lot
) RETURNING
,INTEGER -- Instance
,CHAR(16) -- Lot
,CHAR(16) -- Product
,CHAR(16) -- Process
,CHAR(16) -- Route
,CHAR(32) -- RouteName
;
DEFINE o_Instance INTEGER;
DEFINE o_Lot CHAR(16);
DEFINE o_Product CHAR(16);
DEFINE o_Process CHAR(16);
DEFINE o_Route CHAR(16);
DEFINE o_RouteName CHAR(32);
DEFINE w_Process CHAR(16);
DEFINE w_LotJobSequence INTEGER;
DEFINE ErrorNumber INTEGER;
DEFINE ErrorSQL INTEGER;
DEFINE ErrorISAM INTEGER;
DEFINE ErrorObject CHAR(40);
LET o_Instance = 0;
FOREACH
SELECT Lot
,Product
,Process
INTO o_Lot
,o_Product
,w_Process
FROM Lot
WHERE Lot LIKE i_Lot
SELECT MAX(LotJobSequence)
INTO w_LotJobSequence
FROM LotJob
WHERE Lot = o_Lot;
IF w_LotJobSequence IS NULL
THEN
LET o_Process = w_Process;
LET o_Route = NULL;
LET o_RouteName = NULL;
ELSE
SELECT
Process
,Route
INTO
o_Process
,o_Route
FROM LotJob
WHERE LotJob.Lot = o_Lot
AND LotJob.LotJobSequence = w_LotJobSequence;
SELECT RouteName
INTO o_RouteName
FROM Route
WHERE Route = o_Route;
END IF
LET o_Instance = o_Instance + 1;
RETURN
o_Instance
,o_Lot
,o_Product
,o_Process
,o_Route
,o_RouteName
WITH RESUME;
END FOREACH
END PROCEDURE;
>>>>>>>>>>>>>> SPC code 2 >>>>>>>>>>>>>>>
CREATE PROCEDURE "sdi".lott02a(
,i_Lot CHAR(16) -- Lot
) RETURNING
,INTEGER -- Instance
,CHAR(16) -- Lot
,CHAR(16) -- Product
,CHAR(16) -- Process
,CHAR(16) -- Route
,CHAR(32) -- RouteName
;
DEFINE o_Instance INTEGER;
DEFINE o_Lot CHAR(16);
DEFINE o_Product CHAR(16);
DEFINE o_Process CHAR(16);
DEFINE o_Route CHAR(16);
DEFINE o_RouteName CHAR(32);
DEFINE w_CurrentQuantity INTEGER;
DEFINE w_Process CHAR(16);
DEFINE w_LotJobSequence INTEGER;
LET o_Instance = 0;
LET w_LotJobSequence = NULL;
FOREACH
SELECT Lot
,Product
,Process
INTO o_Lot
,o_Product
,w_Process
FROM Lot
WHERE Lot LIKE i_Lot
--- The following is the different part
FOREACH
SELECT
Process
,Route
,Step
,LotJobSequence
INTO
o_Process
,o_Route
,o_Step
,w_LotJobSequence
FROM LotJob
WHERE Lot = o_Lot ORDER BY LotJobSequence
EXIT FOREACH;
END FOREACH;
----
IF w_LotJobSequence IS NULL
THEN
LET o_Process = w_Process;
LET o_Route = NULL;
LET o_RouteName = NULL;
LET o_Step = NULL;
LET o_StepName = NULL;
ELSE
SELECT RouteName
INTO o_RouteName
FROM Route
WHERE Route = o_Route;
SELECT StepName
INTO o_StepName
FROM Step
WHERE Step = o_Step;
END IF
LET o_Instance = o_Instance + 1;
RETURN
,o_Instance
,o_Lot
,o_Product
,o_Process
,o_Route
,o_RouteName
WITH RESUME;
END FOREACH
END PROCEDURE;