SQL annotations
Cancel the behavior of global annotations at method level
Apply SQL annotations at method level
Use TDD to implement persistence performance properties
Several things about SQL statements can promote performance and scalability at the beginning of application development.
-
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
-
-
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
-
SQL statements having a LIKE pattern starting with a wildcard 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 if Hibernate or Spring frameworks are used. You don't have any configuration to do. If a SQL property is not respected, the SQL annotations can suggest you solutions to fix it with these frameworks.
For example, the following message is diplayed when a 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 | Disable exactly same SELECT statements |
| @EnableExactlySameSelects | Enable exactly same SELECT statements |
| @DisableSameSelectTypesWithDifferentParamValues | Disable same SELECT statements with different parameter values |
| @EnableSameSelectTypesWithDifferentParamValues | Enable 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 | Display SQL |
| @DisplaySqlOfTestMethodBody | Display 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 | Disable like with leading wildcard |
| @EnableLikeWithLeadingWildcard | Enable like with leading wildcard |
| @DisableCrossJoin | Disable CROSS JOIN queries |
| @EnableCrossJoin | Enable CROSS JOIN queries |
| @DisableQueriesWithoutBindParameters | Disable queries without bind variables |
| @EnableQueriesWithoutBindParameters | Enable queries without bind variables |
Global annotations apply on each test.
Let's suppose that you just add QuickPerf to an application having automatic tests. With global annotations, you can quickly apply some performance checks on the existing tests in order to detect some classical performance bottlenecks.
To apply the global annotations, the test classes have to be annotated with @QuickPerfJUnitRunner or @QuickPerfSpringRunner with JUnit 4 and @QuickPerfTest with JUnit 5. With TestNG, you don't have to add a QuickPerf annotation on the test class. Global annotations can be configured by creating a class implementing SpecifiableGlobalAnnotations. This class has to be in org.quickperf package. A SqlAnnotationBuilder class is available to easily configure SQL global annotations.
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)
);
}
}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 |
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, you can check other performance properties at method level.
In addition, the annotations applied at method level can help you to document your code. By example, by reading @ExpectSelect(1) annotation applied on a test method, you kwow that we expect one select sent to the database.
Among all the SQL annotations, we recommend to use the following at method level:
| 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 |
