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 lafras@... [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 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.