SQL annotations
Recommended global annotations
Cancel the behavior of global annotations
Recommended method annotations
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
You can take care of several things about SQL statements to promote performance and scalability at the beginning of application development.
-
JDBC roundtrips
- Detect N+1 selects by using @ExpectSelect, @ExpectMaxSelect or @DisableSameSelectTypesWithDifferentParams
- Detect JDBC batching disabled by using @ExpectJdbcBatching
- Detect exactly same selects by using @DisableExactlySameSelects
- Detect N+1 selects by using @ExpectSelect, @ExpectMaxSelect or @DisableSameSelectTypesWithDifferentParams
-
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
-
...
| Parameter | Type | Meaning | Default value |
|---|---|---|---|
| value | int | Number of select statements | 0 |
@ExpectSelect(1)
@Test
public void should_retrieve_all_cars() {
//...
}With this annotation, the test will fail if the number of SELECT statements is greater than expected.
| Parameter | Type | Meaning | Default value |
|---|---|---|---|
| value | int | Maximum number of selects | 0 |
@ExpectMaxSelect(1)
@Test
public void should_retrieve_all_cars() {
//...
}Verifies the number of selected columns.
Why limit the number of selected columns?
| Parameter | Type | Meaning | Default value |
|---|---|---|---|
| value | int | Number of selected columns | 0 |
@ExpectSelectedColumn(3)With this annotation, the test will fail if the number of returned columns is greater than expected.
Why limit the number of selected columns?
| Parameter | Type | Meaning | Default value |
|---|---|---|---|
| value | int | Maximum number of returned columns | 0 |
@ExpectMaxSelectedColumn(5)| Parameter | Type | Meaning | Default value |
|---|---|---|---|
| value | int | Number of insert statements | 0 |
| Parameter | Type | Meaning | Default value |
|---|---|---|---|
| value | int | Number of update statements | 0 |
With this annotation, the test will fail if the number of updated columns is greater than expected.
| Parameter | Type | Meaning | Default value |
|---|---|---|---|
| value | int | Maximum number of updated columns | 0 |
@ExpectMaxUpdatedColumn(5)| Parameter | Type | Meaning | Default value |
|---|---|---|---|
| value | int | Number of delete statements | 0 |
A SqlAnnotationBuilder class is available to easily implement SpecifiableGlobalAnnotations.
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)
);
}
}The class implementing SpecifiableGlobalAnnotations has to be in org.quickperf package.
Verify that SQL statements do not contain LIKE with leading wildcard (% or _).
You can read this article explaining why LIKE with leading wildcard could be a bad idea in term of performance.
A code sending to the database a like with leading wilcard may be fast in a test having a few data but very slow with the data volume of production.
Verify that inserts, deletes and updates are processed in JDBC batches having batchSize elements. You may sometimes think that you are using JDBC batching but in fact not (Paper 1, Paper 2)! Batching of inserts, updates and deletes allows to reduce the number of roundtrips to the database which can dramatically impact application performance. You can decide to batch all inserts, updates, delete. Prior to Hibernate 5.2, batching, when enabled with a hibernate.jdbc.batch_size property stricly positive, was applied to all inserts, updates and deletes (from Hibernate 5.2 it is also possible to override the batch size value for a given session).
| Parameter | Type | Meaning | Default value |
|---|---|---|---|
| batchSize | int | JDBC batch size | - |
batchSize is optional.
A 0 batch size means that JDBC batching is disabled.
@ExpectJdbcBatching(batchSize = 30)With this annotation, the test will fail at least one request exceeds the max expected query execution time.
| Parameter | Type | Meaning | Default value |
|---|---|---|---|
| value | int | Max query execution time value | - |
| unit | java.util.concurrent.TimeUnit | Time unit | - |
Cancel behavior of @DisableExactlySameSelects.
| Parameter | Type | Meaning | Default value |
|---|---|---|---|
| comment | String | Comment why exactly same selects are enabled | - |
Cancel behavior of @DisableSameSelectTypesWithDifferentParams.
| Parameter | Type | Meaning | Default value |
|---|---|---|---|
| comment | String | Comment why exactly same select types with different parameters are enabled | - |
Cancel behavior of @DisableLikeWithLeadingWildcard.
| Parameter | Type | Meaning | Default value |
|---|---|---|---|
| comment | String | Comment why like with leading wildcard is enabled | - |
Indicate disabling of JDBC batching.
- @ExpectSelect
- @ExpectMaxSelect
- @ExpectSelectedColumn
- @ExpectMaxSelectedColumn
- @ExpectInsert
- @ExpectUpdate
- @ExpectMaxUpdatedColumn
- @ExpectDelete
With this annotation the SQL statements are diplayed in the console during the execution of the test method body.
Compared to @DisplaySql, this annotation does not diplay SQL statements before (JUnit 4: @Before, @BeforeClass) and after (JUnit 4: @After, @AfterClass) the execution of the test method body.
With this annotation the SQL statements are diplayed in the console during the test execution.
Compared to @DisplaySqlOfTestMethodBody, this annotation also diplays SQL statements before (JUnit 4: @Before, @BeforeClass) and after (JUnit 4: @After, @AfterClass) the execution of the test method body.
