It’s time for your database to perform

The number one reason for applications not performing the way they can is the database misuse. May be the following few simple steps can go a long way making our J2EE applications perform and scale well

1. Use databases for only that data which you need to persist and not for everything.

2. Model and design your database to third normal form in general but do the de-normalization of the database to cater to specific functional needs

3. Create indexes where necessary but lets not overdo it as it has its own impact while inserting stuff as database might take up lot of time updating them.

4. Learn about the specific datatypes our database offers and use them to maximum as some might perform better then the others

5. Use the features of the database to the full and do not try to generalize everything viz referential integrity constraints, unique constraints, validations, sorting, joins, stored procedures. Further more there might be features very specific to the database we are using and there is very big chance we will keep on using the same database (much more then may be application server)

6. Get the stuff which you need (remember YAGNI) like select only needed columns and not everything

7. Sit with your DBA to take care of your application specific needs (which otherwise he might not care about) and do it more often then once as the application requirements keep on changing and so the way the application interacts with the database

8. Examine the query execution plans for most of the queries to cut down the full table scans and come up with the right columns to index

9. Turn on the show-sql property when using an ORM during development phase and do not use ORM based queries everywhere instead may be using plain jdbc might perform much better under the scenario.

10. Tune the database which might vary with specific application needs and pay attention to how the database handles transactions and concurrency.