> >When you send a CREATE [OR ALTER] PROCEDURE statement
> >to the server, it gets parsed, syntactical analyzed,
> >optimized and compiled into BLR, like it is done with
> >a view. The optimization plan doesn't get stored with
> >the SP, so the PLAN is generated every time you will
> >use the SP.
> >
> >
> >
> >
> Sorry, but if that is true i can't see how SP or views will have a
> better execution speed than calling a query from a delphi component.

Because your query needs to get parsed, syntactical analyzed
and compiled into BLR every time.

For views, stored procedures and triggers, this happens
only once, namely at creation time (or when altering).

The execution plan you see, when preparing a statement
using a view or stored procedure needs to be generated
every time. IMHO, a view or stored procedure would be
unusable, if the execution plan gets stored within
the database object, namely the best execution plan
at creation time.

Now, think about deploying an empty database to your
customers with views and stored procedure which do
have PLANs stored within the database. The PLAN would
make sense for the amount of data you are deploying,
but you can't foresee how data will grow after 1
month usage. Where a PLAN made sense, it doesn't do
for a query with another amount of data. You would
need to recompile all views, stored procedure, ...
if the PLAN would be embedded, but thank goodness,
that's not the case.

HTH,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database and MS SQL Server
Upscene Productions
http://www.upscene.com