Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Prepare statements causing slowdown?

    Hi,

    I have a stored procedure that I'm trying to speed up.. I have some prepare / execute statements as a part of it and was wondering if it really is a huge overhead for the procedure to compile the statements each time it is called? If so, is there another way to achieve this.. maybe with a cursor or some other method? Thanks!


    IF selectTable = 'TABLE1' THEN
    SET whereClause = ' WHERE col1 = ? AND col2 = 0 AND DATETIME > ? AND DATETIME <= ?';
    ELSE
    SET whereClause = ' WHERE col1 = ? AND DATETIME > ? AND DATETIME <= ?';
    END IF;

    IF condition = 1 THEN
    SET selectStatement = 'SELECT SUM(val) FROM ' || selectTable || whereClause;
    ELSE
    SET selectStatement = 'SELECT AVG(val) FROM ' || selectTable || whereClause;
    END IF;


    SET insertStatement = 'INSERT INTO ' || insertTable
    || ' (col1, col2, col3, col4, col5, col6, col7, col8)'
    || ' VALUES ('|| CHAR(Id) || ',''' || type || ''',' || CHAR(dId) || ', ?, ?, '''
    || iName || ''', ''' || uName || ''', ''' || mName || ''')';


    PREPARE s1 FROM selectStatement;
    PREPARE s2 FROM insertStatement;


    WHILE (start <= end) DO
    ----------------------------------------
    ----------------------------------------
    ----------------------------------------
    EXECUTE s2 USING param1, param2;
    ----------------------------------------
    ----------------------------------------
    END WHILE;

  2. #2
    Join Date
    Nov 2007
    Posts
    265
    So I changed the above to static SQL statements like this --

    WHILE (start <= end) DO
    BEGIN
    SELECT SUM(val) INTO rval FROM tablename WHERE...
    INSERT INTO SESSION.TMP_TBL (col1, col2,...) VALUES (val1, val2,...);
    END;
    END WHILE;



    I tried to stop/start the db ( to clear the bufferpool) and compare the two procedures but am not getting any clear answer as to which one is faster. I would think that the static SQL solution would be at least slightly faster but the results seem to vary.. and other ideas??

Posting Permissions

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