Re: Performance of Forms PL/SQL vs Database PL/SQL

[Quoted] I have always applied this policy for deciding where to locate my PL/SQL:

1. If it involves user interaction or needs to call Forms built-ins, then it goes into the Form (or an attached library)
2. If it implements business logic, doesn't need to call Forms built-ins or is focussed on accessing database objects (rather than UI objects) then it goes in the database.

In the best case, all the code that CAN go into the database DOES go into the database. By so doing, your valuable business logic & code resources are now accessible by any front-end tool (VB, SQL*Plus, Delphi, Forms, etc.) as well as batch server programs & Java stored procedures. Any code you put in your Developer forms or libraries is restricted for use only within that environment.

In my mind, this principle is more important than fractional performance improvements. However, I'd always be willing to compromise if my benchmarks showed a radical performance difference that will have *significant* business impact. Somewhat akin to deriving a fully 3NF database design, then purposely denormalizing for performance's sake in specific situations.

Martin Doherty

--
To email me, remove the stutter from martin.doherty_at_oorraaccllee.com

damorgan wrote:



>JAMES FRANCIS DAVIS wrote:


>

>  

>

>>I'm looking for some kind of documentation on this.  In our case, the application server is just as powerful as the DB server.In article <3DECDFAB.F56F1482_at_exesolutions.com>, damorgan <damorgan_at_exesolutions.com> says...>JAMES FRANCIS DAVIS wrote:>>> If we have large PL/SQL functions, is it better to run them in Forms on the application server, or imbed it in the database and call it from the form? I remember when IAS 8i and 9iAS came out, there were some articles on this, but I that was a while ago. If anyone knows where whitepapers, presentations, etc exist on this subject, I would appreciate it.>>Always better to put code into the back-end database when possible.>>If nothing else it makes the forms leaner and faster loading. In almost all cases it makes the application run more quickly as the back-end server is the stronger piece of hardware and, if the code is used by multiple users, will most likely be in the cache when needed.>>Daniel Morgan>

>>    

>>

>

>You'll likely not find documentation as every installation and application is different.

>

>I would suggest you run a few benchmarks.

>

>But I'd rather have my code in the back-end even if everything else is equal. Makes maintenance a lot easier.

>

>Dan Morgan

>

>  

>