List:General Discussion« Previous MessageNext Message »
From:Albart Coster Date:March 21 2011 2:14pm
Subject:Ranking a table within a stored procedure
View as plain text  
Dear list,

since this is the first time that I submit a question to this list, I 
hope that it is not to silly.

My problem is as follows. I need to assing a ranking to the rows a large 
table. In general, I would do it as follows:

SET @i = 0;
UPDATE data_DgSt SET ii=@i:=@i + i ORDER BY datumtijd,laden_lossen

Where table is the name of the table, ii is the column that I want to 
use for the ranking and datumtijd and laden_lossen are the two columns 
on which the ranking will depend.

Now, I need the ranking in a procedure which transfers the data of this 
table to a series of tables in my database. My problem is that I am not 
able to use this simple code within the stored procedure. To overcome 
this, I iterate through the table following the order defined by the 
columns datumtijd and number. The code which I use to rank the table 
within the stored procedure is this:

           DECLARE i        INT(20)        DEFAULT 0;
           DECLARE dsidi    INT(20);
           DECLARE klaar     BOOL        DEFAULT 0;
           DECLARE cur
                            CURSOR FOR
                    SELECT dsid
                    FROM data_DgSt
                    ORDER BY DATUMTIJD,laden_lossen;
           DECLARE
                            CONTINUE HANDLER FOR
                     SQLSTATE '02000'
                     SET klaar = TRUE;
                  OPEN cur;
           mijnloop: LOOP
                    FETCH cur INTO dsidi;
                    SET i = i + 1;
                    UPDATE data_DgSt SET ii = i WHERE dsid = dsidi;
                    IF klaar THEN
                           CLOSE cur;
                           LEAVE mijnloop;
                     END IF;
           END LOOP;

The problem is that this code is much slower than the initial code. In 
my data, the first code takes approximately 10 seconds while the code in 
the loop takes more than 3 minutes. Therefore, I would like to improve 
the speed of the code in the procedure.

I would very much appreciate any help.

Thanks in advance,

Albart Coster

-- 
Albart Coster
Tel: (0031) 64 24 02 923
Fax: (0031) 84 75 98 558

Dairyconsult
www.dairyconsult.nl

Thread
Ranking a table within a stored procedureAlbart Coster21 Mar