Find and fix SQL performance anti-patterns

Proprietary

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.

org.openrewrite.sql.antipattern.SqlAntiPatterns

Optional

markSourceBoolean

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.

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

Install it once from Recipe actions, then run it on every repository under the current directory. New to the Moderne CLI? Set it up.

Recipe list

It runs these recipes, in this order.

  1. Find SELECT * queries
  2. Find COUNT subqueries used as existence checks
  3. Find scalar subqueries in the SELECT list
  4. Find non-sargable predicates
  5. Find LIKE patterns starting with a wildcard
  6. Find NOT IN with a subquery
  7. Find optional filters written as OR parameter IS NULL
  8. Find UPDATE and DELETE statements without a WHERE clause
  9. Find INSERT ... VALUES statements that omit the column list
  10. Find ORDER BY on a random function
  11. Find HAVING conditions that use no aggregate
  12. Find oversized IN lists
  13. Find UNION where UNION ALL may suffice
  14. Find DISTINCT masking join fan-out
  15. Find cartesian joins
  16. Find constant predicates that are always true or always false
  17. Find OFFSET-based pagination
  18. Find correlated aggregate subqueries in WHERE
  19. Replace = NULL and <> NULL with IS NULL and IS NOT NULL
  20. Remove redundant ORDER BY from subqueries
  21. Find row limiters without an ORDER BY