I'm responsable for one of the most huge application for the government of my country, this application stores it's data in an oracle db. The application server (Apache Tomcat 4.06) stores 4MB of classes and 6MB of JSPs everything uses only java to access the db, there is no sort of pl/sql and 4 stored procedures, we have a great connection pooling, and a really great system for storing some kinds of data in the application server to avoid many access to the data base. But even with all this stuff I could not avoid the at least 47 access to the data base (each time you visit any page) this costs me something near 3,8 seconds at least (time you need to wait to see any page). The highest time we've encountered was 15 seconds. The application server is installed in a dual xeon 400MHz, 1GRAM. 10 people use the sistem. I'M ASKING FOR HELP, CAN SOMEONE HELP ME? DO YOU THING THAT USING J2EE THINGS WOULD BE BETTER? SHOULD I USE PL/SQL? AM I GOING TO CRASH (THE SYSTEM WILL ENTER PRODUCTION NEXT WEEK)??? ANY SORT OF SUGESTION WILL BE HELPFULL. THANKS, IN ADVANCE!
I'M ASKING FOR HELP, CAN SOMEONE HELP ME? There is no need to raise your vioce. Here are a few ideas to consider:
Use profiler (such as OptimizeIt) to pinpoint the bottlenecks, -- you will be surprised to see where the real problem is
Work with your database administrator to set good db access practices, -- your DBA may know something that you don't know about your database
Set the table indexes to match your "where" clause in your SQL quiries and run db stats on the database server (update the indexes)
Cache the results of database queries, -- there is no need to do the same thing more than once. Use an efficient container (such as a HashMap) to store the cached results
Instead of making 100 trips to the database to retreive the data, make use of the value object design pattern, -- take one trip to the database, retreive all the info you need, and keep it around
Don't ask for the data that you don't need to display on one page: instead of retreiving the 100,000 records all at once, make use of the distributed iterator pattern: get 100 records at a time to display and ask for more if the user requests more (presumably by clicking the "next page" link/button.
Instead of quering "select *", request only the fields that you need.
Upgrade to the newer version of jdbc driver and/or newer version of the database server and compare the performance
Talk to your DBA about the database server caching (in addition to the app-level caching), and configuring the database server table spaces efficiently.
Your application server is probably capable of caching prepared statements. If so, configure that cache to take a full advantage of it.
Use performance-oriented features of JDBC2.0 optional package, such as RowSet and CachedRowSet (instead of ResultSet).
Consider a Facade design pattern: instead of making multiple calls from the client to the server over the network, make one call from the client to the facade on the server, and let the facade communicate to the database, to shorten the network overhead.
First you have to measure where performance is slow. Tools like OptimizeIt help here. I recently worked on an app that spent 90% of its time in SQL calls, so we spent all our time tuning SQL and none tuning java. If we would have tuned the java we would have been wasting time. This is what keeping timings can do for you. I have an easy to use performance monitor available at http://www.jamonapi.com. It can also monitor production apps due to it's low overhead. steve
The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus