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;

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??