Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2014
    Posts
    2

    Unanswered: Store Procedure Take Time

    Hello Sir,

    I have one stored procedure in mysql 5.5 and it take time to execute 1 min .

    Please help me out.

    -- ------------------------------------------------------------------------
    DELIMITER $$

    CREATE PROCEDURE `test`(
    IN h char(1)
    )
    BEGIN
    /**
    SELECT NOW();
    **/
    SET @i=1;
    SET @j=1000;
    CREATE Temporary table T (Amount float,Typesd char(1));

    /**ALTER TABLE T ADD index (Typesd);**/
    WHILE (@i<=@j)
    DO
    INSERT INTO T
    SELECT (select @i), (select '+');
    SET @i= @i + 1;

    END while;
    SET @i=1;

    WHILE (@i<=@j)
    DO
    INSERT INTO T
    SELECT (select @i), (select '-');
    SET @i=@i+1;

    END While;
    SET @a=(SELECT SUM(Amount)FROM t WHERE typesd='+');
    SET @b=(SELECT SUM(Amount)FROM t WHERE typesd='+');
    SELECT (select @a)/(select @b);
    DROP table t;

    end
    ---------------------------------------------------------------------------

  2. #2
    Join Date
    Mar 2014
    Posts
    2

    Store Procedure Take Time

    Sir,

    I have one stored procedure in my sql and in this stored procedure , I calculate value for more than 1000 accounts using airthmatic operation .

    I also use temprory table and while loop.

    and it will take more than 3 hours to execute.

    Please suggest how can I optimize. Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •