-
Notifications
You must be signed in to change notification settings - Fork 4
SQL annotations
Take control of SQL requests sent to the database
You can take care of several things about SQL requests to favor performance and scalability at the beginning of application development.
-
Limit JDBC roundtrips
- Detect N+1 selects by using @DisableSameSelectTypesWithDifferentParams, @SelectNumber or @MaxOfSelects
- Detect JDBC batching disabled by using @JdbcBatching
- Detect exactly same selects by using @DisableExactlySameSelects
-
Limit fetched data
- Detect too many selected columns by using @SelectedColumnsNumber or @MaxOfSelectedColumns
Why limit the number of selected columns?
- Detect too many selected columns by using @SelectedColumnsNumber or @MaxOfSelectedColumns
-
Avoid SQL requests having a LIKE pattern starting with a wildcard by using @DisableLikeWithLeadingWildcard
-
Avoid cross join generated by Criteria API by using @DisableCrossJoin
-
...
Do little configuration described in Quick start before using SQL annotations.
Recommended global annotations
Disable some global annotations
Recommended method annotations
Configuration with JUnit 4 (without Spring)
Configuration with JUnit 4 and Spring
Add global annotations or method annotations. See the workflow part to see ways to work with SQL annotations.
The SQL annotations automatically detect if you use Hibernate or Spring Boot framewoks. You have no configuration to do. If a SQL property is not respected, the SQL annotations can suggest you solutions to fix it with these frameworks.
Below, propose ways to use SQL annotations during development.
Configure global annotations
Implementation of a new business use case
Add some performance checks to existing database tests
Play with QuickPerf: test your assumptions
Configure once some recommended global annotations. These annotations are applied to every test method.
The idea is to systematically apply some performance checks to avoid some classical performance bottlenecks.
Firstly, we focus our work and attention on the functional behavior. The goal is to have something working without worrying about performances. We try to do one thing at a time. After, we check some performance properties.
-
Write a test describing and verifying the functional behavior
-
Annotate this test with @DisableQuickPerf or @FunctionalIteration to disable the QuickPerf annotations
So, we disable annotations having global or class scopes.
-
Make the functional behavior working
You can do this applying a TDD approach (Red/Green/Refactor).
- Remove @DisableQuickPerf or @FunctionalIteration to enable QuickPerf annotations
-
Fix or ignore issues reported by global annotations
In some specific cases, you can disable some global annotations. - Possibly add QuickPerf annotation on method to document the code
You can introduce QuickPerf in a project having some database tests.
After the configuration of global annotations, some tests may fail because of some not respected performance properties.
If you may want to progressively fix these failing tests. To do this, you could annotate the failing tests with @DisableGlobalAnnotations with a comment, for example @DisableGlobalAnnotations(comment="Investigate why global annotations are failing"). After that, you could progressively remove the annotation and see why the tests are failing.
A SqlAnnotationBuilder class is available to easily implement SpecifiableAnnotations.
package org.quickperf;
import org.quickperf.config.user.SpecifiableAnnotations;
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 SpecifiableAnnotations {
public Collection<Annotation> specifyAnnotationsAppliedOnEachTest() {
int batchSize = 30; // set the expected batch size
return Arrays.asList( disableSameSelectTypesWithDifferentParams() // can reveal N+1 selects
, disableExactlySameSelects() // can reveal a bad use of Hibernate session
, jdbcBatching(batchSize)
//, disableCrossJoin() // if you use JPA Criteria API
, disableLikeWithLeadingWildcard()
);
}
}
The class implementing SpecifiableAnnotations has to be in org.quickperf package.
Verifies that SQL requests 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.
Verifies 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 | - |
A 0 batch size means that JDBC batching is disabled.
@JdbcBatching(batchSize = 30)
Cross join can be generated by JPA Criteria API and can impact performance.
Cancels behavior of @DisableExactlySameSelects.
Parameter | Type | Meaning | Default value |
---|---|---|---|
comment | String | Comment why exactly same selects are enabled | - |
Cancels behavior of @DisableSameSelectTypesWithDifferentParams.
Parameter | Type | Meaning | Default value |
---|---|---|---|
comment | String | Comment why exactly same select types with different parameters are enabled | - |
Cancels behavior of @DisableCrossJoin.
Parameter | Type | Meaning | Default value |
---|---|---|---|
comment | String | Comment why cross join is enabled | - |
Cancels behavior of @DisableLikeWithLeadingWildcard.
Parameter | Type | Meaning | Default value |
---|---|---|---|
comment | String | Comment why like with leading wildcard is enabled | - |
Indicates disabling of JDBC batching.
Parameter | Type | Meaning | Default value |
---|---|---|---|
value | int | Number of select requests | 0 |
@SelectNumber(1)
@Test
public void should_retrieve_all_cars() {
//...
}
With this annotation, the test will fail if the number of SELECT requests is greater than expected.
Parameter | Type | Meaning | Default value |
---|---|---|---|
value | int | Maximum number of selects | 0 |
@MaxOfSelects(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 |
@SelectedColumnsNumber(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 |
@MaxOfSelectedColumns(5)
Parameter | Type | Meaning | Default value |
---|---|---|---|
value | int | Number of insert requests | 0 |
Parameter | Type | Meaning | Default value |
---|---|---|---|
value | int | Number of update requests | 0 |
Parameter | Type | Meaning | Default value |
---|---|---|---|
value | int | Number of delete requests | 0 |
With this annotation the SQL orders are diplayed in the console during the execution of the test method body.
Compared to @DisplaySql, this annotation does not diplay SQL orders before (JUnit 4: @Before, @BeforeClass) and after (JUnit 4: @After, @AfterClass) the execution of the test method body.
It is not recommended to commit your test with this annotation. Indeed, the displaying of SQL orders would pollute the logs of your continuous integration build and it may slow down your continuous integration build.
With this annotation the SQL orders are diplayed in the console during the test execution.
Compared to @DisplaySqlOfTestMethodBody, this annotation also diplays SQL orders before (JUnit 4: @Before, @BeforeClass) and after (JUnit 4: @After, @AfterClass) the execution of the test method body.
It is not recommended to commit your test with this annotation. Indeed, the displaying of SQL orders would pollute the logs of your continuous integration build and it may slow down your continuous integration build.
π Β Core
π Β JVM
π Β SQL
π Β Scopes
π Β Create an annotation
π Β JUnit 4
π Β JUnit 5
π Β TestNG
π Β Spring
π Β Detect and fix N+1 SELECT
π Β Maven performance
π Β Spring Boot - JUnit 4
π Β Spring Boot - JUnit 5
π Β Micronaut Data - JUnit 5
π Β Micronaut - Spring - JUnit 5
π Β Quarkus - JUnit 5
π Β FAQ
π Β QuickPerf code