Hi Lafras,

I'm sorry to not dig in that deep again, but your assumptions are
starting to get a bit "complex".

Few thoughts:

- Not only sorting, but narrowing procedure result sets ("where",
"join", "on") is not performed by indexed search.

- The main software architectural argument to write a stored procedure
is the idea to create reusable code, where maintenance tasks focuses on
encapsulated body of a well defined interface of a procedure with a well
defined behavior. If a stored procedure gets created for ervery atomic
task or special behavior, there might be a good reason, but none of the
usual ideas of software development.

Thus, before any further guessing, you shold point out, what's your idea
behind the question is.

Cheers, Thomas

Am 19.05.2014 20:20, schrieb Lafras Henning lafras@...
[firebird-support]:

> 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
>
>
>
>
>
>
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo Groups Links
>
>
>

--
Mit freundlichen Grüßen,

Thomas Beckmann
Diplom-Informatiker


Wielandstraße 14c • 23558 Lübeck
Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604
Mail thomas.beckmann@... <mailto:thomas.beckmann@...>

ASSFINET-Logo

*ASSFINET Dienstleistungs-GmbH*
Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn
info@... <mailto:info@...> • www.assfinet.de
<http://www.assfinet.de/>

Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann
Registergericht Koblenz HRB 23331

Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der
richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist
nicht gestattet.