Here are four tips, not really super cool or something you never heard and I rather say fundamentals but in practice many programmers just missed these, you may also called this database performance tips but I prefer to keep them as Java because I mostly used this when I access database from Java application.
Java database performance tips 1:
Reduce the number of calls you make to the database server. Believe it or not if you see performance in seconds than in most cases culprit is database access code. since connecting to database requires connections to be prepared, network round trip and processing on database side, its best to avoid database call if you can work with cached value. even if your application has quite dynamic data having a short time cache can save many database round trip which can boost your java application performance by almost 20-50% based on how many calls got reduced. In order to find out database calls just put logging for each db call in DAO layer, even better if you log in and out time which gives you idea which call is taking how much time.
Java database performance tips 2:
Check whether your database has indexed key columns if you are reading from database and your query is taking longer than expected than first thing you should check is whether you have index on columns which you are using for search (in where clause of query). this is most common error programmers make and believe me there is huge difference than querying a database which is indexed and the one which is not. This tip can boost your performance by more than 100% but as I said its mistake now having proper indexes in your tables so don't do that in first place. Another point which is worth noting is that too many indexes slows insert and update operation so be careful with indexes and always go on suitable and practical numbers like having indexes on fields which most often used for searching like id, category, class etc.
Java database performance tips 3:
Use PreparedStatement or Stored Procedure for executing a query. Prepared Statements are much faster than normal Statement object as database can pre-compile them and also cache there query plan. so always
use parametric form of Prepared Statement like "select * from table where id=?" , don't use "select * from table where id='" + id "'" which is still a prepared Statement but not parametrized. you won't get performance benefit of preparestatment by using second form.
Java database performance tips 4:
Use Connection Pool for holding Database Connections. Creating Database connections are slow process in Java and take long time. So if you are creating connection on each request than obviously your response time will be lot more. Instead if you use Connection pools with adequate number of connections based upon your traffic or number of concurrent request to make to database you can minimize this time. Even with Connection pooling few of first request may take little longer to execute till your connection gets created and cached in pool.
These Java database application performance tips are very simple in nature and most of advanced Java developers already employ these while writing production code, but same time I have seen many java programmers which doesn't put so much attention until they found there java application is very slow. So geeks and expert may not get anything new but for beginners this is something worth remembering and applying. You can also use this Java performance tips as code review checklist of what not to do while writing Java applications which uses database in back-end.
That's all on how to improve performance of java programs with database. let me know if you have some other java or database tips which is helpful to boost performance of java database applications.
Comments