Skip to content

SQL annotations

Jean Bisutti edited this page Jun 5, 2019 · 317 revisions

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.

Do little configuration described in Quick start before using SQL annotations.

Outline

Quick start

Worflow with SQL annotations

Recommended global annotations

Disable some global annotations

Recommended method annotations

Debug annotations

Quick start

Configuration with JUnit 4 (without Spring)

Configuration with JUnit 4 and Spring

Use 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.

Worflow with SQL annotations

Recommended global annotations

Configure recommended global annotations

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 some 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.

@DisableExactlySameSelects

@DisableSameSelectTypesWithDifferentParams

@DisableLikeWithLeadingWildcard

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.

@JdbcBatching

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).

Parameters

Parameter Type Meaning Default value
batchSize int JDBC batch size -

A 0 batch size means that JDBC batching is disabled.

Example

    @JdbcBatching(batchSize = 30)

@DisableCrossJoin

Cross join can be generated by JPA Criteria API and can impact performance.

Disable some global annotations

@EnableExactlySameSelects

Cancels behavior of @DisableExactlySameSelects.

Parameters

Parameter Type Meaning Default value
comment String Comment why exactly same selects are enabled -

@EnableSameSelectTypesWithDifferentParams

Cancels behavior of @DisableSameSelectTypesWithDifferentParams.

Parameters

Parameter Type Meaning Default value
comment String Comment why exactly same select types with different parameters are enabled -

@EnableCrossJoin

Cancels behavior of @DisableCrossJoin.

Parameters

Parameter Type Meaning Default value
comment String Comment why cross join is enabled -

@EnableLikeWithLeadingWildcard

Cancels behavior of @DisableLikeWithLeadingWildcard.

Parameters

Parameter Type Meaning Default value
comment String Comment why like with leading wildcard is enabled -

@JdbcBatching(batchSize=0)

Indicates disabling of JDBC batching.

Recommended method annotations

@SelectNumber

Parameters

Parameter Type Meaning Default value
value int Number of select requests 0

Example

    @SelectNumber(1)
    @Test
    public void should_retrieve_all_cars() {	
     //...
    }

@MaxOfSelects

With this annotation, the test will fail if the number of SELECT requests is greater than expected.

Parameters

Parameter Type Meaning Default value
value int Maximum number of selects 0

Example

    @MaxOfSelects(1)
    @Test
    public void should_retrieve_all_cars() {	
     //...
    }

@SelectedColumnsNumber

Verifies the number of selected columns.

Why limit the number of selected columns?

Parameters

Parameter Type Meaning Default value
value int Number of selected columns 0

Example

    @SelectedColumnsNumber(3)

@MaxOfSelectedColumns

With this annotation, the test will fail if the number of returned columns is greater than expected.

Why limit the number of selected columns?

Parameters

Parameter Type Meaning Default value
value int Maximum number of returned columns 0

Example

    @MaxOfSelectedColumns(5)

@InsertNumber

Parameters

Parameter Type Meaning Default value
value int Number of insert requests 0

@UpdateNumber

Parameters

Parameter Type Meaning Default value
value int Number of update requests 0

@DeleteNumber

Parameter Type Meaning Default value
value int Number of delete requests 0

Debug annotations

@DisplaySqlOfTestMethodBody

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.

@DisplaySql

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.

Annotations

πŸ‘‰ Β Core

πŸ‘‰ Β JVM

πŸ‘‰ Β SQL

πŸ‘‰ Β Scopes

πŸ‘‰ Β Create an annotation

Supported frameworks

πŸ‘‰ Β JUnit 4

πŸ‘‰ Β JUnit 5

πŸ‘‰ Β TestNG

πŸ‘‰ Β Spring

How to

πŸ‘‰ Β Detect and fix N+1 SELECT

Project examples

πŸ‘‰ Β Maven performance

πŸ‘‰ Β Spring Boot - JUnit 4

πŸ‘‰ Β Spring Boot - JUnit 5

πŸ‘‰ Β Micronaut Data - JUnit 5

πŸ‘‰ Β Micronaut - Spring - JUnit 5

πŸ‘‰ Β Quarkus - JUnit 5

Miscellaneous

πŸ‘‰ Β FAQ

πŸ‘‰ Β QuickPerf code

Clone this wiki locally