How to promote performance with SQL annotations?

Quickstart

Guidelines to use the SQL annotations

Testcontainers

Available SQL annotations

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

• Limit JDBC roundtrips

  • Detect N+1 selects by using @ExpectSelect, @ExpectMaxSelect or @DisableSameSelectTypesWithDifferentParamValues
    

  • Detect JDBC batching disabled by using @ExpectJdbcBatching

  • Detect the same selects by using @DisableSameSelects

    Why limit JDBC roundtrips?

• Limit fetched data

  • Detect too many selected columns by using @ExpectSelectedColumn or @ExpectMaxSelectedColumn
    
    Why limit the number of selected columns?

• Reduce SQL queries execution time

  • Spot long queries with @ExpectMaxQueryExecutionTime
  • Avoid SQL statements without bind parameters. Detect them by using @DisableQueriesWithoutBindParameters
  • Avoid SQL statements having a LIKE pattern starting with a wildcard. Identify them by using @DisableLikeWithLeadingWildcard

• Avoid database connection leaks by using @ExpectNoConnectionLeak

• ...

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

👉  Micronaut   Preview/Example       👉 JUnit 5 example

👉  Quarkus   Preview/Example          :point_right: TestNG example

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.

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

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

Configure global annotations

Disable global annotations at the method level

Apply SQL annotations on methods

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:

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

⚠ 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()

        );

    }

}

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:

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.

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:

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.

You can also use @DisplayAppliedAnnotations in debug activity.