 |
|
EnterpriseDB: Prepared Statements
Oracle Tips by
Burleson
|
When
EnterpriseDB runs a DML command, it first must parse the command and
then find the best way to run the command (optimize it). The parse
step breaks it up into its component pieces and checks permissions.
The optimize step finds the best access path. Finally, EnterpriseDB
executes the command and returns the results.
In some
cases, it might be better if EnterpriseDB could do the first two steps
up front and store those results. Then you could execute the query
later without having to wait for those steps. Prepared statements
gives you that ability.
When
would you want to do this? Primarily when you have a query that you
will run many times per day, per hour or per second and that either
does not change or only changes the where clause between calls.
Prepared
statements work with SELECT, INSERT, UPDATE and DELETE.
Prepare
To use a
prepared statement, you must first prepare it. When you prepare a
statement, you give it a name (so you can refer to it later), identify
the data types of any bind variables (bind variables are those items
that will change between calls) and define the query.
In the
query itself, the bind variables are identified by the dollar sign ($)
followed by a number. The first bind variable would be referred to as
$1, the second as $2, etc.
Let's
say we have an application that frequently requests to see all
employees who are in a particular job (that changes between calls) and
who have a salary greater than a certain amount (which changes between
calls).
PREPARE emp_sal_query (text, int) AS
SELECT ename, job, sal
FROM emp
WHERE job = $1
AND sal > $2;
edb=# PREPARE emp_sal_query (text, int) AS
edb-# SELECT ename, job, sal
edb-# FROM emp
edb-# WHERE job = $1
edb-# AND sal > $2;
PREPARE
edb=#
We now
have a prepared statement. We identified two variables that will be
replaced at run time, a text variable and an int variable. In our
query, we associated the job = with $1, the text variable and
associated the sal > with the $2 variable which is the int variable.
Once we
have prepared the statement, EnterpriseDB has parsed it and figured
out its execution path. When we execute it, EnterpriseDB will not
need to re-parse or re-optimize the statement.
Execute
To
execute a prepared statement, we use the EXECUTE command.
EXECUTE emp_sal_query( 'SALESMAN', 1500 );
edb=# EXECUTE emp_sal_query( 'SALESMAN', 1500 );
ename | job | sal
--------+----------+---------
ALLEN | SALESMAN | 1648.00
TURNER | SALESMAN | 1545.00
(2 rows)
edb=#
We can
execute it many times with different values:
EXECUTE emp_sal_query( 'MANAGER', 3000 );
EXECUTE emp_sal_query( 'MANAGER', 2700 );
edb=# EXECUTE emp_sal_query( 'MANAGER', 3000 );
ename | job | sal
-------+---------+---------
JONES | MANAGER | 3064.25
(1 row)
edb=# EXECUTE emp_sal_query( 'MANAGER', 2700 );
ename | job | sal
-------+---------+---------
JONES | MANAGER | 3064.25
BLAKE | MANAGER | 2935.50
(2 rows)
edb=#
That's
all there is to it. If you have an application that runs many queries
over and over throughout the day and just changes variables, you can
create a stored procedure to prepare all of your statements and just
have you application execute those statements.
Using
prepared statements does consume a small amount of memory. If you
want to clean up that memory, you can get rid of the prepared
statement by using the DEALLOCATE command:
DEALLOCATE emp_sal_query;
edb=# DEALLOCATE emp_sal_query;
DEALLOCATE
edb=#
Our
chapter is almost over now. The last topic to be covered is some of
the more common SQL functions.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress. |
