locked
Stored procedure lasts to long when called-Mysql RRS feed

  • Question

  • 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

    • Moved by Naomi N Monday, March 11, 2013 10:04 PM Possibility of an answer here
    Monday, March 11, 2013 8:24 PM

Answers

All replies