Introduction
AI coding assistants can now generate database queries from natural-language requirements. A developer can describe a business question and ask an AI system to produce SQL that retrieves the required data.
That can save development time, especially for repetitive queries.
But generating SQL is different from generating correct SQL.
A query can return the expected result for a small dataset and still have problems when it runs against production-sized data. It may use an inefficient filter, return unnecessary columns, perform an expensive join, ignore an available index, or behave differently as the dataset grows.
This creates an important engineering question:
How does AI-generated SQL compare with SQL written by an experienced developer?
The answer should not be based on a few examples or personal impressions. A useful comparison requires a controlled benchmark that measures correctness, execution time, resource consumption, maintainability, and query behavior.
What Should Be Compared?
The benchmark should compare two query-generation approaches:
Business Requirement
|
+------------------+
| |
v v
Expert-Written SQL AI-Generated SQL
| |
+--------+---------+
|
v
Same Database
|
v
Same Test Dataset
|
v
Measure Results
The important part is keeping the environment consistent.
Both queries should run against:
Otherwise, the benchmark can become difficult to interpret.
Define the Business Requirements First
Do not start the benchmark by asking an AI to generate random SQL.
Start with business requirements.
For example:
Find the 20 most recent completed orders
for a customer within the selected date range.
Return the order ID, date, status, and total.
The requirement should be independent of SQL.
Then create:
Requirement
|
+--> Expert SQL
|
+--> AI SQL
This prevents the benchmark from favoring one implementation simply because the requirement was written around a particular query structure.
Build a Representative Dataset
A benchmark is only useful when the data resembles the workload being measured.
Consider an orders table:
CREATE TABLE Orders
(
Id BIGINT PRIMARY KEY,
CustomerId BIGINT NOT NULL,
Status VARCHAR(30) NOT NULL,
Total DECIMAL(18, 2) NOT NULL,
CreatedAt TIMESTAMP NOT NULL
);
Create an index that represents the application's normal design:
CREATE INDEX IX_Orders_Customer_Status_Created
ON Orders (CustomerId, Status, CreatedAt);
The benchmark should then use a meaningful dataset size.
For example:
Small
1,000 rows
Medium
100,000 rows
Large
1,000,000+ rows
The exact dataset size should reflect the application being tested.
Do not claim that a query is production-ready simply because it performs well against a tiny test table.
Create the Expert-Written Query
Suppose the requirement is to retrieve the 20 most recent completed orders for a customer.
An experienced developer might write:
SELECT
Id,
CreatedAt,
Status,
Total
FROM Orders
WHERE CustomerId = @CustomerId
AND Status = 'Completed'
AND CreatedAt >= @StartDate
AND CreatedAt < @EndDate
ORDER BY CreatedAt DESC
LIMIT 20;
The query is parameterized and returns only the columns required by the application.
The benchmark should record the query exactly as tested.
Generate the AI Query Separately
Now provide the same requirement to the AI system.
For example:
Find the 20 most recent completed orders
for a customer between the supplied start
and end dates.
Return:
- Order ID
- Created date
- Status
- Total
Do not provide the expert-written SQL as part of the generation prompt.
Otherwise, the benchmark is no longer testing independent query generation.
Store the generated SQL exactly as returned.
Validate Correctness Before Performance
Performance should not be measured before correctness.
A fast query that returns the wrong records is not a successful result.
For each query, verify:
Correct Rows
Correct Columns
Correct Filters
Correct Ordering
Correct Aggregation
Correct Null Handling
Correct Boundary Conditions
For example:
Expert Result
|
v
Expected Dataset
AI Result
|
v
Compare
|
+-- Same -> Correct
|
+-- Different -> Investigate
A result comparison should use deterministic test data whenever possible.
Test Edge Cases
A query may appear correct under normal conditions while failing at boundaries.
Include cases such as:
For example, this distinction matters:
CreatedAt >= @StartDate
AND CreatedAt < @EndDate
versus:
CreatedAt BETWEEN @StartDate AND @EndDate
Depending on the data type and requirements, the boundary behavior can be different.
An AI-generated query should be evaluated against the actual requirement rather than judged only by whether it looks reasonable.
Measure Execution Time
Once correctness has been established, measure performance.
A basic result table can contain:
| Metric | Expert SQL | AI SQL |
|---|
| Average execution time | Measure | Measure |
| P95 execution time | Measure | Measure |
| P99 execution time | Measure | Measure |
| Rows returned | Measure | Measure |
| Logical reads | Measure | Measure |
| CPU time | Measure | Measure |
The values should come from actual benchmark runs.
Do not publish fabricated performance percentages.
Warm Up the Database
The first execution may behave differently from subsequent executions because of:
Therefore, separate warm-up from measurement.
Start
|
v
Warm-up Queries
|
v
Discard Results
|
v
Measured Runs
Run both query versions under the same conditions.
Repeat Each Query
One execution is not enough for a reliable comparison.
For example:
Expert SQL
|
+-- Run 1
+-- Run 2
+-- Run 3
+-- ...
AI SQL
|
+-- Run 1
+-- Run 2
+-- Run 3
+-- ...
The number of repetitions should be appropriate for the benchmark environment.
Capture both average behavior and variation.
Compare Execution Plans
Execution time tells you what happened.
The execution plan can help explain why.
For example:
EXPLAIN
SELECT
Id,
CreatedAt,
Status,
Total
FROM Orders
WHERE CustomerId = @CustomerId
AND Status = 'Completed'
ORDER BY CreatedAt DESC
LIMIT 20;
For supported database systems, a runtime-aware plan can provide additional information.
Look for differences such as:
Index scan versus sequential scan
Different join strategies
Sort operations
Large intermediate results
Unexpected row estimates
Excessive data reads
Do not assume that a different execution plan is automatically worse.
The question is whether the resulting behavior is appropriate for the workload.
Measure Resource Consumption
Execution time is only one dimension.
Two queries can have similar latency while consuming different amounts of CPU or memory.
Measure where the database engine provides useful metrics:
CPU
I/O
Logical Reads
Physical Reads
Memory
Rows Processed
Rows Returned
This matters when queries are executed frequently.
A query that takes only a few milliseconds but scans a large amount of data may still become expensive at scale.
Test Different Dataset Sizes
A strong benchmark should examine how queries behave as data volume increases.
For example:
| Dataset | Expert SQL | AI SQL |
|---|
| 1K rows | Measure | Measure |
| 100K rows | Measure | Measure |
| 1M rows | Measure | Measure |
| Larger production-like dataset | Measure | Measure |
The objective is to identify whether one query scales differently.
A query that performs similarly at 1,000 rows may behave very differently at 1,000,000 rows.
Test Different Parameter Values
Query behavior can change depending on parameter values.
For example:
Customer A -> Many orders
Customer B -> Few orders
Customer C -> No orders
Test all three types.
This can expose problems related to:
Do not benchmark only the easiest parameter value.
Compare Aggregation Queries
AI-generated queries should also be tested on aggregations.
For example:
SELECT
CustomerId,
COUNT(*) AS OrderCount,
SUM(Total) AS TotalValue
FROM Orders
WHERE CreatedAt >= @StartDate
AND CreatedAt < @EndDate
GROUP BY CustomerId;
Compare the AI-generated version against the expert version for:
Correct grouping
Correct filtering
Null handling
Numeric precision
Execution plan
Resource consumption
Aggregation queries can reveal problems that simple lookups do not.
Compare Join Queries
Joins are another important test category.
Suppose the database contains:
Customers
|
+-- Orders
|
+-- OrderItems
A query might be:
SELECT
c.Id,
c.Name,
o.Id AS OrderId,
o.Total
FROM Customers c
JOIN Orders o
ON o.CustomerId = c.Id
WHERE o.Status = 'Completed';
The AI-generated version should be evaluated for:
Correct join conditions
Duplicate rows
Filtering
Join order
Unnecessary tables
Returned columns
A syntactically valid join can still produce incorrect business results.
Test Pagination
Pagination is a particularly useful benchmark category.
An AI system might generate:
SELECT *
FROM Orders
ORDER BY CreatedAt DESC
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY;
The expert query might use a different strategy depending on the database and workload.
Test:
Page 1
Page 10
Page 100
Page 1000
Large offsets can behave differently from early pages.
The benchmark should measure this instead of testing only the first page.
Evaluate SQL Maintainability
Performance is not the only concern.
An expert-written query may be easier to maintain, but that should be measured through defined criteria rather than assumed.
Review:
For example:
SELECT *
FROM Orders;
may be valid but less precise than:
SELECT
Id,
CustomerId,
Status,
Total,
CreatedAt
FROM Orders;
The benchmark should record such differences as quality observations.
Measure Security Quality
Generated SQL should also be checked for security issues.
Parameterized SQL is preferred:
SELECT
Id,
Status,
Total
FROM Orders
WHERE CustomerId = @CustomerId;
Avoid constructing SQL by concatenating user input:
var sql =
"SELECT * FROM Orders WHERE CustomerId = "
+ customerId;
Even if an AI-generated query appears correct, it should pass the same security review as developer-written SQL.
Build an Automated Evaluation Harness
A benchmark becomes much more useful when it can run repeatedly.
A simple C# model can represent each query test:
public sealed record QueryTestCase(
string Name,
string Requirement,
string ExpertSql,
string AiSql);
The benchmark runner can execute both versions and capture results:
public sealed record QueryBenchmarkResult(
string QueryName,
bool Correct,
double ExecutionTimeMs,
long RowsReturned);
The exact implementation will depend on the database engine and client library.
The important point is to keep the evaluation process consistent.
Create a Query Quality Scorecard
A practical scorecard can contain:
| Category | Expert SQL | AI SQL |
|---|
| Correctness | Pass/Fail | Pass/Fail |
| Security | Pass/Fail | Pass/Fail |
| Execution Time | Measure | Measure |
| Resource Usage | Measure | Measure |
| Scalability | Measure | Measure |
| Maintainability | Review | Review |
| Parameterization | Pass/Fail | Pass/Fail |
Avoid collapsing everything into one score too early.
A query that is slightly slower but substantially easier to maintain may be preferable in some situations.
The decision depends on the application's requirements.
Common Mistakes
Benchmarking Only One Query
One query cannot represent all SQL workloads.
Using a Tiny Dataset
Small datasets can hide scalability problems.
Measuring Only Execution Time
A fast query can still consume excessive resources.
Ignoring Correctness
Performance does not matter if the query returns the wrong data.
Giving the AI the Expert SQL
This contaminates the comparison.
Changing the Database Between Tests
The database schema, indexes, and data should remain consistent.
Testing Only Happy-Path Parameters
Different data distributions can produce different query behavior.
Treating AI SQL as Production-Ready
Generated SQL should go through the same review and testing process as manually written SQL.
Troubleshooting
AI Query Returns Different Results
Compare the queries systematically:
SELECT
|
+-- Filters
+-- Joins
+-- Grouping
+-- Ordering
+-- Pagination
+-- Null Handling
Find the first logical difference rather than comparing the complete queries visually.
AI Query Is Slower
Compare execution plans and resource usage.
Determine whether the problem is caused by:
Missing filter
Poor join
Unnecessary sort
Full scan
Large projection
Inefficient pagination
AI Query Uses More Resources
Check the number of rows processed versus rows returned.
A large difference can indicate that the query is processing much more data than necessary.
Results Are Correct but Query Is Hard to Maintain
Rewrite the query manually or provide stronger generation constraints.
AI-generated SQL should not be accepted solely because automated tests pass.
Best Practices
Define business requirements independently of SQL.
Keep expert and AI queries separate during generation.
Use the same database schema and dataset.
Validate correctness before measuring performance.
Test edge cases and different parameter values.
Warm up the database before measurement.
Run repeated benchmark iterations.
Compare execution plans.
Measure CPU and I/O where available.
Test multiple dataset sizes.
Test joins, aggregations, and pagination.
Check parameterization and security.
Review maintainability.
Automate the evaluation process.
Never treat benchmark results as universal guarantees.
Advantages of AI-Generated SQL
Can accelerate query development.
Helps developers explore unfamiliar schemas.
Can reduce time spent on repetitive queries.
Provides useful starting points for complex SQL.
Can assist developers during database troubleshooting and exploration.
Can be incorporated into development tooling.
Disadvantages of AI-Generated SQL
Generated queries can be logically incorrect.
Query performance can vary significantly.
The model may misunderstand business requirements.
Complex joins can introduce subtle errors.
Generated SQL may not follow project conventions.
Security and parameterization still require validation.
Developers remain responsible for production database behavior.
A Practical Benchmark Architecture
A reusable benchmark can be organized like this:
Business Requirements
|
+-----------+-----------+
| |
v v
Expert SQL Generator AI SQL Generator
| |
+-----------+-----------+
|
v
Validation Layer
|
+---------------+---------------+
| | |
v v v
Correctness Security Syntax
| | |
+---------------+---------------+
|
v
Performance Runner
|
+---------------+---------------+
| | |
v v v
Latency Resource Execution Plan
|
v
Result Analysis
This architecture allows the same benchmark to be reused when:
Example Benchmark Matrix
A comprehensive test suite could look like:
| Query Type | Small Data | Medium Data | Large Data | Edge Cases |
|---|
| Lookup | Test | Test | Test | Test |
| Filtering | Test | Test | Test | Test |
| Aggregation | Test | Test | Test | Test |
| Join | Test | Test | Test | Test |
| Pagination | Test | Test | Test | Test |
| Search | Test | Test | Test | Test |
This produces a much stronger evaluation than comparing two queries once.
Conclusion
AI-generated SQL can be useful for developers, but usefulness should not be confused with production readiness. A query that looks correct and executes successfully may still produce incorrect results, consume unnecessary database resources, or behave poorly as the dataset grows.
The most reliable way to evaluate AI-generated SQL is to compare it against expert-written SQL using the same requirements, schema, indexes, dataset, parameters, and execution environment. Correctness should be checked first, followed by execution time, resource consumption, execution plans, scalability, security, and maintainability.
The purpose of this benchmark is not to prove that AI-generated SQL is better or worse than expert-written SQL. It is to identify where AI-generated queries are reliable, where they need human review, and which types of database workloads require stronger validation before the generated SQL reaches production.