Moderne licensed

Find and fix SQL performance anti-patterns

Recipe IDorg.openrewrite.sql.antipattern.SqlAntiPatternsArtifactorg.openrewrite.recipe:rewrite-sql

Analyzes SQL found in code and resource files for statically detectable performance anti-patterns such as SELECT *, COUNT(*) used as an existence check, scalar subqueries in the SELECT list, non-sargable predicates, and missing join conditions. Every occurrence is reported to the SqlAntiPatterns data table with a severity and suggested remediation, then fixed in place where a safe rewrite exists or marked in source otherwise.

Composite recipeProprietary

Add to your build

Get a download tokenThis recipe is Moderne licensed, so the token must belong to a Moderne customer. It replaces YOUR_DOWNLOAD_TOKEN below.

~/.gradle/gradle.properties

codeGenomeUsername=you@example.com
codeGenomeToken=YOUR_DOWNLOAD_TOKEN

build.gradle.kts

plugins {
    id("org.openrewrite.rewrite") version("latest.release")
}

rewrite {
    activeRecipe("org.openrewrite.sql.antipattern.SqlAntiPatterns")
}

repositories {
    mavenCentral()
    maven {
        url = uri("https://artifacts.codegenomeproject.org/maven")
        credentials {
            username = providers.gradleProperty("codeGenomeUsername").get()
            password = providers.gradleProperty("codeGenomeToken").get()
        }
    }
}

dependencies {
    rewrite("org.openrewrite.recipe:rewrite-sql:2.17.0")
}

Then run

./gradlew rewriteRun

Usage

This recipe has no required configuration options. You’ll need the Moderne CLI configured before running the command below.

mod run . --recipe org.openrewrite.sql.antipattern.SqlAntiPatterns

If the recipe isn’t available locally, install it with:

mod config recipes jar install org.openrewrite.recipe:rewrite-sql:RELEASE

Options

Name	Type	Description
`markSource`	`Boolean`	Whether to mark each occurrence in source, and apply the fix where the rule remediates. Defaults to `true`; set to `false` to leave source untouched and report only to the data table, as an analysis-only pipeline requires.

Definition

This recipe runs the following recipes in order.

Find SELECT * queriesorg.openrewrite.sql.antipattern.FindSelectStar
Find COUNT subqueries used as existence checksorg.openrewrite.sql.antipattern.FindCountAsExistenceCheck
Find scalar subqueries in the SELECT listorg.openrewrite.sql.antipattern.FindScalarSubqueryInSelect
Find non-sargable predicatesorg.openrewrite.sql.antipattern.FindNonSargablePredicate
Find LIKE patterns starting with a wildcardorg.openrewrite.sql.antipattern.FindLeadingWildcardLike
Find NOT IN with a subqueryorg.openrewrite.sql.antipattern.FindNotInSubquery
Find optional filters written as OR parameter IS NULLorg.openrewrite.sql.antipattern.FindOptionalParameterOr
Find UPDATE and DELETE statements without a WHERE clauseorg.openrewrite.sql.antipattern.FindDmlWithoutWhere
Find INSERT ... VALUES statements that omit the column listorg.openrewrite.sql.antipattern.FindInsertWithoutColumns
Find ORDER BY on a random functionorg.openrewrite.sql.antipattern.FindOrderByRandom
Find HAVING conditions that use no aggregateorg.openrewrite.sql.antipattern.FindHavingWithoutAggregate
Find oversized IN listsorg.openrewrite.sql.antipattern.FindOversizedInList
Find UNION where UNION ALL may sufficeorg.openrewrite.sql.antipattern.FindUnionInsteadOfUnionAll
Find DISTINCT masking join fan-outorg.openrewrite.sql.antipattern.FindDistinctWithJoin
Find cartesian joinsorg.openrewrite.sql.antipattern.FindCartesianJoin
Find constant predicates that are always true or always falseorg.openrewrite.sql.antipattern.FindConstantPredicate
Find OFFSET-based paginationorg.openrewrite.sql.antipattern.FindOffsetPagination
Find correlated aggregate subqueries in WHEREorg.openrewrite.sql.antipattern.FindCorrelatedAggregateSubquery
Replace = NULL and <> NULL with IS NULL and IS NOT NULLorg.openrewrite.sql.antipattern.ReplaceNullComparisonWithIsNull
Remove redundant ORDER BY from subqueriesorg.openrewrite.sql.antipattern.RemoveOrderByInSubquery
Find row limiters without an ORDER BYorg.openrewrite.sql.antipattern.FindLimitWithoutOrderBy

Data tables

Structured output this recipe can produce.

SQL anti-patternsSQL statements matching performance anti-pattern rules.org.openrewrite.sql.table.SqlAntiPatterns
Source files that had resultsSource files that were modified by the recipe run.org.openrewrite.table.SourcesFileResults
Source files that had search resultsSearch results that were found during the recipe run.org.openrewrite.table.SearchResults
Source files that errored on a recipeThe details of all errors produced by a recipe run.org.openrewrite.table.SourcesFileErrors
Recipe performanceStatistics used in analyzing the performance of recipes.org.openrewrite.table.RecipeRunStats