Skip to content

SQL annotations

Jean Bisutti edited this page Jul 21, 2020 Β· 317 revisions

🚩 Table of contents

How to promote performance and scalability with SQL annotations?

Quickstart

Available SQL annotations

Guidelines to use the SQL annotations

How to promote performance and scalability with SQL annotations?

To promote performance and scalability at the beginning of application development, we can

⚠️ Do little configuration described in Quickstart before using SQL annotations.

Quickstart

Add configuration

Check the configuration

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.

Use SQL annotations

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

Available SQL annotations

All the SQL annotations

@ExpectSelect @ExpectMaxSelect
@ExpectSelectedColumn @ExpectMaxSelectedColumn
@ExpectUpdate @ExpectMaxUpdate
@ExpectUpdatedColumn @ExpectMaxUpdatedColumn
@ExpectInsert @ExpectMaxInsert
@ExpectDelete
@DisplaySql @DisplaySqlOfTestMethodBody
@ExpectJdbcBatching @ExpectMaxQueryExecutionTime
@DisableExactlySameSelects @EnableExactlySameSelects
@DisableSameSelectTypesWithDifferentParamValues @EnableSameSelectTypesWithDifferentParamValues
@DisableLikeWithLeadingWildcard @EnableLikeWithLeadingWildcard
@DisableQueriesWithoutBindParameters @EnableQueriesWithoutBindParameters

SELECT statements

Annotation Short description
@ExpectSelect SELECT number
@ExpectMaxSelect Max SELECT number
@ExpectSelectedColumn Selected columns number
@ExpectMaxSelectedColumn Max selected columns number
@DisableExactlySameSelects Disables exactly same SELECT statements
@EnableExactlySameSelects 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

UPDATE statements

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

Debug

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

You can also use @DisplayAppliedAnnotations in debug activity.

Other

Annotation Short description
@ExpectJdbcBatching JDBC batching is enabled
@ExpectMaxQueryExecutionTime Max query execution time
@DisableLikeWithLeadingWildcard Disables like with leading wildcard
@EnableLikeWithLeadingWildcard Enables like with leading wildcard
@DisableQueriesWithoutBindParameters Disables queries without bind variables
@EnableQueriesWithoutBindParameters Enables queries without bind variables

Guidelines to use the SQL annotations

Configure global annotations

Cancel the behavior of global annotations at method level

Apply SQL annotations on methods

Use SQL annotations and TDD to promote performance and scalability

Configure global annotations

Annotations with a global scope, also called global annotations, apply on each test.

Let's suppose that we add QuickPerf to an application having automatic tests. Configuring global annotations can allow us to detect some performance bottlenecks rapidly.

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

A SqlAnnotationBuilder class is available to configure SQL global annotations easily.

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)

        );

    }
}

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

Cancel the behavior of global annotations at method level

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.

Apply SQL annotations on test methods

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

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

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

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

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