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
