Conference ulysse::rdb_vms_competition

    Granted I've not used Rdb since V2.x, but I thought that Rdb always did
    this.
    
    The first time that you execute a query in an image, the query
    optimizer figures out the best solution.  The next time you execute the
    query, the same strategy is used.  (Strategies are not retained across
    image activations.)
    
    This allowed (allows?) Rdb to adapt to changing database conditions
    (new indexes, changes in cardinality, etc...) without requiring
    programmer or DBA intervention.
    
    If you only "solve" the query at compile time, as some databases do,
    then the only way to adapt to changes in the DB is to recompile all of
    your programs. (Ignoring of course, the issue of knowing when to
    recompile...)  (Of course, some databases apparently "solve" the query
    based on how you phrase it, not based on the database.  From other
    Notes that I've read, Ingres is not one of these.)
    
    If you solve the query every time you execute it, you can have a lot of
    overhead when a program executes the same query multiple times, such as
    in your example.
    
    I hope that someone can confirm/indicate what Rdb V4.x does.

    I believe your customer is refering to 'stored procedures'. RDB does
    not offer that functionality, but I don't think it's a big problem. I
    don't really see the advantage that stored procedures would provide.
    
    Below is a discussion about stored procedures extracted from RDB_WISH
    notes conference.
    
    I hope this helps.
    
    Piotr
    
               <<< NOVA::DVD12:[NOTES$LIBRARY]RDB_WISH.NOTE;1 >>>
                      -< Rdb/VMS Wishes and Suggestions >-
================================================================================
Note 294.0                      Stored Procedures                      3 replies
SCAACT::HARRISON "Bo knows Rdb"                       4 lines  16-APR-1991 16:36
--------------------------------------------------------------------------------
Stored Procedures would be really nice.  INGRES and SYBASE have these and in
competitive situations this is one feature we can't match.

Don
================================================================================
Note 294.1                      Stored Procedures                         1 of 3
NOVA::SMITHI "and keep asking questions..."          10 lines  16-APR-1991 18:51
                           -< justification please >-           
--------------------------------------------------------------------------------
Why do you think we need them?

The Rdb/VMS dynamic optimizer is something they can't match either.  Our
benchmarks look pretty good against these competitors...

What do you think Stored Procedures would buy us?

Thanks,

	Ian
================================================================================
Note 294.2                      Stored Procedures                         2 of 3
SCAACT::HARRISON "Bo knows Rdb"                      17 lines  18-APR-1991 10:23
                      -< Eliminating a compiled request? >-
--------------------------------------------------------------------------------
For static databases, eliminating a compiled request the first time would be
a big advantage over the current method.  Obviously, for non-static databases,
stored procedures would not be very appealing.  Without a doubt, the dynamic
optimizer is a big advantage for us in most cases.

Another plus for stored procedures is the ability to call a procedure that
has already been written beforehand and is stored within the database.

Customers bring up the question about stored procedures frequently.  My response
usually brings up our SQL module language capability, but this is not suitable
for some customers. If I am missing something, please let me know and help me 
to better position ourselves against the competition regarding stored 
procedures.

Thanks,

Don
================================================================================
Note 294.3                      Stored Procedures                         3 of 3
COOKIE::BERENSON "and who will protect us *from* th" 22 lines  22-APR-1991 12:50
                  -< Security, not performance, is the issue >-
--------------------------------------------------------------------------------
What I always see as the real big advantage of stored procedures is
SECURITY.  You can give the procedure access that you would never give a
user.  The procedure itself is what the user is authorized to call.  You
can thus control access to a much greater degree than is possible today.

Performance wise, I believe that most people would be dissapointed by
the small gain from stored procedures.  To gain, the situation has to be
a static query that is infrequently accessed within an attach.  In this
situation, a stored procedure could yield some performance gains.  But,
of the query is frequently accessed within the attach and/or the attach
is of very long duration (e.g., ACMS Server) then the performance gain
just won't be there (and the extra housekeeping would hurt performance
at certain points).

If you just wanted a performance boost, a better answer might be a cache
of compiled queries.

But, personally, I'd like to see stored procedures at some point for
their security benefits.  Then, whatever performance we get out of it is
gravy.

Hal
    
    

    Another advantage that I've heard some customer claim is "known
    performance".  Specifically, once a query has been compiled, you can
    know how the data is retrieved and get a feel for what kind of
    performance to expect.  When your query is compiled the first time it
    is requested during an image activation, there is no guarantee that the
    same solution will be selected.  Just because the optimizer chooses to
    use an index one time, doesn't mean it won't walk the 2,000,000 block
    table the next time.
    
    
    In reality, the solution Rdb determines for a query almost never
    changes unless there is a drastic change in the number of records or a
    change the indices available.   However, this uncertainty scares some
    DBAs, especially ones who are used to CODASYL-style database (where the
    optimal path to a record is chosen by the programmer.)
    
    
    I don't see this as necessarily being a good reason for wanting stored
    procedures, but mention it only because customers have mentioned it to
    me.  If a database system had a reputation for an optimizer that
    created wildly different solutions for the same query, then having
    precompiled queries would be very useful.
    

�                <<< Note 943.3 by KBEAR::STENOISH "DBS West" >>>

�    In reality, the solution Rdb determines for a query almost never
�    changes unless there is a drastic change in the number of records or a
�    change the indices available. 

Or the Rdb version changes :-).

John.

>    In reality, the solution Rdb determines for a query almost never
>    changes unless there is a drastic change in the number of records or a
>    change the indices available.   However, this uncertainty scares some
>    DBAs, especially ones who are used to CODASYL-style database (where the
>    optimal path to a record is chosen by the programmer.)

	What the programmer hopes is the optimal path to a record!

    Last time I worked with Ingres, the REPEAT QUERY simply duplicated the
    behavior of Rdb.  The performance gain in Ingres is a little greater,
    since Ingres parses embedded SQL at run-time.  All the pre-processor
    does is check basis formal systax and wrap the query string in a
    function call that passes it to the server at run-time.  Errors like
    mispellings of table and variable names are not detected during
    pre-processing and compilation.  As a result, "re-optimizing" a query
    everyt time it's executed can become VERY costly.
    
    -dave

>        <<< BEAGLE::USER06:[NOTES$LIBRARY]RDB_VMS_COMPETITION.NOTE;1 >>>
>                         -< Rdb/VMS against the world >-
>================================================================================
>Note 943.3             INGRES's storing repeat SQL Queries                3 of 6
>KBEAR::STENOISH "DBS West"                           23 lines  27-JUN-1991 17:25
>--------------------------------------------------------------------------------
>    Another advantage that I've heard some customer claim is "known
>    performance".  Specifically, once a query has been compiled, you can
.
.
.

The flip side of this 'advantage' is what happens with stored 
procedures when any DDL operation is performed on any object related 
to this procedure.  For instance if you have a parts relation and have 
5 indices defined against the relation and the relation is comprised 
of 30 columns. (and in Rdb's case possibly constraints, triggers, 
storage maps,...)

Depending on the implementation any change to objects 'owned' by the 
parts relations invalidates stored procedures and requires 
recompilation.  What is interesting (for the customers sake) is not 
that stored procedures are offered, but also how are they 
implemented....what is the granularity of invalidation of the stored 
procedure (relation level or a lower level).  How is invalidation done 
(will the applications get errors at DDL time or runtime...possibly 
none...because the system recognizes the invalidation and recompiles).

Anyways, from an implementation standpoint...stored procedures are not 
interesting what is interesting is how they are controlled.

-Jay