Re-write T-SQL where clause causing performance issue

Question

I have a query with the below where clause taking lot of time to execute since the underlying table has lot of data in it:

Environment: SQL Server 2022

WHERE clause:

DATEFROMPARTS(dyear, dmonth, dday) 
    BETWEEN DATEADD(m, -2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))  
        AND CAST(CAST(DATEADD(d, -2, GETDATE()) AS DATE) AS DATETIME)

It doesn't use index on dyear, dmonth, dday (all three int columns) currently due to use of function.

How can I rewrite it so that underlying index can be used to make query run faster?

Apologies, but I won't be able to share the execution plan due to security restrictions.

Data storage format is defined by a third party which isn't in our control. For example, it is not an option to add a computed column to the table which could then be indexed.

Martin Smith · Answer 1 · 2023-09-13T12:55:22.690

You could expand the date range out and then do correlated index seeks on each date in the range.

An equality on dyear, dmonth, dday is sargable. Each seek can read exactly the rows for that one day so in aggregate the number of rows read by the seeks can be exactly the number needed with no excessive rows read.

As you have only shown a fragment of the query I don't know what the SELECT list needs to be. Don't use * - ideally it will be columns covered by the composite index on those three columns.

Sample Data

CREATE TABLE #yourtable (dyear int not null, dmonth int not null, dday int not null, index ix (dyear, dmonth, dday))
INSERT #yourtable
SELECT TOP 1000000 CRYPT_GEN_RANDOM(1) + 1970,
                   CRYPT_GEN_RANDOM(1)%12 + 1,
                   CRYPT_GEN_RANDOM(1)%28 + 1
FROM   sys.all_objects o1,
       sys.all_objects o2

Query

SELECT yt.dyear,
       yt.dmonth,
       yt.dday
FROM   generate_series(0, DATEDIFF(DAY, DATETRUNC(MONTH, DATEADD(MONTH, -2, GETDATE())), GETDATE()) - 2)
       CROSS APPLY (VALUES(DATEADD(DAY, value, DATETRUNC(MONTH, DATEADD(MONTH, -2, GETDATE()))))) D(calc_date)
       CROSS APPLY (SELECT *
                    FROM   #yourtable yt
                    WHERE  yt.dday = DAY(calc_date)
                           AND yt.dmonth = month(calc_date)
                           AND yt.dyear = YEAR(calc_date)) yt

NB: I did try and get an index seek plan using the approach in Nenad's answer but I wasn't able to get a plan that didn't either read many additional rows or had a step to remove duplicates.

Number of rows read by the seek is exactly as needed when the predicate is just

WHERE  ( dyear > @StartYear
          OR ( dyear = @StartYear
               AND dmonth > @StartMonth )
          OR ( dyear = @StartYear
               AND dmonth = @StartMonth
               AND dday >= @StartDay ) )

But it didn't seem to combine the start and end conditions very effectively.

I also tried expanding out the conditions.

select *
from #yourtable  with (forceseek)
 WHERE 
((dyear > @StartYear) AND (dyear < @EndYear)) OR
((dyear > @StartYear) AND (dyear = @EndYear AND dmonth < @EndMonth)) OR
((dyear > @StartYear) AND (dyear = @EndYear AND dmonth = @EndMonth AND dday <= @EndDay)) OR
((dyear = @StartYear AND dmonth > @StartMonth) AND (dyear < @EndYear)) OR
((dyear = @StartYear AND dmonth > @StartMonth) AND (dyear = @EndYear AND dmonth < @EndMonth)) OR
((dyear = @StartYear AND dmonth > @StartMonth) AND (dyear = @EndYear AND dmonth = @EndMonth AND dday <= @EndDay)) OR
((dyear = @StartYear AND dmonth = @StartMonth AND dday >= @StartDay) AND (dyear < @EndYear)) OR
((dyear = @StartYear AND dmonth = @StartMonth AND dday >= @StartDay) AND (dyear = @EndYear AND dmonth < @EndMonth)) OR
((dyear = @StartYear AND dmonth = @StartMonth AND dday >= @StartDay) AND (dyear = @EndYear AND dmonth = @EndMonth AND dday <= @EndDay))
OPTION (RECOMPILE);

This does seek exactly the right ranges (able to simplified by the RECOMPILE to just the following)

dyear = 2023 and dmonth = 7 and dday >= 1
dyear = 2023 and dmonth > 7 and dmonth < 9
dyear = 2023 and dmonth = 9 and dday <= 11

but the plan has an annoying distinct sort despite these being disjoint

score 5 · Answer 2 · answered Sep 14 '23 at 06:25

Another approach is to generate disjunct ranges using dynamic SQL:

DECLARE 
    @StartDate date = DATETRUNC(MONTH, DATEADD(MONTH, -2, GETDATE())),
    @EndDate date = DATEADD(DAY, -2, GETDATE());
DECLARE 
    @SQL nvarchar(max) = N'SELECT * FROM #yourtable WHERE 0 = 1';
WHILE @StartDate < @EndDate
BEGIN
    SET @SQL = 
        CONCAT
        (
            @SQL,
            NCHAR(13), NCHAR(10),
            N' OR (dyear = ', YEAR(@StartDate),
            N' AND dmonth = ', MONTH(@StartDate),
            -- First month only if short
            IIF
            (
                DAY(@StartDate) > 1,
                CONCAT(N' AND dday >= ', DAY(@StartDate)),
                N''
            ),
            -- Last month only if short
            IIF
            (
                DATETRUNC(MONTH, @StartDate) = DATETRUNC(MONTH, @EndDate),
                CONCAT(N' AND dday <= ', DAY(@EndDate)),
                N''
            ),
            N')'
        );
-- Next month
SET @StartDate = DATETRUNC(MONTH, DATEADD(MONTH, 1, @StartDate));

END;
PRINT @SQL;
EXECUTE (@SQL);

Today, that script produces:

SELECT * FROM #yourtable WHERE 0 = 1
 OR (dyear = 2023 AND dmonth = 7)
 OR (dyear = 2023 AND dmonth = 8)
 OR (dyear = 2023 AND dmonth = 9 AND dday <= 12)

The execution plan is a single seek operator performing three range seeks:

db<>fiddle

The script will work for any start and end dates.

score 3 · Answer 3 · edited Sep 14 '23 at 06:27

If I understand correctly, the time range is about two months. If the time range is always lesser than a full year, you could leverage this by splitting the query in three parts merged with an UNION ALL. (Using UNION instead of UNION ALL would cause a distinct sort operation, but we can avoid it since we know that the conditions are mutually exclusive).

DECLARE @StartDate DATE = DATEADD(m, -2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0));
DECLARE @EndDate DATE = DATEADD(d, -2, GETDATE());
SELECT 
...
WHERE YEAR(@StartDate) = YEAR (@EndDate) 
      AND dyear = YEAR(@StartDate)
      AND dmonth BETWEEN MONTH(@StartDate) AND MONTH (@EndDate)
      AND DATEFROMPARTS(dyear, dmonth, dday) BETWEEN @StartDate AND @EndDate
UNION ALL
SELECT 
...
WHERE YEAR(@StartDate) < YEAR (@EndDate) 
      AND dyear = YEAR(@StartDate)
      AND dmonth >= MONTH(@StartDate) 
      AND DATEFROMPARTS(dyear, dmonth, dday) >= @StartDate
UNION ALL
SELECT 
...
WHERE YEAR(@StartDate) < YEAR (@EndDate) 
      AND dyear = YEAR(@EndDate)
      AND dmonth <= MONTH(@EndDate) 
      AND DATEFROMPARTS(dyear, dmonth, dday) <= @EndDate

If @StartDate and @EndDate belong to the same year, only the first query will be executed, using the index for the year and month parts (the day part would never be used, since month is constrained by inequalities). The last check filters out the unwanted days.

If @StartDate and @EndDate belong to different (consecutive) years, only the last two queries will be executed, also using the index for the year and month parts.

If the date range could possibly be larger than a full year, the solution could be generalised by adding another subquery which would be executed only in the case of such greater time range:

UNION ALL
SELECT 
...
WHERE YEAR(@StartDate) < YEAR(@EndDate)-1
      AND dyear > YEAR(@StartDate)
      AND dyear < YEAR(@EndDate)

On the other end, if you just need this exact time range (from the beginning of month, two months ago, up to two days ago), you could still use UNION ALL but with more selective conditions, which take advantage of knowing that the first month and the middle one (if exists) will be considered as whole months, while the last one will be selected up to the computed day :

DECLARE @StartDate DATE = DATEADD(m, -2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0));
DECLARE @MiddleDate DATE = DATEADD(m, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0));
DECLARE @EndDate DATE = DATEADD(d, -2, GETDATE());
SELECT 
...
WHERE dyear = YEAR(@StartDate)
      AND dmonth = MONTH(@StartDate)
UNION ALL
SELECT 
...
WHERE MONTH(@MiddleDate) <> MONTH(@EndDate)
      AND dyear = YEAR(@MiddleDate)
      AND dmonth = MONTH(@MiddleDate)
UNION ALL
SELECT 
...
WHERE dyear = YEAR(@EndDate)
      AND dmonth = MONTH(@EndDate) 
      AND dday <= DAY(@EndDate)

score 2 · Answer 4 · edited Sep 13 '23 at 11:58

You can try the "old fashioned" approach and just compare each part in every combination

Extract your @StarDate and @EndDate for better readability:

DECLARE @StarDate DATE = DATEADD(m, -2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0));
DECLARE @EndDate DATE = CAST(DATEADD(d, -2, GETDATE()) AS DATE;

Or even all the parts:

DECLARE @StartYear INT = YEAR(@StarDate);
DECLARE @StartMonth INT = MONTH(@StarDate);
DECLARE @StartDay INT = DAY(@StarDate);
DECLARE @EndYear INT = YEAR(@EndDate);
DECLARE @EndMonth INT = MONTH(@EndDate);
DECLARE @EndDay INT = DAY(@EndDate);

And then compare year to year, month to month, day to day

... WHERE 
    (dyear > @StartYear OR (dyear = @StartYear AND dmonth > @StartMonth) OR (dyear = @StartYear AND dmonth = @StartMonth AND dday >= @StartDay))
    AND
    (dyear < @EndYear OR (dyear = @EndYear AND dmonth < @EndMonth) OR (dyear = @EndYear AND dmonth = @EndMonth AND dday <= @EndDay));

Might need tweaking but it is an idea.

For example, try a FORCESEEK hint on the table and OPTION (RECOMPILE) so the query optimizer can generate a plan for the specific values of the variables.

Related Q & A: Index seek to specific multi-column key, then get some rows in lexicographical order

score 1 · Answer 5 · answered Sep 12 '23 at 14:12

How can I rewrite it so that underlying index can be used to make query run faster?

By fixing the bad schema to store real date values. I say, "fix", because the current schema really is broken and incorrect. This will allow you to index the corrected column, and then you can write the query so it will properly use that index.

score 1 · Answer 6 · answered Sep 12 '23 at 16:10

This is not to suggest that you continue using this design, it's a bad design. Another approach, however, would be to use a calendar table which contains the separate date parts. Then your data engine can do a seek on those rows and perform a JOIN on those:

--Very quick calendar table, make a better one;
CREATE TABLE dbo.Calendar (CalendarDate date
                               CONSTRAINT PK_Calendar PRIMARY KEY,
                           --YOu could  be better off *not* computing there here
                           CalendarYear AS DATEPART(YEAR, CalendarDate),
                           CalendarMonth AS DATEPART(MONTH, CalendarDate),
                           CalendarDay AS DATEPART(DAY, CalendarDate));
GO
INSERT INTO dbo.Calendar (CalendarDate)
SELECT DATEADD(DAY, GS.value, '20220101')
FROM GENERATE_SERIES(0, DATEDIFF(DAY, '20220101', '20231231')) GS;
GO
CREATE TABLE dbo.BadDates (ID int IDENTITY PRIMARY KEY,
                           dyear int,
                           dmonth int,
                           dday int,
                           INDEX IX_BadDates_DateParts (dyear, dmonth, dday));
GO
INSERT INTO dbo.BadDates (dyear,
                      dmonth,
                      dday)
SELECT TOP (300)
       CalendarYear,
       CalendarMonth,
       CalendarDay
FROM dbo.Calendar
ORDER BY NEWID();
GO
DECLARE @SearchDate date = GETDATE();
DECLARE @FromDate date = DATEADD(MONTH, -2, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)),
        @ToDate date = CAST(DATEADD(d, -2, GETDATE()) AS date);
SELECT BD.ID,
       BD.dyear,
       BD.dmonth,
       BD.dday
FROM dbo.Calendar C
    JOIN dbo.BadDates BD ON C.CalendarYear = BD.dyear
                        AND C.CalendarMonth = BD.dmonth
                        AND C.CalendarDay = BD.dday
WHERE C.CalendarDate >= @FromDate
  AND C.CalendarDate <= @ToDate;
GO
DROP TABLE dbo.BadDates;
DROP TABLE dbo.Calendar;

db<>fiddle;

score 0 · Answer 7 · answered Sep 13 '23 at 13:43

You could quickly fix the bad performances of this query bu using an indexed computed column like :

ALTER TABLE ??? ADD COMPLETE_DATE AS DATEFROMPARTS(dyear, dmonth, dday) PERSISTED;

Then, index it :

CREATE INDEX X001 ON ??? ( COMPLETE_DATE );

Then use the COMPLETE_DATE column into your query instead of the expression...

Of course, beacause of your restrictions about data model, you can use an indexed view that does the same and containes only id column and computed column....

score 0 · Answer 8 · answered Sep 14 '23 at 13:29

You may want to look at using a subquery to narrow your result set down to the year range. Then apply the query you provided on top of the results of the year range.

For example, Select * From YourTable Where dyear >= @bottomyear And dyear <= @topyear

That would be your subquery, which you can now apply your main query on top of.

If the issue is large data, sometimes it helps a lot to simply use a sub query or join to simply cut down the total amount of data your main query has to process.

You probably should include your table schema or a mock or it in your question. Would help a lot to see the whole picture of what this table looks like.

score 0 · Answer 9 · answered Sep 14 '23 at 19:46

You could use a common table expression to generate a list of dates and join this to your table:

DECLARE @StartDate Date
DECLARE @EndDate Date
SET @StartDate = DATEADD(m, -2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))  
SET @EndDate =  CAST(CAST(DATEADD(d, -2, GETDATE()) AS DATE) AS DATETIME)
SELECT @StartDate, @EndDate
;WITH Tally AS (
SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS [N]
  FROM dbo.syscolumns tb1,dbo.syscolumns tb2 -- or you could use a large table from your ConfigMgr db if necessary
),
selectedDates AS
(
    SELECT DATEPART(d,DateAdd(d, N-1, @StartDate)) as d,
            DATEPART(m,DateAdd(d, N-1, @StartDate)) as m,
            DATEPART(yy,DateAdd(d, N-1, @StartDate)) as y
    FROM Tally
    WHERE DateAdd(d, N-1, @StartDate) <= @EndDate
)
SELECT *
FROM yourtable
WHERE selectedDates.d = yourTable.dday AND
      selectedDates.m = yourTable.dmonth AND
      selectedDates.y = yourTable.dyear

tpdi · Answer 10 · 2023-12-07T07:36:31.373

For example, it is not an option to add a computed column to the table which could then be indexed.

If it has a unique id, index that, then add the computed column to a table that joins the first on the id.

When you insert new rows into the base table, create corresponding rows in the second with where not exists.

Pseudo-SQL:

Create index pk on original_table(id);
Select id, dateadd(m, -2, start_time), other, computed, columns from original_table
Into companion_table;

Create indexes on companion_table computed columns;

Select a.* from original_table a join companion_table b on (a.id = b.id)
Where some_indexed_computed_column = whatever;

Use companion_table's indices and the join to to find original_tables rows of interest.

When you add rows to original_tabge, re-run the select into with a where not exists a.id = b.id;

Ciarán Mac Lochlainn · Answer 11 · 2023-09-14T10:29:51.297

SQL Server may be recalculating the limits per each record because of the function calls in the WHERE clause. This slows down query execution, and additionally the results can't be cached because the query is nondeterministic due to including GETDATE().

You could save the date in a variable that you reference in the query as follows. This approach has worked for me in similar scenarios on previous SQL Server versions, but you should measure it yourself (and compare with other suggestions above) to see what gets the desired results in your environment and server version. Good practice is to time multiple executions for confidence and assess if cacheing is playing a role.

declare @startdate datetime
set @startdate = DATEADD(m, -2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))  
declare @enddate datetime
set @enddate = CAST(CAST(DATEADD(d, -2, GETDATE()) AS DATE) AS DATETIME)
...
WHERE DATEFROMPARTS(dyear, dmonth, dday)
BETWEEN @startdate AND @enddate

Re-write T-SQL where clause causing performance issue

11 Answers11