Dynamic list of parameters for execute statement - performance POV ------------------------------------------------------------------
Key: CORE-4813 URL: http://tracker.firebirdsql.org/browse/CORE-4813 Project: Firebird Core Issue Type: Improvement Reporter: Karol Bieniaszewski Will be good to see a way to build dynamic list of parameters for execute statement at procedure run-time now we have only possibility to specify parameters known at procedure creation time like EXECUTE STATEMENT (VAR_SQL) (PARAM_X := aaa, PARAM_Y := ....) but it is impossible to build params list at runtime - which "violates" concept of EXECUTE STATEMENT for dynamically created queries Will be good to see the way to specify dynamic list of parameters build at run-time. The concept is in example pseudo-code SET TERM ^ ; CREATE PROCEDURE MY_PROC_CALC_EXEC_PARAM(ID INTEGER) RETURNS(SUM_AAA INTEGER) AS DECLARE VARIABLE VAR_SQL VARCHAR(1000); DECLARE VARIABLE VAR_BBB INTEGER; DECLARE VARIABLE VAR_NR INTEGER; DECLARE VARIABLE VAR_PARAM_NAME VARCAHR(10); BEGIN VAR_SQL = 'SELECT SUM(AAA) FROM TABLEY WHERE ID=:ID '; VAR_NR = 1; FOR SELECT BBB FROM TABLEZ WHERE ID=:ID INTO :VAR_BBB DO BEGIN /* DO SOME CALCULATION ON VAR_BBB */ VAR_PARAM_NAME = 'ID' || VAR_NR; VAR_SQL = VAR_SQL || ' OR ID=:' || VAR_PARAM_NAME; PARAMS.ByNAME[VAR_PARAM_NAME] := VAR_BBB; <-- here we create param in virtual space "PARAMS" - quite same like NEW.FIELD, OLD.FIELD work - but parameter is created at request not at start of proc /* may be it is also possible for unnamed params - PARAMS.ByPosition[4] 4 treated as name with internal prefix to prevent allocation of parameters form 1 to 9999 if we specify ByPosition[9999] ;-) */ VAR_NR = VAR_NR + 1; END EXECUTE STATEMENT (VAR_SQL) (PARAMS) <----- Here we specify parameters list INTO :SUM_AAA; SUSPEND; END^ SET TERM ; ^ this feature is important from performance POV and server resources POV for queries like this: SELECT (SELECT P.SUM_AAA FROM MY_PROC_CALC_EXEC_PARAM(T.FIELD_ID) P) FROM TABLEX T without parameters, for every record returned from TABLEX, stored procedure is executed and query is prepared prepare distinct queries for every record is time consuming process but with parameters this will be very fast only few distinct prepared queries i compare times for real example where i put in stored proc execute statement with and without parameters and for query with 100000 records i got times: 15 sec with parameters 42 sec plain text passed to execute statement - for every record query was prepared by server which consume time PS. old form without (PARAMS) should work as previously like EXECUTE STATEMENT (VAR_SQL) (ID1 := something, ID2:= something) -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira ------------------------------------------------------------------------------ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel