Find oversized IN lists
Recipe ID
org.openrewrite.sql.antipattern.FindOversizedInListArtifact
org.openrewrite.recipe:rewrite-sqlA very long IN list is parsed and planned on every execution, tends to pollute the statement cache, and some databases hard-cap the number of elements it may contain. Loading the values into a temporary table and joining against it scales far better. IN with a subquery is not flagged.
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.FindOversizedInListIf the recipe isn’t available locally, install it with:
mod config recipes jar install org.openrewrite.recipe:rewrite-sql:2.14.1Options
| Name | Type | Description |
|---|---|---|
threshold | Integer | Flag IN lists with more values than this. Defaults to 50 when not set.e.g. 50 |
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