Stored procedure to find record with max values, help!!

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.

SPC Code 2 uses Temp Table to store records when search LotJob by Lot. usually, the temp table has less than 10 records for every Lot. because I drop the temp table every new Lot, o the Temp table is really small. Then I search the Temp table by MAX(LotJobSequence) and get the record.

BUT, the SPC Code 1 take less time to execute!!!! I thought temp table should be faster, because temp table's size is pretty small compare with LotJob table.

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;


	FOREACH			
	    SELECT Lot
		  ,Product
		  ,Process
	      INTO o_Lot
		  ,o_Product
		  ,w_Process
	      FROM Lot
	     WHERE Lot LIKE i_Lot
     
	    SELECT 
		   ,Process
		   ,Route
		   ,Step
		   ,LotJobSequence
            FROM LotJob
	    WHERE Lot = o_Lot into temp s999 with no log;

            CREATE INDEX s999.idx1 on s999(LotJobSequence);

	    SELECT MAX(LotJobSequence) 
	    INTO w_LotJobSequence
	    FROM s999;


            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 s999
            WHERE s999.LotJobSequence = w_LotJobSequence;

		SELECT RouteName
		  INTO o_RouteName
		  FROM Route
		 WHERE Route = o_Route;

            END IF

	    DROP TABLE s999;

	
	    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 Sat Nov 09 1996 - 00:00:00 CST