Hi Lafras, I would say: "it depends".
We moved a lot of business logic from our delphi client over to the database, and had, compared to the database size, a lot of procedures (>1500) and Triggers (>2750, many of them holding business logic, but most generated automatically). But I stepped back from solving everything in simply writing a procedure, but much rather have a query (with CTEs or even recursively), because the stuff inside the procedure gets compiled, and optimzed at compile time (this might be an advantage, but can be a disadvantage, if data changes during population of the database). The optimizer can not combine the queries inside the procedure with the stuff, you join to it. Nor can an index be used on the result set, even though the procedure might just return the table contents. Creating a view in some cases is much more efficient. I don't think the number of procedures is a problem, nor do i think, having many small procedures is a problem - it simply depends on what they are doing and what you want to achieve. Thomas Am 18.05.2014 12:16, schrieb [email protected] [firebird-support]: > > > Hi, > > I have been using IB from 1999 then FB from v1, for conventional > applications, recently I started an unconventional experiment (in > database terms) and am uncertain about some of my assumptions. > > > I will state my assumptions as I understand them and would appreciate > comments. > > > > Assumptions: > > 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. > > > 3) 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. > > > 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. > > > > Regards, > > Lafras > > -- 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 [email protected] <mailto:[email protected]> ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn [email protected] <mailto:[email protected]> • 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.
