Constant scans and compute scalars in execution plan when joining table and temp table

Question

I'm checking if this statement could take less time to execute. The execution plan is absolutely confusing. The query and plan are below.

There is a join between temp table and normal table with clustered index on Date column. Table contains ~1000 rows and temp table has a bit over 1 million.

In execution plan this join is reflected in two nested loops with lots of strange things like Constant Scans and Compute Scalars taking part in the process.

Clustered Index Seek here takes 74% and estimated number of rows is 1 there. Actual number of rows is huge.

    select t.StartDateTime
        , t.EndDateTime
        , t.StartDateTime_BusinessHours
        , t.EndDateTime_BusinessHours
        , count (c.Date) as DaysOffCount
    --into #DaysOff
    from #CorrectedStartEndDateTime as t
    left join util.Calendar as c on c. Date >= t.StartDateTime_BusinessHours and c. Date <= t.EndDateTime_BusinessHours and c.IsDayOff = 1
    group by t.StartDateTime
        , t.EndDateTime
        , t.StartDateTime_BusinessHours
        , t.EndDateTime_BusinessHours

Here is a link to the execution plan: https://www.brentozar.com/pastetheplan/?id=r1crVr6rB

Temporary table is a 'select into' dump from another query without any indexing. And table looks this way:

CREATE TABLE [util].[Calendar](
    [Date] [date] NOT NULL,
    [IsDayOff] [bit] NOT NULL,
    [DaysTillNextBusinessDay] [int] NOT NULL,
 CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED ([Date] ASC)
)

I would expect this to be a simple nested loop between temp table scan and clustered index seek or anything similar, could you please help me understand what's happenning and why?

If you're going to provide an execution plan, use Paste the plan; images won't help us as we can't interact with an image. Can include the DDL of your objects and the relevant indexes too please? I note you're using a temporary table too; are you indexing that if it contains a large number of rows? — Larnu
@Larnu, thank you so much for advice! I have added the link to execution plan and create table statement to the question text. Temporary table is created with select into and has no indexing. — ivanochkah
So does your table Calendar have no indexes apart from the one on [Date]? — Larnu
What data type are t.StartDateTime_BusinessHours and t.EndDateTime_BusinessHours? Do they match the data type of c.Date? — Chris Hackett

Vladimir Baranov Vladimir Baranov · Accepted Answer · 2019-09-04T14:30:13

I don't know all the details of these additional operators in the plan, but they are very cheap. Most likely they are added to the plan because there is a mismatch in types of Calendar.date and StartDateTime_BusinessHours, EndDateTime_BusinessHours.

You can see a call to internal function

GetRangeWithMismatchedTypes([tempdb].[dbo].[#CorrectedStartEndDateTime].[StartDateTime_BusinessHours] as [t].[StartDateTime_BusinessHours],NULL,(22))

in the plan. Engine has to compare somehow date with datetime and these strange looking operators are introduced to avoid conversion of date into datetime, so that index still could be used.

The plan itself looks fine. I doubt you can make it run significantly faster.

See https://dba.stackexchange.com/questions/128526/having-hard-time-understanding-expressions-in-below-query-plan for more details about GetRangeWithMismatchedTypes.

I don't think that it is a problem in this specific query that there is a big difference in the number of estimated and actual rows in the index seek of the Calendar table. I think that this mis-estimation doesn't make the query run slower.

You can try to rewrite the query using OUTER APPLY to avoid GROUP BY. It may be a bit faster.

select 
    t.StartDateTime
    , t.EndDateTime
    , t.StartDateTime_BusinessHours
    , t.EndDateTime_BusinessHours
    , A.DaysOffCount
--into #DaysOff
from 
    #CorrectedStartEndDateTime as t
    OUTER APPLY
    (
        SELECT
            COUNT(*) AS DaysOffCount
        FROM
            util.Calendar as c
        WHERE
            c.Date >= t.StartDateTime_BusinessHours 
            and c.Date <= t.EndDateTime_BusinessHours 
            and c.IsDayOff = 1
    ) AS A
;

Constant scans and compute scalars in execution plan when joining table and temp table

1 Answers