MySQL Forums
Forum List  »  General

Re: Multiple complex queries
Posted by: Malcolm Rook
Date: December 07, 2009 12:27PM

Hi Rick

I had assumed that the TIMESTAMP type automatically inserted current server values. Am I wrong? I had not used it as the data collection boxes operate on a battery backed supply which will store data for up to a week in the event of power failures or network problems. (Some of the data collection points are difficult to get at. One is on a pole which can only be accessed at low tide!). For this reason data is timestamped on collection, not insertion into the database.

I think the 60x speed up was coincidental as the system is configured such that irrespective of the time period under examination, 360 rows of data are returned. The real speed up came from the use of the stored procedure to put the data in a summary table. The procedure is below

DELIMITER $$

DROP PROCEDURE IF EXISTS `getdata_a0000001` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getdata_a0000001`(IN p_starttime DATETIME, IN p_timespan INT)
BEGIN

DECLARE v_start DATETIME;
DECLARE v_end DATETIME;
DECLARE v_blockend DATETIME;
DECLARE v_RT DATETIME;
DECLARE v_S1 DOUBLE;
DECLARE v_S2 DOUBLE;
DECLARE v_S3 DOUBLE;
DECLARE v_S4 DOUBLE;
DECLARE v_S5 DOUBLE;
DECLARE v_P DOUBLE;
DECLARE v_H DOUBLE;
DECLARE v_R DOUBLE;
DECLARE v_L DOUBLE;
DECLARE v_WS DOUBLE;
DECLARE v_WD DOUBLE;
DECLARE v_GS DOUBLE;
DECLARE v_GD DOUBLE;

TRUNCATE TABLE weatherdata.temp_a0000001;

SET v_start = p_starttime;
SET v_end = DATE_ADD(v_start, INTERVAL p_timespan SECOND);
SET v_blockend = DATE_ADD(v_start, INTERVAL p_timespan / 360 SECOND);

WHILE (v_start < v_end) DO

SELECT MAX(ReadTime), AVG(Temp1), AVG(Temp2), AVG(Temp3), AVG(Temp4),
AVG(Temp5), AVG(Pressure), AVG(Humidity), SUM(Rainfall), AVG(Light),
AVG(WindSpeed), AVG(WindDir), MAX(GustSpeed), AVG(GustDir) INTO v_RT, v_S1,
v_S2, v_S3, v_S4, v_S5, v_P, v_H, v_R, v_L, v_WS, v_WD, v_GS, v_GD FROM
weatherdata.sitedataa0000001 WHERE ReadTime BETWEEN v_start AND v_blockend;

INSERT INTO temp_a0000001 (ReadTime, S1, S2, S3, S4, S5, P, H, R, L, WS, WD,
GS, GD) VALUES(v_blockend, v_S1, v_S2, v_S3, v_S4, v_S5, v_P, v_H, v_R,
v_L, v_WS, v_WD, v_GS, v_GD);

SET v_start = v_blockend;
SET v_blockend = DATE_ADD(v_start, INTERVAL p_timespan / 360 SECOND);

END WHILE;

END $$

DELIMITER ;

I tried doing an INSERT INTO SELECT but ran into problems with winges about variables not existing so I put this to one side for the moment.

The above procedure produced the table and returned its content in 1.9 secs, extracting averages from 1440 rows of data in a base table of >1952000 rows, indexed on the ReadTime field.

Many thanks for you help and interest

Options: ReplyQuote


Subject
Written By
Posted
December 03, 2009 05:14AM
December 03, 2009 09:02AM
December 04, 2009 10:50PM
December 07, 2009 04:16AM
December 07, 2009 10:37AM
Re: Multiple complex queries
December 07, 2009 12:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.