SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Optimizing storedprocedure !!

    since mysql doesn't support array data type ,i wrote procedure as follow
    which stores output of select query into @variable,later i have use this @variable in if condition and update stmt ,and i also want all the variables
    as output of procedure. but this procedure takes time,more than the normal way.


    IS THERE ANY WAY TO OPTIMIZING STORED PROCEDURE?



    drop procedure test
    //
    create procedure test
    (
    IN mode int ,
    IN a int
    )

    BEGIN

    SET @x=a;

    CASE mode

    WHEN 0 THEN

    PREPARE STMT FROM "
    SELECT SQL_NO_CACHE e.empno,e.ename,e.salary,d.deptname,d.location
    FROM EMP e LEFT JOIN DEPT d ON e.deptno=d.deptno WHERE d.deptno>=3 AND d.location='USA' OR d.location='UK'
    ORDER BY deptno ASC
    LIMIT ?,1 into @empno,@ename,@salary,@deptname,@siteid,@location";
    EXECUTE STMT USING @x;

    WHEN 1 THEN
    select stmt
    WHEN 2 THEN
    select stmt
    WHEN 3 THEN
    select stmt
    END CASE;


    SET @deptname_2 = @deptname;
    SET @location_2 = @location;
    SET @ename_2=@ename;



    IF @deptname_2 IN ('ANALYST','TESTING') AND @location_2 ='USA'
    THEN
    PREPARE STMT FROM "
    UPDATE EMP e
    SET e.deptname = 'DBA '
    WHERE e.ename=?" ;
    EXECUTE STMT USING @ename_2;
    END IF ;


    PREPARE STMT FROM "
    UPDATE ......


    DEALLOCATE PREPARE STMT;

    END;
    //

  2. #2
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,633
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Not sure--what are you trying to optimize?

    I'd also note that MySql does have an array datatype--its just called a table.

  3. #3
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wwb_99 View Post
    Not sure--what are you trying to optimize?

    I'd also note that MySql does have an array datatype--its just called a table.
    MY Requirements are as follows:

    -a stored procedure which has select and update stmts
    -output of select query is stored in to multiple variables
    eg: select empno,deptname from emp,dept where (some condition) into @empno,@deptno

    -then there is IF stmt which matches values in these variables
    and if condition is true then it execute Update stmt
    -and i also want this variables values as output from the procedure

    IN actual case there are so many variables which i could have stored using cursor or in to variables ,by doing this execution time and storage area increases while running the query ,U HAVE BETTER SOLUTION FOR THIS?


Bookmarks

Posting Permissions

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