Why Table Spool on MemoryOptimized table and how to suppress it?

I am in the middle of the road to achieving an optimized query for faster execution, or at least I hope so. I stumbled upon a pattern (it looks strange to me) in the execution plan, which seems to be responsible for around half of the execution time currently. I did a research on the topic and this is how I managed to optimize it so far.

But first the background. I need to do this fairly basic, although a bit nasty-looking select query:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>select distinct a1.* from
(
select
distinct
al.AL_ParentCode as Code,
c.Code as ContrCode
from #ALevels as al
inner join ActiveContrForAddressees as c
on al.AL_Val = c.MarketType
and al.AL_Type = 1
and al.AL_ParentCode = '91'
inner join Dealer2Dealer as d2d
on
(
d2d.DealerID = c.DealerID
or d2d.DealerID = c.MerchandiserCode
or d2d.DealerID = c.StrikerCode
)
) as a1
inner join
(
select
distinct
al.AL_ParentCode as Code,
c.Code as ContrCode
from #ALevels as al
inner join ActiveContrForAddressees as c
on al.AL_Type = 6
and al.AL_ParentCode = '91'
inner join Dealer2Dealer as d2d
on al.AL_Val = d2d.ParentDealerID
and
(
c.DealerID = d2d.DealerID
or c.MerchandiserCode = d2d.DealerID
or c.StrikerCode = d2d.DealerID
)
) as a2
on a1.ContrCode = a2.ContrCode
inner join
(
select
distinct
al.AL_ParentCode as Code,
c.Code as ContrCode
from #ALevels as al
inner join ActiveContrForAddressees as c
on al.AL_Val = c.ObjType
and al.AL_Type = 9
and al.AL_ParentCode = '91'
inner join Dealer2Dealer as d2d
on
(
d2d.DealerID = c.DealerID
or d2d.DealerID = c.MerchandiserCode
or d2d.DealerID = c.StrikerCode
)
) as a3
on a2.ContrCode = a3.ContrCode
inner join
(
select
distinct
al.AL_ParentCode as Code,
c.Code as ContrCode
from #ALevels as al
inner join Dealer2Dealer as d2d
on al.AL_Val = d2d.ParentDealerType
and al.AL_Type = 12
inner join ActiveContrForAddressees as c
on
(
d2d.DealerID = c.DealerID
or d2d.DealerID = c.MerchandiserCode
or d2d.DealerID = c.StrikerCode
)
and al.AL_ParentCode = '91'
) as a4
on a3.ContrCode = a4.ContrCode
</code>
<code>select distinct a1.* from ( select distinct al.AL_ParentCode as Code, c.Code as ContrCode from #ALevels as al inner join ActiveContrForAddressees as c on al.AL_Val = c.MarketType and al.AL_Type = 1 and al.AL_ParentCode = '91' inner join Dealer2Dealer as d2d on ( d2d.DealerID = c.DealerID or d2d.DealerID = c.MerchandiserCode or d2d.DealerID = c.StrikerCode ) ) as a1 inner join ( select distinct al.AL_ParentCode as Code, c.Code as ContrCode from #ALevels as al inner join ActiveContrForAddressees as c on al.AL_Type = 6 and al.AL_ParentCode = '91' inner join Dealer2Dealer as d2d on al.AL_Val = d2d.ParentDealerID and ( c.DealerID = d2d.DealerID or c.MerchandiserCode = d2d.DealerID or c.StrikerCode = d2d.DealerID ) ) as a2 on a1.ContrCode = a2.ContrCode inner join ( select distinct al.AL_ParentCode as Code, c.Code as ContrCode from #ALevels as al inner join ActiveContrForAddressees as c on al.AL_Val = c.ObjType and al.AL_Type = 9 and al.AL_ParentCode = '91' inner join Dealer2Dealer as d2d on ( d2d.DealerID = c.DealerID or d2d.DealerID = c.MerchandiserCode or d2d.DealerID = c.StrikerCode ) ) as a3 on a2.ContrCode = a3.ContrCode inner join ( select distinct al.AL_ParentCode as Code, c.Code as ContrCode from #ALevels as al inner join Dealer2Dealer as d2d on al.AL_Val = d2d.ParentDealerType and al.AL_Type = 12 inner join ActiveContrForAddressees as c on ( d2d.DealerID = c.DealerID or d2d.DealerID = c.MerchandiserCode or d2d.DealerID = c.StrikerCode ) and al.AL_ParentCode = '91' ) as a4 on a3.ContrCode = a4.ContrCode </code>
select distinct a1.* from
(
select
    distinct
    al.AL_ParentCode as Code,
c.Code as ContrCode
from #ALevels as al
inner join ActiveContrForAddressees as c
on al.AL_Val = c.MarketType
and al.AL_Type = 1
and al.AL_ParentCode = '91'
    inner join Dealer2Dealer as d2d
    on
    (
        d2d.DealerID = c.DealerID
        or d2d.DealerID = c.MerchandiserCode
        or d2d.DealerID = c.StrikerCode
    )
) as a1

inner join
(
select
    distinct
    al.AL_ParentCode as Code,
c.Code as ContrCode
from #ALevels as al
inner join ActiveContrForAddressees as c
on al.AL_Type = 6
and al.AL_ParentCode = '91'
inner join Dealer2Dealer as d2d
on al.AL_Val = d2d.ParentDealerID
and
(
    c.DealerID = d2d.DealerID
    or c.MerchandiserCode = d2d.DealerID
    or c.StrikerCode = d2d.DealerID
)
) as a2

on a1.ContrCode = a2.ContrCode

inner join
(
select
    distinct
    al.AL_ParentCode as Code,
c.Code as ContrCode
from #ALevels as al
inner join ActiveContrForAddressees as c
on al.AL_Val = c.ObjType
and al.AL_Type = 9
and al.AL_ParentCode = '91'
    inner join Dealer2Dealer as d2d
    on
    (
        d2d.DealerID = c.DealerID
        or d2d.DealerID = c.MerchandiserCode
        or d2d.DealerID = c.StrikerCode
    )

) as a3

on a2.ContrCode = a3.ContrCode

inner join
(
select
    distinct
    al.AL_ParentCode as Code,
c.Code as ContrCode
from #ALevels as al
inner join Dealer2Dealer as d2d
on al.AL_Val = d2d.ParentDealerType
and al.AL_Type = 12
inner join ActiveContrForAddressees as c
on
(
    d2d.DealerID = c.DealerID
    or d2d.DealerID = c.MerchandiserCode
    or d2d.DealerID = c.StrikerCode
)
and al.AL_ParentCode = '91'
) as a4

on a3.ContrCode = a4.ContrCode

Table #ALevels is populated from a normal table in advance, in a separate query.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>-- this index is created after the table is populated:
create nonclustered index idx_ALevels
on #ALevels (
AL_ParentCode, AL_Type, AL_Val
) include (AL_ParentType)
</code>
<code>-- this index is created after the table is populated: create nonclustered index idx_ALevels on #ALevels ( AL_ParentCode, AL_Type, AL_Val ) include (AL_ParentType) </code>
-- this index is created after the table is populated:
create nonclustered index idx_ALevels
on #ALevels (
    AL_ParentCode, AL_Type, AL_Val
) include (AL_ParentType)

The definition of table ActiveContrForAddressees is this:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>create table ActiveContrForAddressees (
Code nvarchar(25) not null primary key nonclustered hash with (bucket_count = 150000),
DealerID nchar(10) null,
MerchandiserCode nvarchar(20) null,
StrikerCode nvarchar(255) null,
MarketType nvarchar(20) null,
CustomerType nvarchar(20) null,
CustSubClass nvarchar(20) null,
CustClass nvarchar(20) null,
Seasonal nvarchar(20) null,
ObjType nvarchar(20) null,
RegionCode nvarchar(60) null,
InternalCategory int null,
Chain nvarchar(100) null,
index idx_ACFA nonclustered (
Code, DealerID, MerchandiserCode, StrikerCode,
MarketType, CustomerType, CustSubClass, CustClass,
Seasonal, ObjType, RegionCode, InternalCategory, Chain
)
) with (
memory_optimized = on
)
</code>
<code>create table ActiveContrForAddressees ( Code nvarchar(25) not null primary key nonclustered hash with (bucket_count = 150000), DealerID nchar(10) null, MerchandiserCode nvarchar(20) null, StrikerCode nvarchar(255) null, MarketType nvarchar(20) null, CustomerType nvarchar(20) null, CustSubClass nvarchar(20) null, CustClass nvarchar(20) null, Seasonal nvarchar(20) null, ObjType nvarchar(20) null, RegionCode nvarchar(60) null, InternalCategory int null, Chain nvarchar(100) null, index idx_ACFA nonclustered ( Code, DealerID, MerchandiserCode, StrikerCode, MarketType, CustomerType, CustSubClass, CustClass, Seasonal, ObjType, RegionCode, InternalCategory, Chain ) ) with ( memory_optimized = on ) </code>
create table ActiveContrForAddressees (
    Code nvarchar(25) not null primary key nonclustered hash with (bucket_count = 150000),
    DealerID nchar(10) null,
    MerchandiserCode nvarchar(20) null,
    StrikerCode nvarchar(255) null,
    MarketType nvarchar(20) null,
    CustomerType nvarchar(20) null,
    CustSubClass nvarchar(20) null,
    CustClass nvarchar(20) null,
    Seasonal nvarchar(20) null,
    ObjType nvarchar(20) null,
    RegionCode nvarchar(60) null,
    InternalCategory int null,
    Chain nvarchar(100) null,
    index idx_ACFA nonclustered (
        Code, DealerID, MerchandiserCode, StrikerCode,
        MarketType, CustomerType, CustSubClass, CustClass,
        Seasonal, ObjType, RegionCode, InternalCategory, Chain
    )
) with (
    memory_optimized = on
)

It gets refreshed by the following procedure every time the data in the normal table changes:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>create procedure RefreshActiveContr @contrCode nvarchar(25) = null
as
begin
insert into Objects (O_Type) values ('RefreshActiveContrSync')
--truncate table ActiveContrForAddressees
--The statement 'TRUNCATE TABLE' is not supported with memory optimized tables.
delete from ActiveContrForAddressees
where coalesce(@contrCode, '') = ''
or Code = @contrCode
insert into ActiveContrForAddressees
select Code, DealerID, MerchandiserCode, StrikerCode,
MarketType, CustomerType, CustSubClass, CustClass,
Seasonal, ObjType, RegionCode, InternalCategory, Chain
from Contragent
where coalesce(Distributor, 0) != 1
and coalesce(Active, 0) > 0
and coalesce(blocked, '') != '1'
and
(
coalesce(@contrCode, '') = ''
or Code = @contrCode
)
-- https://key2consulting.com/incorrect-indexing-can-slow-down-memory-optimized-tables/
update statistics ActiveContrForAddressees with norecompute, fullscan
delete from Objects where O_Type = 'RefreshActiveContrSync'
end
</code>
<code>create procedure RefreshActiveContr @contrCode nvarchar(25) = null as begin insert into Objects (O_Type) values ('RefreshActiveContrSync') --truncate table ActiveContrForAddressees --The statement 'TRUNCATE TABLE' is not supported with memory optimized tables. delete from ActiveContrForAddressees where coalesce(@contrCode, '') = '' or Code = @contrCode insert into ActiveContrForAddressees select Code, DealerID, MerchandiserCode, StrikerCode, MarketType, CustomerType, CustSubClass, CustClass, Seasonal, ObjType, RegionCode, InternalCategory, Chain from Contragent where coalesce(Distributor, 0) != 1 and coalesce(Active, 0) > 0 and coalesce(blocked, '') != '1' and ( coalesce(@contrCode, '') = '' or Code = @contrCode ) -- https://key2consulting.com/incorrect-indexing-can-slow-down-memory-optimized-tables/ update statistics ActiveContrForAddressees with norecompute, fullscan delete from Objects where O_Type = 'RefreshActiveContrSync' end </code>
create procedure RefreshActiveContr @contrCode nvarchar(25) = null
as
begin

    insert into Objects (O_Type) values ('RefreshActiveContrSync')

    --truncate table ActiveContrForAddressees
    --The statement 'TRUNCATE TABLE' is not supported with memory optimized tables.
    delete from ActiveContrForAddressees
    where coalesce(@contrCode, '') = ''
    or Code = @contrCode

    insert into ActiveContrForAddressees
    select Code, DealerID, MerchandiserCode, StrikerCode,
    MarketType, CustomerType, CustSubClass, CustClass,
    Seasonal, ObjType, RegionCode, InternalCategory, Chain
    from Contragent
    where coalesce(Distributor, 0) != 1
    and coalesce(Active, 0) > 0
    and coalesce(blocked, '') != '1'
    and
    (
        coalesce(@contrCode, '') = ''
        or Code = @contrCode
    )

    -- https://key2consulting.com/incorrect-indexing-can-slow-down-memory-optimized-tables/
    update statistics ActiveContrForAddressees with norecompute, fullscan

    delete from Objects where O_Type = 'RefreshActiveContrSync'

end

That ‘RefreshActiveContrSync’ thing is a synchronizing flag, which is checked and the select query is allowed to execute only if it is missing.

The definition of table Dealer2Dealer is this:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>-- https://www.sqlshack.com/memory-optimized-table-variables-in-sql-server/
-- https://techcommunity.microsoft.com/t5/sql-server-blog/improving-temp-table-and-table-variable-performance-using-memory/ba-p/384697
-- https://www.mssqltips.com/sqlservertip/3104/determine-bucketcount-for-hash-indexes-for-sql-server-memory-optimized-tables/
-- https://www.mssqltips.com/sqlservertip/3099/understanding-sql-server-memoryoptimized-tables-hash-indexes/
-- Clustered indexes, which are the default for primary keys, are not supported with memory optimized tables. Specify a NONCLUSTERED index instead.
create table Dealer2Dealer (
ParentDealerID nvarchar(255) not null,
ParentDealerType nvarchar(255) null,
DealerID nvarchar(255) not null,
primary key nonclustered /*hash*/ (DealerID, ParentDealerID)
--with (bucket_count = 1024)
) with (
memory_optimized = on
)
</code>
<code>-- https://www.sqlshack.com/memory-optimized-table-variables-in-sql-server/ -- https://techcommunity.microsoft.com/t5/sql-server-blog/improving-temp-table-and-table-variable-performance-using-memory/ba-p/384697 -- https://www.mssqltips.com/sqlservertip/3104/determine-bucketcount-for-hash-indexes-for-sql-server-memory-optimized-tables/ -- https://www.mssqltips.com/sqlservertip/3099/understanding-sql-server-memoryoptimized-tables-hash-indexes/ -- Clustered indexes, which are the default for primary keys, are not supported with memory optimized tables. Specify a NONCLUSTERED index instead. create table Dealer2Dealer ( ParentDealerID nvarchar(255) not null, ParentDealerType nvarchar(255) null, DealerID nvarchar(255) not null, primary key nonclustered /*hash*/ (DealerID, ParentDealerID) --with (bucket_count = 1024) ) with ( memory_optimized = on ) </code>
-- https://www.sqlshack.com/memory-optimized-table-variables-in-sql-server/
-- https://techcommunity.microsoft.com/t5/sql-server-blog/improving-temp-table-and-table-variable-performance-using-memory/ba-p/384697
-- https://www.mssqltips.com/sqlservertip/3104/determine-bucketcount-for-hash-indexes-for-sql-server-memory-optimized-tables/
-- https://www.mssqltips.com/sqlservertip/3099/understanding-sql-server-memoryoptimized-tables-hash-indexes/
-- Clustered indexes, which are the default for primary keys, are not supported with memory optimized tables. Specify a NONCLUSTERED index instead.
create table Dealer2Dealer (
    ParentDealerID nvarchar(255) not null,
    ParentDealerType nvarchar(255) null,
    DealerID nvarchar(255) not null,
    primary key nonclustered /*hash*/ (DealerID, ParentDealerID)
    --with (bucket_count = 1024)
) with (
    memory_optimized = on
)

It gets refreshed by a similar procedure to the other one, which refreshes ActiveContrForAddressees table:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>--truncate table Dealer2Dealer
--The statement 'TRUNCATE TABLE' is not supported with memory optimized tables.
delete from Dealer2Dealer
--a couple of insert queries...
-- https://key2consulting.com/incorrect-indexing-can-slow-down-memory-optimized-tables/
update statistics Dealer2Dealer with norecompute, fullscan
</code>
<code>--truncate table Dealer2Dealer --The statement 'TRUNCATE TABLE' is not supported with memory optimized tables. delete from Dealer2Dealer --a couple of insert queries... -- https://key2consulting.com/incorrect-indexing-can-slow-down-memory-optimized-tables/ update statistics Dealer2Dealer with norecompute, fullscan </code>
--truncate table Dealer2Dealer
--The statement 'TRUNCATE TABLE' is not supported with memory optimized tables.
delete from Dealer2Dealer

--a couple of insert queries...

-- https://key2consulting.com/incorrect-indexing-can-slow-down-memory-optimized-tables/
update statistics Dealer2Dealer with norecompute, fullscan

And now the interesting part – the execution plan and times, shown in SQL Server Management Studio:

And the execution plan itself:

Here you can see that all other operators execute in a matter of seconds:

Even the operators on the branch where the Table Spool is positioned:

Is there a way to optimize this query?

You may ask me why don’t I use table variables of certain memory optimized type. I tried with table variables, but SQL Server complained about the fact that the table variable is out of the scope of the exec() or at least this is the conclusion I made, after it complained like “Must declare the table variable @temp”

The problem is that this query is built as a string and exec()-ed after that. Actually, it is a procedure, which builds several such query strings and executes them one after another, based on the tables #ALevels, ActiveContrForAddressees and Dealer2Dealer and a couple of other ones. It inserts the result from every query into another temporary table #Results and finally selects from that table. Thus, I decided not to create the suggested index, because the constructed query may include another column from table ActiveContrForAddressees, based on a very specific logic, and it once said that I cannot create more than 8 (if I remember correctly) indexes on a memory optimized table.

Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa Dịch vụ tổ chức sự kiện 5 sao Thông tin về chúng tôi Dịch vụ sinh nhật bé trai Dịch vụ sinh nhật bé gái Sự kiện trọn gói Các tiết mục giải trí Dịch vụ bổ trợ Tiệc cưới sang trọng Dịch vụ khai trương Tư vấn tổ chức sự kiện Hình ảnh sự kiện Cập nhật tin tức Liên hệ ngay Thuê chú hề chuyên nghiệp Tiệc tất niên cho công ty Trang trí tiệc cuối năm Tiệc tất niên độc đáo Sinh nhật bé Hải Đăng Sinh nhật đáng yêu bé Khánh Vân Sinh nhật sang trọng Bích Ngân Tiệc sinh nhật bé Thanh Trang Dịch vụ ông già Noel Xiếc thú vui nhộn Biểu diễn xiếc quay đĩa Dịch vụ tổ chức tiệc uy tín Khám phá dịch vụ của chúng tôi Tiệc sinh nhật cho bé trai Trang trí tiệc cho bé gái Gói sự kiện chuyên nghiệp Chương trình giải trí hấp dẫn Dịch vụ hỗ trợ sự kiện Trang trí tiệc cưới đẹp Khởi đầu thành công với khai trương Chuyên gia tư vấn sự kiện Xem ảnh các sự kiện đẹp Tin mới về sự kiện Kết nối với đội ngũ chuyên gia Chú hề vui nhộn cho tiệc sinh nhật Ý tưởng tiệc cuối năm Tất niên độc đáo Trang trí tiệc hiện đại Tổ chức sinh nhật cho Hải Đăng Sinh nhật độc quyền Khánh Vân Phong cách tiệc Bích Ngân Trang trí tiệc bé Thanh Trang Thuê dịch vụ ông già Noel chuyên nghiệp Xem xiếc khỉ đặc sắc Xiếc quay đĩa thú vị
Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa
Thiết kế website Thiết kế website Thiết kế website Cách kháng tài khoản quảng cáo Mua bán Fanpage Facebook Dịch vụ SEO Tổ chức sinh nhật