Evaluate a change without optimizing for one execution

An alternative is not validated merely because one execution finishes quickly. It must preserve the result, repeat the benefit with different parameters, and show which aspects improve, which remain unchanged, and which have not yet been tested.

Recover the hypothesis

The previous entry observed 482 logical reads and an Index Scan that examined 100,000 rows to return 136. The provisional explanation pointed to the functions applied to FechaPedido:

WHERE YEAR(p.FechaPedido) = YEAR(@TargetDate)
  AND MONTH(p.FechaPedido) = MONTH(@TargetDate)
  AND DAY(p.FechaPedido) = DAY(@TargetDate)

The alternative expresses the same day as a range:

WHERE p.FechaPedido >= @TargetDate
  AND p.FechaPedido < DATEADD(DAY, 1, @TargetDate)

The prediction is testable: the range must preserve counts and amounts while reducing read work. If either result is absent, the hypothesis loses support.

Choose parameters before measuring

The table contains 730 distinct dates. To prevent one date from deciding the conclusion, the test selects three positions from the available interval:

These dates test whether the behavior repeats at separated points. They do not represent a skewed distribution: all three days contain similar volumes. A workload with empty days, month-end activity, or exceptional concentrations would need additional parameters.

Prove equivalence before comparing performance

Both variants are grouped by date and status. The check preserves counts and amounts separately:

1. 1
   Same result

   The function and range columns match across all six compared groups.

The equality includes times stored within each day. The lower boundary includes the beginning of the date and the upper boundary excludes the beginning of the next day; it does not require FechaPedido to end at midnight.

Separate warm-up from measurement

Each variant runs once per date with a warm-up label. A different series then begins with text that can be identified in the cache:

/* PE29_FUNCTIONS_MEASURED_20261030 */
SELECT
    p.Estado,
    COUNT_BIG(*) AS Orders,
    SUM(p.Total) AS TotalAmount
FROM dbo.PedidoLaboratorio AS p
WHERE YEAR(p.FechaPedido) = YEAR(@TargetDate)
  AND MONTH(p.FechaPedido) = MONTH(@TargetDate)
  AND DAY(p.FechaPedido) = DAY(@TargetDate)
GROUP BY p.Estado
ORDER BY TotalAmount DESC;

/* PE29_RANGE_MEASURED_20261030 */
SELECT
    p.Estado,
    COUNT_BIG(*) AS Orders,
    SUM(p.Total) AS TotalAmount
FROM dbo.PedidoLaboratorio AS p
WHERE p.FechaPedido >= @TargetDate
  AND p.FechaPedido < DATEADD(DAY, 1, @TargetDate)
GROUP BY p.Estado
ORDER BY TotalAmount DESC;

The test alternates both queries five times for each date. This produces 15 measured executions per variant without running one full series before the other.

Alternation does not remove activity on the machine or turn the test into a production benchmark. It avoids a basic ordering difference and shows whether reads remain consistent within each variant.

Compare repeated work

1. 1
   Functions

   All 15 executions record 482 logical reads: minimum, maximum, and average remain at 482.

2. 2
   Range

   The 15 executions record between 2 and 4 logical reads, with an average of 2.67.

The repetition supports the prediction for these three dates. It does not prove that every date query will produce the same difference: index size, distribution, and requested interval can change the work.

Read timings as an observed distribution

1. 1
   Functions

   The observed averages were 30.326 ms of CPU and 30.329 ms elapsed.

2. 2
   Range

   The observed averages were 0.432 ms of CPU and 0.433 ms elapsed.

sys.dm_exec_query_stats accumulated the 15 executions and showed one cached plan for each measured text. This describes the test window. It does not guarantee that the same plan will remain stable after restarts, statistics changes, recompilations, or parameters with a different distribution.

The timings accompany the read evidence, but they are not turned into a commercial improvement percentage. They depend too heavily on this machine and controlled workload to be transferred without another measurement.

Compare the actual plans

The function variant preserves the traversal observed in the hypothesis:

1. 1
   Index Scan

   The operator traverses the date index and applies the predicate to the rows it examines.

The range enables a different access method:

1. 1
   Index Seek

   The boundaries form a range on the FechaPedido key.

Hash Match (Aggregate) and Sort remain because the query still groups and orders. The change addresses row access; it does not remove the work required to form the result.

Verify rows read and one remaining signal

1. 1
   Rows returned

   The operator returns the 136 rows from the final date.

2. 2
   Rows read

   The operator reads those same 136 rows instead of examining all 100,000 rows in the structure.

The representative range plan estimated 16,432 rows and observed 136. The access method improved, but the estimate did not become aligned. A correction does not have to resolve every signal in the plan to be useful, and it should not hide the signals that remain.

In this query the date is declared in a local variable. That form can limit the information available during compilation. The estimate deserves a separate test using the way the application actually sends the parameter before assigning it a definitive cause.

Review effects outside the measurement

• Different parameters: three separated dates preserved results and the read difference; extreme distributions remain untested.
• Writes: the change modifies the predicate and adds no maintenance to INSERT, UPDATE, or DELETE.
• Storage: no additional index was created, so structure space is unchanged.
• Other plans: the test covers this text. It does not show how other queries on FechaPedido will react.
• Blocking: there was no concurrent load. Reading fewer pages may reduce work, but this test did not measure waits or lock duration.
• Stability: one plan per variant was cached during the series; this is not long-term stability evidence.

Decide with the correct scope

The hypothesis receives support within the test: the range preserves results, changes access from Scan to Seek, and reduces reads across all 15 measured executions. It can advance as a candidate modification for this query.

Before applying it in another system, the actual application text, parameters representative of its distribution, concurrency, current statistics, and the complete workload still need to be tested. A conclusion is more useful when it also states what it does not know.

Next reasonable check

The next entry will bring the complete method together in a reproducible business case: context, baseline, plan, metrics, hypothesis, change, new measurement, and a conclusion limited by the evidence.

Related topics

• From a slow query to a testable hypothesis: state the explanation evaluated by this test.
• Build a repeatable baseline: separate conditions, warm-up, and measurement.
• Save and compare two plans: preserve access methods before and after.
• CPU and elapsed time with SET STATISTICS TIME: interpret timings alongside the rest of the evidence.