SQL annotations
How to promote performance and scalability with SQL annotations?
Guidelines to use the SQL annotations
To promote performance and scalability at the beginning of application development, we can
-
Limit JDBC roundtrips
-
Detect N+1 selects by using @ExpectSelect, @ExpectMaxSelect or @DisableSameSelectTypesWithDifferentParamValues
-
Detect JDBC batching disabled by using @ExpectJdbcBatching
-
Detect exactly same selects by using @DisableExactlySameSelects
-
-
Limit fetched data
-
Detect too many selected columns by using @ExpectSelectedColumn or @ExpectMaxSelectedColumn
Why limit the number of selected columns?
-
Detect too many selected columns by using @ExpectSelectedColumn or @ExpectMaxSelectedColumn
-
Avoid SQL statements without bind parameters, detect them by using @DisableQueriesWithoutBindParameters
-
Avoid SQL statements having a LIKE pattern starting with a wildcard, identify them by using @DisableLikeWithLeadingWildcard
-
...
To check that the configuration is properly done, you can try to add an annotation on a test method in order to make it fail. For example, add @ExpectSelect(0) on a test method that is supposed to send one or several selects to the database.
You can use SQL annotations with a global scope, a class scope or a method scope.
The SQL annotations automatically detect the presence of Hibernate and Spring frameworks. These annotations can propose solutions to get the expected behavior with these frameworks.
For example, QuickPerf displays the following message when an N+1 select is presumed, and Spring Data JPA is detected:
* With Spring Data JPA, you may fix it by adding
@EntityGraph(attributePaths = { "..." }) on repository method.
https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.entity-graph
| Annotation | Short description |
|---|---|
| @ExpectSelect | SELECT number |
| @ExpectMaxSelect | Max SELECT number |
| @ExpectSelectedColumn | Selected columns number |
| @ExpectMaxSelectedColumn | Max selected columns number |
| @DisableExactlySameSelects | Disables exactly same SELECT statements |
| @EnableExactlySameSelects | Enables exactly same SELECT statements |
| @DisableSameSelectTypesWithDifferentParamValues | Disables same SELECT statements with different parameter values |
| @EnableSameSelectTypesWithDifferentParamValues | Enables same SELECT statements with different parameter values |
| Annotation | Short description |
|---|---|
| @ExpectInsert | INSERT number |
| @ExpectMaxInsert | Max INSERT number |
| Annotation | Short description |
|---|---|
| @ExpectDelete | DELETE number |
| Annotation | Short description |
|---|---|
| @ExpectUpdate | UPDATE number |
| @ExpectMaxUpdate | Max UPDATE number |
| @ExpectMaxUpdatedColumn | Updated columns number |
| @ExpectUpdatedColumn | Max updated columns number |
| Annotation | Short description |
|---|---|
| @DisplaySql | Displays SQL |
| @DisplaySqlOfTestMethodBody | Displays SQL executed in test method body |
You can also use @DisplayAppliedAnnotations in debug activity.
| Annotation | Short description |
|---|---|
| @ExpectJdbcBatching | JDBC batching is enabled |
| @ExpectMaxQueryExecutionTime | Max query execution time |
| @DisableLikeWithLeadingWildcard | Disables like with leading wildcard |
| @EnableLikeWithLeadingWildcard | Enables like with leading wildcard |
| @DisableQueriesWithoutBindParameters | Disables queries without bind variables |
| @EnableQueriesWithoutBindParameters | Enables queries without bind variables |
Cancel the behavior of global annotations at method level
Apply SQL annotations on methods
Use SQL annotations and TDD to promote performance and scalability
Annotations with a global scope, also called global annotations, apply on each test.
Let's suppose that we add QuickPerf to an application having automatic tests. Configuring global annotations can allow us to detect some performance bottlenecks rapidly.
We recommend to configure the following SQL global annotations:
| Annotation | Short description |
|---|---|
| @DisableExactlySameSelects | Disable exactly same SELECT statements |
| @DisableSameSelectTypesWithDifferentParamValues | Disable same SELECT statements with different parameter values |
| @DisableLikeWithLeadingWildcard | Disable like with leading wildcard |
| @ExpectJdbcBatching | JDBC batching is enabled |
| @ExpectMaxQueryExecutionTime | Max query execution time |
A SqlAnnotationBuilder class is available to configure SQL global annotations easily.
package org.quickperf;
import org.quickperf.config.user.SpecifiableGlobalAnnotations;
import org.quickperf.sql.annotation.SqlAnnotationBuilder;
import java.lang.annotation.Annotation;
import java.util.Arrays;
import java.util.Collection;
import static org.quickperf.sql.annotation.SqlAnnotationBuilder.*;
public class QuickPerfConfiguration implements SpecifiableGlobalAnnotations {
public Collection<Annotation> specifyAnnotationsAppliedOnEachTest() {
return Arrays.asList(
// Can reveal some N+1 selects
// https://blog.jooq.org/2017/12/18/the-cost-of-jdbc-server-roundtrips/
disableSameSelectTypesWithDifferentParams()
, // Sometimes, JDBC batching can be disabled:
// https://abramsm.wordpress.com/2008/04/23/hibernate-batch-processing-why-you-may-not-be-using-it-even-if-you-think-you-are/
// https://stackoverflow.com/questions/27697810/hibernate-disabled-insert-batching-when-using-an-identity-identifier
expectJdbcBatching()
, // https://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning
disableLikeWithLeadingWildcard()
, disableExactlySameSelects()
// Not relevant with an in-memory database used for testing purpose
, expectMaxQueryExecutionTime( 30, TimeUnit.MILLISECONDS)
);
}
}⚠ Reminder: The class implementing SpecifiableGlobalAnnotations has to be in org.quickperf package.
In some specific cases, you may want to disable dôme global annotations.
You can use the following annotations to disable the recommended global annotations for some test methods:
| Annotation | Short description |
|---|---|
| @EnableExactlySameSelects | Cancels behavior of @DisableExactlySameSelects |
| @EnableSameSelectTypesWithDifferentParamValues | Cancels behavior of @DisableSameSelectTypesWithDifferentParamValues |
| @EnableLikeWithLeadingWildcard | Cancels behavior of @DisableLikeWithLeadingWildcard |
| @ExpectJdbcBatching(batchSize=0) | Cancels behavior of @ExpectJdbcBatching |
In the case where you are developing a new feature, perhaps with the help of Test-Driven Development (TDD), your test may fail because the business property is unrespected but also because some performance properties checked by global annotations are unrespected. In order to do one step at a time, you can temporarily disable global annotations by applying @FunctionalIteration or @DisableQuickPerf or @DisableGlobalAnnotations at method level.
In addition to the performance properties verified by the global annotations, others can be checked for some test methods.
The annotations added on the test methods can help to document the code. For example, by reading @ExpectSelect(1) annotation applied on a test method, we know that we expect exactly one select sent to the database.
Among all the SQL annotations, we recommend using the following on test methods:
| Annotation | Short description |
|---|---|
| @ExpectSelect | SELECT number |
| @ExpectMaxSelect | Max SELECT number |
| @ExpectSelectedColumn | Selected columns number |
| @ExpectMaxSelectedColumn | Max selected columns number |
| @ExpectInsert | INSERT number |
| @ExpectUpdate | UPDATE number |
| @ExpectMaxUpdatedColumn | Max updated columns |
| @ExpectDelete | DELETE number |
