Interpolation between values of one table
Hi,
I have a performance problem. I have two tables.
This ist the first table:
CREATE TABLE Demobauteil.robotposition(rtdbtime BIGINT PRIMARY KEY, ipoc BIGINT, interpolated TINYINT,
xCurr DOUBLE, yCurr DOUBLE, zCurr DOUBLE, aCurr DOUBLE, bCurr DOUBLE, cCurr DOUBLE,
xNom DOUBLE, yNom DOUBLE, zNom DOUBLE, aNom DOUBLE, bNom DOUBLE, cNom DOUBLE,
velocity DOUBLE, isDelayed BOOLEAN, absolutDelay MEDIUMINT,
offsetY DOUBLE, offsetZ DOUBLE, referenceId BIGINT, robotStatus BIGINT, robotProgramStatus BIGINT);
And this ist the second table:
CREATE TABLE Demobauteil.keyence(rtdbtime BIGINT PRIMARY KEY, value DOUBLE, x DOUBLE, y DOUBLE, z DOUBLE, quality DOUBLE);
I get position information from an robot and write them into the robotposition-table parrallel I get values from a lasersensor and write them into the keyence-table. Both tables have the rtdbtime as primary key. This is a global timestamp from my realtime system.
After the measurement of the robotpositions and the laservalues I want to interpolate the robotposition for each timestamp of the sensorvalues. For example the robot has the following rtdbtimes "1 5 12 30" and the sensor has the following rtdbtimes "2 10 20". For each rtdbtime the robot has a 3D-Position. Now I take the first value of my lasersensor-table (for example "2") and try to find the value small then this and the higher value, in the robotposition-table. In this case I would find "1" and "5". Then I calculate the 3D-Position of the robot at time "2".
My problem is, that the tables have a size of more then 500.000 datasets and it needs more then 20h to calculate the interpolation points. I use stored procedures. The most important procedures are this:
DELIMITER //
CREATE PROCEDURE keyence_insertInterPoints()
BEGIN
DECLARE v1 INT DEFAULT 0;
SET @t=0;
SET @count=(SELECT COUNT(*) from keyence);
WHILE v1 < @count DO
SELECT rtdbtime into @t from keyence where @t<rtdbtime LIMIT 1;
IF (IFNULL((SELECT rtdbtime from robotposition where rtdbtime=@t),TRUE)) THEN
CALL robotposition_setInterPoint(@t);
END IF;
SET v1=v1+1;
END WHILE;
END;
//
DELIMITER ;
DELIMITER //
CREATE PROCEDURE robotposition_setInterPoint(in rtdbtimeIn BIGINT)
BEGIN
CALL keyence_getPositionIpocBefor(rtdbtimeIn,'robotposition', @ipocBefor);
CALL keyence_getPositionIpocAfter(rtdbtimeIn,'robotposition', @ipocAfter);
#SELECT @ipocBefor,@ipocAfter;
IF (@ipocBefor<=>@ipocAfter or @ipocBefor<=>NULL or @ipocAfter<=>NULL) THEN
SET @x=NULL;
SET @y=NULL;
SET @z=NULL;
SET @a=NULL;
SET @b=NULL;
SET @c=NULL;
ELSE
CALL keyence_getFactorForToolCalc(@ipocBefor,@ipocAfter,'robotposition',rtdbtimeIn,@factor);
CALL keyence_getDistanceBetweenRobotPosition(@ipocBefor,@ipocAfter,'robotposition',@distance);
CALL keyence_getRobotPositionByIpoc(@ipocBefor,'robotposition',@xBefor,@yBefor,@zBefor,@aBefor,@bBefor,@cBefor);
CALL keyence_getRobotPositionByIpoc(@ipocAfter,'robotposition',@xAfter,@yAfter,@zAfter,@aAfter,@bAfter,@cAfter);
SET @x=@xBefor+@factor*(@xAfter-@xBefor);
SET @y=@yBefor+@factor*(@yAfter-@yBefor);
SET @z=@zBefor+@factor*(@zAfter-@zBefor);
#Das hier sollte noch verbessert werden. DAs Problem bei den Winkeln ist, das man nciht einfach die selbe Formel anwenden kann wie bei der Translation weil die Werte von -180 auf +180 springen können.
SET @a=@aAfter;
SET @b=@bAfter;
SET @c=@cAfter;
insert into robotposition (ipoc, rtdbtime, interpolated, xCurr, yCurr, zCurr, aCurr, bCurr, cCurr) VALUES ( NULL ,rtdbtimeIn, 1, @x, @y, @z, @a, @b, @c);
END IF;
END;
CREATE PROCEDURE keyence_getPositionIpocBefor(in timestamp BIGINT,in tb_name VARCHAR(50),out result DOUBLE)
BEGIN
SELECT CONCAT("SELECT rtdbtime,ipoc into @rtt,@result from ", tb_name, " where rtdbtime<=",timestamp, " AND interpolated=0 ORDER BY rtdbtime DESC LIMIT 1") INTO @a;
prepare stmt1 from @a;
execute stmt1;
deallocate prepare stmt1;
IF(@rtt>timestamp OR @result=0) then
SET result=NULL;
ELSE
SET result=@result;
END IF;
END;
//
CREATE PROCEDURE keyence_getPositionIpocAfter(in timestamp BIGINT,in tb_name VARCHAR(50),out result DOUBLE)
BEGIN
SELECT CONCAT("SELECT rtdbtime,ipoc into @rtt,@result from ", tb_name, " where rtdbtime>=",timestamp, " AND interpolated=0 ORDER BY rtdbtime LIMIT 1") INTO @a;
prepare stmt1 from @a;
execute stmt1;
deallocate prepare stmt1;
IF(@rtt<timestamp OR @result=0) then
SET result=NULL;
ELSE
SET result=@result;
END IF;
END;
//
DELIMITER ;
Does someone has any idea how can I speed up my code? I think that the functions keyence_getPositionIpocAfter and keyence_getPositionIpocBefor need very much time.
Thanks and regards,
Franz