SQL annotations

🚩 Table of contents

How to promote performance and scalability?

Quickstart

Available SQL annotations

Configure global annotations

Cancel the behavior of global annotations at method level

Apply SQL annotations on methods

Use TDD to promote performance and scalability

How to promote performance and scalability?

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

• Limit JDBC roundtrips

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

  • Detect JDBC batching disabled by using @ExpectJdbcBatching

  • Detect exactly same selects by using @DisableExactlySameSelects

    Why limit JDBC roundtrips?

• Limit fetched data

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

• 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

• ...

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

Quickstart

Add configuration

Configuration for Spring (JUnit 4, JUnit 5, TestNG)

Configuration for JUnit 4

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

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