Thanks Thomas,

You make a good point about compile time SP optimisation becoming stale
as the data changes, but this can simply become a maintenance task,
along with monitoring of index efficiency, doing backups, and sweeping.
This should be a proviso to the assumptions.

> But I stepped back....The optimizer can not

combine the queries inside the procedure with the stuff, you join to it.

Writing queries with joins out side the SP would then be the wrong
approach as the combined query (SP and Join) should have been a new
optimised SP, even if was inconvenient to recreate (considering the
existing SP probibly did 99% of the job and all you may want is a bit
more which kills the performance - very frustrating).

You still make a good point though as I presume the same would apply
executing sub procedures from other procedures, so to my assumption 3 is
definitely not true, as it depends if the result set needs to sorted.


I think rereading the assumptions I am trying to express the following
assumption.

Assumption 5) It is better (from a performance point of few - providing
the logarithms are the same and the same plans will be executed) to have
a lot of complex queries on a lot of tables running using a lot of
stored procedures (for slight variations in the algorithm) finally
returning a single result set than to have a high level language
querying the same data with multiple queries and result sets and
manipulating the same data outside of the DB engine and producing the
same result set.

Here I am not just talking about latency between application and server
as the same could apply to embedded server that does not go via the
network,I rather refer to the time it takes on both side to prepare and
package the on the wire protocol and the time to compile the query on
the fly.

(sorry if I rant and rave a bit, but I do not mean to appear argumentative)

Thanks
Lafras


Version 0.2

Assumptions:
a) Presuming recompiling of the Stored procedures are done as the data
profile changes:

1) There is no performance penalty for having many thousands of stored
procedures (other than the storage space, and possibly manageability
with GUI tools like flamerobin that reads the DDL).


2) There is performance gains in making use of stored procedures over
execute blocks, especially when the statements are long and complex.


3a) There is no significant performance penalty in breaking up a large
stored procedures into many sub stored procedures and invoking them from
the original stored procedure provided their result set is used in the
natural return order.


4) SQL statements run as byte code in a “virtual machine” and are just
as as speed efficient(if not more so) at data and sting manipulation
(Casting, concatenation, substing and replace) than other byte coded
languages such as java, python etc.


5a) Conclusion: It is better (from a performance point of few -
providing the logarithms are the same and the same plans will be
executed) to have a lot of complex queries on a lot of tables running
using a lot of stored procedures (for slight variations in the
algorithm), finally returning a single result set; than to have a high
level language querying the same data with multiple queries and result
sets going to and from the database and manipulating the same data
outside of the DB engine and producing the same result set.


end