Subject | Stored procedure and execute statemen |
---|---|
Author | paultugwell |
Post date | 2011-11-23T12:16:12Z |
I am using FB 2.5
I am trying to create a stored procedure which has a number of input parameters, each of which may or may not need to be used in the procedure depending on the value passed. The procedure is basically this
CREATE PROCEDURE XX
A VARCHAR(8),
B INTEGER,
C INTEGER,
D CHAR(1)
)
RETURNS (
return_variables
) AS
internal_variables
BEGIN
FOR SELECT fields
FROM tables
WHERE conditions
INTO return_variables
DO BEGIN
statements
SUSPEND;
END
The input parameters may have their values set or not. If the values are set, the WHERE statement needs to take account of this.
For example : A = XXX, B = 0, C = 0, D = empty string the WHERE clause would need to be
WHERE conditions AND FIELD_A = :A
or : A = empty string, B = 99, C = 1, D = empty string
WHERE conditions AND FIELD_B = :B AND FIELD_C = :C
This procedure returns a large number of records and takes a noticable time to run if none of the input parameters is set, but returns very few records if one or more of the parameters is set. Because of this, changing the procedure so that it does not use any input paramters then running the query
SELECT * FROM X WHERE A =...
to get the desired result set is not a sensible option, as the FOR SELECT statement would always have to select all possible records then decide which to return.
I could use FOR EXECUTE STATEMENT and build the query string taking account of the values passed, or I could restructure the query so that it has a seperate FOR SELECT statement for each possible set of input paramters (this does not mean 16 statements as most combinations of input paramters are never used, e.g if A is set, B and C will not be set). I am however wary about using FOR EXECUTE STATEMENT as the instructions for its use warn that it may be slow, so I may get no speed gain over using the SELECT.. WHERE contruct with no input parameters.
My question is, which of these 2 methods would be the most efficient in terms of the speed with which the result set is returned?
I am trying to create a stored procedure which has a number of input parameters, each of which may or may not need to be used in the procedure depending on the value passed. The procedure is basically this
CREATE PROCEDURE XX
A VARCHAR(8),
B INTEGER,
C INTEGER,
D CHAR(1)
)
RETURNS (
return_variables
) AS
internal_variables
BEGIN
FOR SELECT fields
FROM tables
WHERE conditions
INTO return_variables
DO BEGIN
statements
SUSPEND;
END
The input parameters may have their values set or not. If the values are set, the WHERE statement needs to take account of this.
For example : A = XXX, B = 0, C = 0, D = empty string the WHERE clause would need to be
WHERE conditions AND FIELD_A = :A
or : A = empty string, B = 99, C = 1, D = empty string
WHERE conditions AND FIELD_B = :B AND FIELD_C = :C
This procedure returns a large number of records and takes a noticable time to run if none of the input parameters is set, but returns very few records if one or more of the parameters is set. Because of this, changing the procedure so that it does not use any input paramters then running the query
SELECT * FROM X WHERE A =...
to get the desired result set is not a sensible option, as the FOR SELECT statement would always have to select all possible records then decide which to return.
I could use FOR EXECUTE STATEMENT and build the query string taking account of the values passed, or I could restructure the query so that it has a seperate FOR SELECT statement for each possible set of input paramters (this does not mean 16 statements as most combinations of input paramters are never used, e.g if A is set, B and C will not be set). I am however wary about using FOR EXECUTE STATEMENT as the instructions for its use warn that it may be slow, so I may get no speed gain over using the SELECT.. WHERE contruct with no input parameters.
My question is, which of these 2 methods would be the most efficient in terms of the speed with which the result set is returned?