Find and fix SQL performance anti-patterns
Recipe ID
org.openrewrite.sql.antipattern.SqlAntiPatternsArtifact
org.openrewrite.recipe:rewrite-sqlAnalyzes 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.
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.SqlAntiPatternsIf the recipe isn’t available locally, install it with:
mod config recipes jar install org.openrewrite.recipe:rewrite-sql:2.14.1Definition
This recipe runs the following recipes in order.
- Find
SELECT *queriesorg.openrewrite.sql.antipattern.FindSelectStar - Find
COUNTsubqueries used as existence checksorg.openrewrite.sql.antipattern.FindCountAsExistenceCheck - Find scalar subqueries in the
SELECTlistorg.openrewrite.sql.antipattern.FindScalarSubqueryInSelect - Find non-sargable predicates
org.openrewrite.sql.antipattern.FindNonSargablePredicate - Find
LIKEpatterns starting with a wildcardorg.openrewrite.sql.antipattern.FindLeadingWildcardLike - Find
NOT INwith a subqueryorg.openrewrite.sql.antipattern.FindNotInSubquery - Find optional filters written as
ORparameterIS NULLorg.openrewrite.sql.antipattern.FindOptionalParameterOr - Find
UPDATEandDELETEstatements without aWHEREclauseorg.openrewrite.sql.antipattern.FindDmlWithoutWhere - Find
INSERT ... VALUESstatements that omit the column listorg.openrewrite.sql.antipattern.FindInsertWithoutColumns - Find
ORDER BYon a random functionorg.openrewrite.sql.antipattern.FindOrderByRandom - Find
HAVINGconditions that use no aggregateorg.openrewrite.sql.antipattern.FindHavingWithoutAggregate - Find oversized
INlistsorg.openrewrite.sql.antipattern.FindOversizedInList - Find
UNIONwhereUNION ALLmay sufficeorg.openrewrite.sql.antipattern.FindUnionInsteadOfUnionAll - Find
DISTINCTmasking join fan-outorg.openrewrite.sql.antipattern.FindDistinctWithJoin - Find cartesian joins
org.openrewrite.sql.antipattern.FindCartesianJoin - Find constant predicates that are always true or always false
org.openrewrite.sql.antipattern.FindConstantPredicate - Find
OFFSET-based paginationorg.openrewrite.sql.antipattern.FindOffsetPagination - Find correlated aggregate subqueries in
WHEREorg.openrewrite.sql.antipattern.FindCorrelatedAggregateSubquery - Replace
= NULLand<> NULLwithIS NULLandIS NOT NULLorg.openrewrite.sql.antipattern.ReplaceNullComparisonWithIsNull - Remove redundant
ORDER BYfrom 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