Skip to content

SQL annotations

Jump to bottom
Jean Bisutti edited this page Sep 29, 2021 · 317 revisions

🚩 Table of contents

How to promote performance with SQL annotations?

Quickstart

Guidelines to use the SQL annotations

Testcontainers

Available SQL annotations

How to promote performance with SQL annotations?

To promote performance at the beginning of application development, you can

Quickstart

Configure QuickPerf for SQL annotations

👉  Spring (JUnit 4, JUnit 5)               👉 JUnit 4

👉  Micronaut   Preview/Example       👉 JUnit 5 example

👉  Quarkus   Preview/Example          :point_right: TestNG example

Check the configuration

You can add an annotation on a test method 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.

Annotation scopes

You can use SQL annotations with a global scope, a class scope, or a method scope.

Automatic framework detection

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

Guidelines to use the SQL annotations

Configure global annotations

Disable global annotations at the method level

Apply SQL annotations on methods

Configure global annotations

You may want to quickly promote performance without adding some SQL annotations on each test method. Or let's suppose that you add QuickPerf to an application having automatic tests.

Annotations with a global scope, also called global annotations, apply to each test. Configuring SQL global annotations can allow you to detect and fix some performance-sensitive properties promptly.

We recommend configuring the following SQL global annotations:

Annotation Short description
@DisableSameSelectTypesWithDifferentParamValues Disables same SELECT statements with different parameter values => N+1 select detection
@ExpectJdbcBatching JDBC batching is enabled
@ExpectMaxQueryExecutionTime Max query execution time
@DisableStatements Disables java.sql.Statement
@DisableQueriesWithoutBindParameters Disables queries without bind variables
@DisableLikeWithLeadingWildcard Disables like with leading wildcard
@ExpectNoConnectionLeak Detects database connection leaks

A SqlAnnotationBuilder class allows you to configure SQL global annotations easily.

🔎 Java code of recommended SQL global annotations

⚠ The class implementing SpecifiableGlobalAnnotations has to be in the org.quickperf package.

package org.quickperf;

import org.quickperf.config.SpecifiableGlobalAnnotations;

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/
                disableSameSelectTypesWithDifferentParamValues()

                // Sometimes, JDBC batching can be disabled with Hibernate:
                // 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()

                // Not relevant with an in-memory database used for testing purpose
              , expectMaxQueryExecutionTime(30)

              , disableStatements()

              , disableQueriesWithoutBindParameters()
              
              , expectNoConnectionLeak()

        );

    }

}

Disable global annotations at the method level

Disable some recommended global annotations

In some specific cases, you may want to disable some global annotations. You can use the following annotations to disable some recommended global annotations for some test methods:

Annotation Short description
@EnableSameSelectTypesWithDifferentParamValues Cancels behavior of @DisableSameSelectTypesWithDifferentParamValues
@ExpectJdbcBatching(batchSize=0) Cancels behavior of @ExpectJdbcBatching
@EnableStatements Cancels behavior of @DisableStatements
@EnableQueriesWithoutBindParameters Cancels behavior of @DisableQueriesWithoutBindParameters
@EnableLikeWithLeadingWildcard Cancels behavior of @DisableLikeWithLeadingWildcard

Temporarily disable the global annotations

Suppose you are developing a new feature, perhaps with the help of Test-Driven Development (TDD). The test may fail because the business property does not work. The test may also fail because of failing performance-related properties. We think it is easier to focus on the business behavior (the functional property), and after that to work on performance-related properties, to do one step at a time. You can temporarily disable QuickPerf global annotations by applying @FunctionalIteration, or @DisableQuickPerf, or @DisableGlobalAnnotations at the method level.

Apply SQL annotations on test methods

In addition to the performance properties verified by the global annotations, you can check others for some test methods.

The annotations added to the test methods can help to document the code. For example, by reading @ExpectSelect(1) annotation applied on a test method, you know that you expect exactly one select sent to the database.

Among all the SQL annotations, we suggest using the following ones on the test methods:

Annotation Short description
@ExpectJdbcQueryExecution JDBC query execution number
@ExpectMaxQueryExecutionTime Max query execution time
@ExpectSelectedColumn Selected columns number
@ExpectUpdatedColumn Updated columns number
@ExpectMaxSelectedColumn Max selected columns number
@ExpectMaxUpdatedColumn Max updated columns
@ExpectSelect SELECT number
@ExpectInsert INSERT number
@ExpectUpdate UPDATE number
@ExpectDelete DELETE number

Testcontainers

Testcontainers is useful to write tests with the same type of database used in production.

QuickPerf can work with Testcontainers.

Several project examples combining Testcontainers and QuickPerf are available here.

Available SQL annotations

All the SQL annotations

@ExpectSelect @ExpectMaxSelect
@ExpectSelectedColumn @ExpectMaxSelectedColumn
@ExpectUpdate @ExpectMaxUpdate
@ExpectUpdatedColumn @ExpectMaxUpdatedColumn
@ExpectInsert @ExpectMaxInsert
@ExpectDelete @ExpectMaxDelete
@DisplaySql @DisplaySqlOfTestMethodBody
@ExpectJdbcBatching @ExpectMaxQueryExecutionTime
@DisableStatements @EnableStatements
@DisableSameSelects @EnableSameSelects
@DisableSameSelectTypesWithDifferentParamValues @EnableSameSelectTypesWithDifferentParamValues
@DisableLikeWithLeadingWildcard @EnableLikeWithLeadingWildcard
@DisableQueriesWithoutBindParameters @EnableQueriesWithoutBindParameters
@ExpectNoConnectionLeak @AnalyzeSql

SELECT statements

Annotation Short description
@ExpectSelect SELECT number
@ExpectMaxSelect Max SELECT number
@ExpectSelectedColumn Selected columns number
@ExpectMaxSelectedColumn Max selected columns number
@DisableSameSelects Disables exactly same SELECT statements
@EnableSameSelects Enables exactly same SELECT statements
@DisableSameSelectTypesWithDifferentParamValues Disables same SELECT statements with different parameter values
@EnableSameSelectTypesWithDifferentParamValues Enables same SELECT statements with different parameter values

INSERT statements

Annotation Short description
@ExpectInsert INSERT number
@ExpectMaxInsert Max INSERT number

DELETE statements

Annotation Short description
@ExpectDelete DELETE number
@ExpectMaxDelete Max DELETE number

UPDATE statements

Annotation Short description
@ExpectUpdate UPDATE number
@ExpectMaxUpdate Max UPDATE number
@ExpectUpdatedColumn Updated columns number
@ExpectMaxUpdatedColumn Max updated columns number

Debug

Annotation Short description
@DisplaySql Displays SQL
@DisplaySqlOfTestMethodBody Displays SQL executed in the test method body

You can also use @DisplayAppliedAnnotations in debug activity.

Other

Annotation Short description
@ExpectJdbcQueryExecution JDBC query execution number
@ExpectMaxJdbcQueryExecution Max JDBC query execution number
@ExpectJdbcBatching JDBC batching is enabled
@ExpectMaxQueryExecutionTime Max query execution time
@DisableStatements Disables java.sql.Statement
@EnableStatements Enablesjava.sql.Statement
@DisableLikeWithLeadingWildcard Disables like with leading wildcard
@EnableLikeWithLeadingWildcard Enables like with leading wildcard
@DisableQueriesWithoutBindParameters Disables queries without bind variables
@EnableQueriesWithoutBindParameters Enables queries without bind variables
@ExpectNoConnectionLeak Detects database connection leaks
@ProfileConnection Profiles connection
@AnalyzeSql Builds an analysis of the SQL executed in the test method

:octocat: Code

Twitter URL

📺

QuickPerf

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