Repeated Select vs Normal Select

bhobes@hpsgm2.sgp.hp.com writes:
>
>I have the following situation for Ingres experts.
>Consider the following two situations:
>
>A) A "repeated select" operation is performed on a table.
>   In the "EXEC REPEATED SQL SELECT" query, the table name is
>   **hard coded**.
>
>B) A "select" operation is performed on the same table.
>   But in the "EXEC SQL SELECT" query, the table name is
>   now a **host variable** known to ESQL.  
>
>Will query A be executed faster than B?  One of my
>coworkers thinks so.  
>
>He thinks that dynamic ESQL queries are slower than REPEATED ESQL 
>queries where table names are hard-coded.  
>
>His rationale is that the DB server has to compile an execution plan
>for the dynamic ESQL query EVERY TIME it is executed whereas the execution
>plan for the REPEATED SELECT query is compiled only once and reused later.
>
>If he is right, what is the extent of performance 
>degradation when dynamic ESQL queries are used instead
>of repeated selects ?
>
>					Thanks,
>					Suyog
>
>
Well, why don't you try it out.  If you did, you'd discover that
method A is infinitely faster than method B since method B cannot
be achieved as described.  You cannot use a host variable in the
FROM list of a SELECT statement in embedded SQL.  You can, however,
achieve the result of method B using dynamic SQL, which leads to
a response to the second question.  In general, yes, your colleage
is correct: the REPEATED SELECT will be faster than a dynamic version
because the REPEATED SELECT query plan is stored when the SELECT is
executed the first time.  The parsing and qep generation do not
need to be redone on subsequent executions, so subsequent executions
will run faster.  Dynamic SQL is inherently slower because the
server must reparse and reoptimize each time the query is execute.

However, care should be taken in choosing which queries should be
made REPEATED.  Queries to avoid making REPEATED are those in which
the query plan is subject to change.  Examples of these are queries
that use a variable for the WHERE clause (which embedded SQL does
allow) and queries that operate on differing ranges of data, such
as those with pattern matching or range comparisons (i.e., >= <= etc).
For example, suppose your query looks like:

SELECT blah1.blah1, blah2.blah2 FROM blah1, blah2
WHERE blah1.blah1 >= :var1 AND blah1.blah1 <= :var2
  AND blah1.blah1 = blah2.blah2

The desired query plan could be very different if a few rows match
blah1.blah1 vs. thousands of rows matching blah1.blah1.  With REPEATED,
the query plan generated on the first execution is used for all others.
This could be good or very very very bad.  So the best overall 
recommendation is to avoid REPEATED on these types of queries.

-- 

Steve Caswell           |   (404) 448-7727    |  "The opinions expressed are my
Principal Consultant    |   sfc@tpghq.com     |   own.  They may not be perfect,
The Palmer Group        |   uunet!tpghq!sfc   |   but they're all I've got."
ken@ksads.wpic.pitt.edu (Ken Tew) writes:
Ingres Q & A
To William's Home Page
Email William