Optimizing queries and the data access layer of your enterprise application is key to avoiding bottlenecks and performance issues. We can use New Relic as an APM, and this will help us to detect bottlenecks and performance problems using database access graphics. With these graphics, we can find the SQL sentences used by your application, finding delay transactions or blocked tables if we continue to drilldown the information until we can also find the most SQL sentences used and the number of connections managed, as in the following screenshot:
From the app, we can identify the queries most used and check for opportunities to optimize them. We would need indexes or to refactor our code to get better performance. On the other hand, without using an APM or a profiling tool, we can use a number of techniques to improve our SQL and data access layers, such as the following:
- Review SQL sentences: This reviews the SQL sentences executed and optimized one by one via the profiler or the APM, applying indexes, choosing the right column types, and optimizing relationships using native queries when necessary.
-
JDBC batch: This uses prepared statements for batching, and some databases such as Oracle support batching for prepared statements.
-
Connection managing: This reviews the use of the connection pool, and measure and set the correct pool size.
-
Scale up and scale out: This is explained in the Scalability section.
-
Caching: This uses in-memory buffer structures to avoid disk access.
-
Avoid ORM: Object Relational Mapping (ORM) tools are used to treat database tables as Java Objects to persist information. However, in some cases, it is better to use plain SQL statements to avoid unnecessary joins, which lead us to improve the performance of applications and databases at the same time.
In the next section, we will look at how to simulate virtual users in order to create a load test for your applications.