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;   Received on Thu Nov 14 1996 - 00:00:00 CET