Design question

[Bruce Trimpop]

I'm having a discussion with a co-worker and we're having a difference of opinion. I'd like to get others thoughts.

We have a Web based app, Cold Fusion and JRUN application servers with an Oracle 9i backend. Load will be anywhere from 1000 to 5000 users simultaneously. There is a fair amount of processing of data that needs to occur at times before display to the Browser. Does it make more sense (performance wise) to do the processing with stored procedures or move it into Java and run it on the app server?
db server is a single quad 700 with 4 gig, the app servers are 2 load balanced dual 1600's with 2 gig.

My thought is to have the app server do the processing and just let the db server serve data. Otherwise I think you'll be killing response times by having the db server doing what can be some pretty heavy processing instead of just responding to queries. Doing some limited benchmarking with under 100 users the db server cpu's hitting some pretty high utilization percentages with the processing being done in stored procedures.

I know this is pretty limited info to go on, but any thoughts or comments would be appreciated?

Thanks

[slimdave]

See http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:883929178230 plus the article that it references.

[gandolf989]

Doing processing in Java will cause a lot of network traffic between the database server and the app servers. If I were to create a similar system I would want all of the logic for accessing data to be in PL/SQL and all of the presentation code to be in Java. Java should make a specific request and PL/SQL should provide the data or do the DML on the database.

IMHO, Web apps must be scalable to work properly. If you wrote stored procedures that are slow, then either they need to change or you need to tweak you schema in your database to get better performance. Small inefficiencies in code in this environment can create big problems with scalability.