Conference ulysse::rdb_vms_competition

    Sometimes, stored procedures are sold as a performance-enhancing
    feature.  The proper response to such claims is, "Rdb/VMS runs faster,
    even though it doesn't have them.  If they do enhance performance
    as the competitor claims, we have an opportunity to get even further
    out in front in performance."
    
    Sometimes stored procedures are touted as a way to attach the
    database-access operations to the database, so that when the database
    is reorganized, the access operations can be re-optimized to match
    the new structure.  This is in contrast to other systems that freeze
    the database-access operations at the time that you precompile a
    module.  Rdb/VMS doesn't follow either of these models.  Instead,
    the accesses are optimized at run-time.  This sounds costly, but
    in most reasonable applications it turns out to win.

    The STORED PROCEDURE is based on IBM's idea of an access strategy or
    PLAN.  At the time you compile your application, the database system 
    optimizes your query based on the query structure and the database 
    statistics _at_the_time_of_your_compilation_.  This access strategy 
    (aka "procedure" or "plan") is stored (hence "stored procedure") in 
    the database.  At runtime, your application invokes this stored 
    procedure and executes the access plan which was determined a long
    time ago when the database looked entirely different than it does now.
    
    What is the advantage of a stored procedure?  Well, data query
    optimization could be a lengthy process if the optimizer is not 
    especially efficient.  So Ingres and Sybase choose to store these
    procedures.
    
    How do we compete?  Our optimizer is very efficient.  Also, we feel 
    it more appropriate to use real-time statistics (actual table
    cardinality, number of leaves in an index, etc).  Our optimizer 
    uses these statistics combined with rules to determine the best 
    access path.  Since we use rules, it qualifies as "AI".  The proof 
    is in the pudding.  Rdb is faster than Ingres or Sybase.
    
    Don't let the competition intimidate you with their buzz words.
    What really counts is performance, not the mechanism used to 
    obtain the good performance.

Re .2

�    The proof 
�    is in the pudding.  Rdb is faster than Ingres or Sybase.

This doesn't prove that a run-time optimizer is better than compile-time
optimization, or that ours is better than other vendors' optimizers. It does
prove that we do *something* better, and don't we tell our customers that
they should use Rdb on VMS since it's optimized for that environment? Perhaps
with compile-time optimization we could get better performance. Why not have
both? Seriously, there are many applications (and I would argue MOST large
applications) require a stable environment, and in fact the database does
not change that much, certainly not enough for a change in Rdb query
optimization. All queries in this type of shop would have to be checked out
in development and UAT stage and they would definitely have to behave the SAME
way in production. Compile-time optimization may be a boon in these cases.

John.

I go with what the recent replies have said - what matters is the performance
of the database. However, for completeness, it must be mentioned that a number
of text books treat this sort of feature - called a catalog by IBM - as
necessary. One that comes to mind is Chris Date's 'Introduction to Database
Systems'. So you've got to be careful about user perceptions ( e.g. If
respected industry specialists say it it necessary then why doesn't Rdb have it
etc.)

Cheers

Steve

    thanks for all the input..... I think I understand them better.  I am
    trying desperatly to have the requirement demoted to a "desirable"
    feature as opposed to a mandatory.  I will take your advice to heard...
    
    Thanks again for all the help...
    
    Lisa

    Re .3
    
    I agree that it's sometimes nice to avoid optimization at runtime, 
    but I'm not convinced by your argument.  Actually, I'm convinced
    that the database does change _radically_ during production use.
    Imagine an Order/Entry system.  LL Bean takes on average 10,000 
    orders a day.  That means that the cardinality of their Order table
    increases by 10,000 rows per day.  That's 300,000 new rows each 
    month.  Our dynamic optimizer (it's patented, by the way) takes 
    into consideration the number of rows in the table, the number of 
    leaves/levels in a B-tree index, etc.  If you do not reoptimize 
    plans regularly to take into consideration the radically changing 
    database statistics, you will not get dependable performance.
    Chances are, you'll get worsening performance as the tables grow.
    
    DB2 DBAs find it an annoying and tedious task to run STATISTICS
    (DB2 doesn't automatically maintain statistics, a utility must be 
    run to recalculate them) and recompile all DB2 plans on a regular basis.  
    Sometimes the DBAs load exaggerated statistics into the catalog just so
    they won't have to recompile the plans every month or so (it's a feature 
    -- you can feed erroneous information to the optimizer so that it's 
    tuned for a larger database). 
    
    Now if the system had the ability to store the compiled plans and 
    to automatically recompile the plans based on statistical thresholds,
    that would be great.  -- No one's done this yet.

�                       <<< Note 893.6 by BROKE::THOMAS >>>
�                              -< Here's an idea >-

�    Actually, I'm convinced
�    that the database does change _radically_ during production use.
�    Imagine an Order/Entry system.  LL Bean takes on average 10,000 
�    orders a day.  That means that the cardinality of their Order table
�    increases by 10,000 rows per day.  That's 300,000 new rows each 
�    month.  Our dynamic optimizer (it's patented, by the way) takes 

I don't mean that the database won't change in terms of number of rows - of
course that will happen. But if we take your example, then this order table
would hold maybe 6-12 months worth of data before archiving - this translates
to 1.8 - 3.6 million rows. Now if you don't implement indexes to this table
and *ensure* that every access is via the appropriate index then your
performance is going to be lousy. Thus I mean that database access mechanism
won't change in that period - you'll start by using indexed lookup (because
of the table size) and you will continue to use indexed lookup even when the
table size is doubled. Hence optimizing at compile time in this example would
produce exactly the same results as months later when many more rows would have
been added.

John.

    Ahhh, but when you're joining tables, the optimizer chooses which
    table to search first based on cardinality rules, etc.  There are
    times when access strategy changes based on cardinality.  
    
    I still like the idea of stored plans which are automatically 
    recompiled based on changes in statistics.

In a less esoteric vein:  Remember that Sybase, which makes good use of
stored procedures, is always client/server.  Client requests for data always
go, via net, to the server for processing; then the server sends results back
over the net to the client.  True even for a standalone machine running
Sybase; unless the network piece is installed, things just don't run.

So stored procedures make sense here because the client sends just one networked
request calling the procedure, then the server makes one networked delivery
of results.  Combined with the single-server/multi-threaded architecture
and the way Sybase reads a database into memory the first time the thing's
accessed, it's a sensible way for them to boost performance.

To each his own, I guess.