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

I have recently done this on a form using the trace files. In our case (times are relative as my memory fades) (forms 6.0, 8.1.6. Yeah, I know...)

Code in a post-query form block trigger: Parsed every time the trigger was fired 60 seconds
Code in a form procedure: Only parsed once Time 30 seconds but frequent writes to disk as the procedure was pased back and forth accross the network Code in a database procedure : Parsed once Time 15 seconds and much less network traffic and fewer writes because only the results are being returned.

The other thing to consider is until 9i, there is a different pl/sql engine in the form than in the database and features are just not available in the forms. I quite agree with Mr Morgan et al., the maintenance is much easier on the database and the procedure/function can be reused in multiple places. I have spent (too) much time fixing the same function in 20 different forms (what is this subclass of which you speak? ARRGH.).

Your mileage may vary.

"damorgan" <damorgan_at_exesolutions.com> wrote in message news:3DED3A28.95C7B89C_at_exesolutions.com... 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 multipl e 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 Received on Wed Dec 04 2002 - 17:23:35 CET