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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=listsearcharchive@lassosoft.com