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 <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
