小表中使用cast导致查询变慢

SQL：

select  top 100000 t24147.name as [$<交易日期_日>],会员人数 as [$<会员人数>]
from
(
    select OrderDateDC as 交易日期_日,sum(Members) as 会员人数
    from
    (
        select Day(o.OrderDate) as OrderDateDC,count(distinct c.customerid ) as Members
        from DimCustomer c
        inner join FactOrder o on c.customerid=o.customerid and o.brand=c.brand
        
        where  c.brand=1and o.orderdate >= '2020-06-01' and o.orderdate<'2020-07-01' and c.RegisteredDate<'2020-07-01'
             and  not exists ( select customerId from FactOrder nod where 1=1  and o.CustomerId = nod.CustomerId             and  nod.orderdate >= '2020-06-01' and nod.orderdate < '2020-07-01'                      group by Customerid having sum(case OrderType when - 1 then 1 else 0 end) = count(1)  )
        group by Day(o.OrderDate)
    ) b
    group by OrderDateDC
) a inner join (select -1 id,0 pid,'All' name,'0' code union all select  d id, -1 pid, ds name, cast （d  as varchar）code from DimDate) t24147 on a.交易日期_日=t24147.code
order by 交易日期_日

 

优化前：、 （47s）

 

 

 

 

 

 

将DimDate的d改为nvarchar(5)类型后：（1s）