MySQL Forums
Forum List  »  Stored Procedures

Stored procedure lasts to long when called-Mysql
Posted by: Daniel Feliciano
Date: March 12, 2013 07:16AM

Hello,
I'm new here and I hope you can help me, I would be very thankful
I'm using command procesor to develop this SP and using Mysql
I've already have a database created
Ok so this is my code
***************************************
DROP PROCEDURE IF EXISTS search;
DELIMITER ;;
create procedure search
(
IN startTime datetime,
IN endTime datetime,
IN searchString varchar(255)
)
BEGIN
DECLARE t int;
SET t = 0;
SET @outvar = 0;
SET @outvar2 = @outvar;
WHILE startTime<=endTime DO
SET @query = CONCAT('SELECT tableName FROM syslogindex WHERE startDate =\'',startTime,'\' INTO @outvar ;');
PREPARE STMT FROM @query;
EXECUTE STMT;

SET @query3 = CONCAT('CREATE TABLE IF NOT EXISTS `',@outvar,'` (ID int(10) unsigned, CustomerID bigint(20), ReceivedAt datetime, DeviceReportedTime datetime, Facility smallint(6), Priority smallint(6), FromHost varchar(60), Message text, NTSeverity int(11), Importance int(11), EventSource varchar(60), EventUser varchar(60), EventCategory int(11), EventID int(11), EventBinaryData text, MaxAvailable int(11), CurrUsage int(11), MinUsage int(11), MaxUsage int(11), InfoUnitID int(11), SysLogTag varchar(60), EventLogType varchar(60), GenericFileName varchar(60), SystemID int(11), processid varchar(60), checksum int(11))');
PREPARE STMT FROM @query3;
EXECUTE STMT;

IF @outvar2 != @outvar THEN
IF t > 0 THEN
SET @query2 = CONCAT(@query2,' union (select * from `',@outvar, '` WHERE Message LIKE
\'',searchString,'\')')
ELSEIF t < 1 THEN
SET @query2 = CONCAT('(select * from `',@outvar, '` WHERE Message LIKE \'',searchString,'\')');
SET t = 1;
END IF;
END IF;

SET @outvar2 = @outvar;
SET startTime = DATE_ADD(startTime,INTERVAL 1 DAY);
END WHILE;

SET @query2 = CONCAT(@query2,';');
PREPARE STMT FROM @query2;
EXECUTE STMT;
END;;
DELIMITER ;
****************************
The function runs well, but the problem is that it takes too long to compile or take all records
For example, when I call the SP:
call search('2013-02-17','2013-02-17 1:00:00','%port%');
those on dates are the parameters,, and they take too long to get all the records even though it's only a brief preriod of time. This shouldn't be happening.
What command should I use in order to stop this function pass the indicated time?
The function keeps runnig over the time, for example it stays until 5:00:00 and more form the statement above

Hope you can help,

Thank you i adavance

Daniel

Options: ReplyQuote


Subject
Views
Written By
Posted
2663
March 13, 2013 08:12AM
1062
March 13, 2013 09:59AM
1121
March 13, 2013 10:04AM
1087
March 13, 2013 10:24AM
1124
March 13, 2013 10:50AM
1045
March 13, 2013 12:15PM
Stored procedure lasts to long when called-Mysql
1534
March 12, 2013 07:16AM


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.