Performance: Java vs. Database

Question

Hi folks. When programming there are decisions to make all the time, like:

• Should I generate my Menu/Buttons/Navigation dynamically from a db entry or static via code
• Should I count entries of a list by Java or firing a DB query ..or maybe you find more of those DB vs Javacode questions.

(I hope that question is not too common:)

What should your DB do and what excercises are better done by your Java-code, regarding performance issues in web applications.

Maybe differing between small and huge projects is reasonable

Answer 1

Database is slow. Java code is relatively very fast. Cache everything pulled from the database in memory if possible, possibly using something like memcache (if relevant; I don't do much Java web code).

Answer 2

Extending Ignacio's answer a bit: the DB typically has a big latency (unless it is physically on the same machine), so you want to hit it as rarely as possible. If you use an ORM like Hibernate, you get caching and lazy loading for free; otherwise you need to take care of these yourself.

Thus it is OK to fetch GUI element data from the DB once - then cache and reuse it locally. And it is better to count entries of a list locally if you already have all the elements. If you need a query anyway, you can try combining the fetches into one. However, if you have a huge list and you want to select relatively few elements from it, it may be preferred to let the DB do the work and return only the selected entries, as opposed to cramming a large amount of data through a slow network connection.

Answer 3

Use the database for changeable data. If the application features being able to change the menu/buttons/navigation on the fly, put them in the database, otherwise, do not. Content management systems often do.

Answer 4

Historically, accessing a database is slow. This is specially true for databases that are accessed over the network (3-tier architecture). That's why you should avoid accessing the database, limiting the number of database calls, and limiting the number of connections to the database (possibly using a connection pool). Typical examples are Oracle, IBM DB2, MS SQL Server. Newer databases are MySQL and PostgreSQL.

There are some newer databases that can run in-memory or embedded in the application. That's much faster. Some typical Java databases in this area are HSQLDB and newer the H2 database. They also support client/server operations, but they are not as mature as the databases mentioned above.

But even in-memory and embedded databases are not as fast as using the collection API.

Answer 5

Your database should act as a container that holds data that needs to be persisted for your application. You need to make the decision depending on the type of data that you are dealing with:

For application configurations (menu items, title bar names, button names, etc) - consider using some sort of properties file. If your application has many users and the configuration will be different for each or an excessive amount of properties, consider using a database to persist.

For model data (Person, Address etc.) - consider using a database as this is important information for your application. Also, you'd benefit from a database here so that you can do reporting and analysis outside of your application.

In general, you should be doing all your computations on the data in the java code. This is the concept of using your application layer for business logic and database layer simply for persistence.

There is one exception to using java to perform all actions on your data - most databases are very efficient at sorting data and should be used in your queries to sort your result set if you require it. Depending on the data set and table implementation, sorting your data on the DB side and putting it into a List in your java application may more efficient than getting your data and then sorting in java.

Lastly, if your require frequent use of data, then consider caching it in java rather than querying from database. As some of the other posts mentioned, creating a db connection, executing a query, parsing results is more expensive than simply accessing in your heap.

Answer 6

Create a model. (Bubbles and Line) and identify the entities that is your core data, create classes using that. This is what you put in your database. (Or, you can do it the other way, create a relational model from your entity model. But that may lead to a crappy model in your program, more often than not.)

Initially, dont bother with caching etc, but I admit that its good to have database performance in mind too when designing the model...

Make a clear model that supports what you are trying to do.

Regarding "configuration" in the databas, consider this: If your configuration can't be changed without also changing code, and redeploying, then its no point. Really. It will just be harder to maintain.

Also read this:
http://thedailywtf.com/Articles/Soft_Coding.aspx

And this:

http://thedailywtf.com/Articles/The-Mythical-Business-Layer.aspx