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:
<code>select distinct a1.* from
al.AL_ParentCode as Code,
inner join ActiveContrForAddressees as c
on al.AL_Val = c.MarketType
and al.AL_ParentCode = '91'
inner join Dealer2Dealer as d2d
d2d.DealerID = c.DealerID
or d2d.DealerID = c.MerchandiserCode
or d2d.DealerID = c.StrikerCode
al.AL_ParentCode as Code,
inner join ActiveContrForAddressees as c
and al.AL_ParentCode = '91'
inner join Dealer2Dealer as d2d
on al.AL_Val = d2d.ParentDealerID
c.DealerID = d2d.DealerID
or c.MerchandiserCode = d2d.DealerID
or c.StrikerCode = d2d.DealerID
on a1.ContrCode = a2.ContrCode
al.AL_ParentCode as Code,
inner join ActiveContrForAddressees as c
and al.AL_ParentCode = '91'
inner join Dealer2Dealer as d2d
d2d.DealerID = c.DealerID
or d2d.DealerID = c.MerchandiserCode
or d2d.DealerID = c.StrikerCode
on a2.ContrCode = a3.ContrCode
al.AL_ParentCode as Code,
inner join Dealer2Dealer as d2d
on al.AL_Val = d2d.ParentDealerType
inner join ActiveContrForAddressees as c
d2d.DealerID = c.DealerID
or d2d.DealerID = c.MerchandiserCode
or d2d.DealerID = c.StrikerCode
and al.AL_ParentCode = '91'
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
</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.
<code>-- this index is created after the table is populated:
create nonclustered index idx_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)
</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:
<code>create table ActiveContrForAddressees (
Code nvarchar(25) not null primary key nonclustered hash with (bucket_count = 150000),
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
<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:
<code>create procedure RefreshActiveContr @contrCode nvarchar(25) = null
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, '') = ''
insert into ActiveContrForAddressees
select Code, DealerID, MerchandiserCode, StrikerCode,
MarketType, CustomerType, CustSubClass, CustClass,
Seasonal, ObjType, RegionCode, InternalCategory, Chain
where coalesce(Distributor, 0) != 1
and coalesce(Active, 0) > 0
and coalesce(blocked, '') != '1'
coalesce(@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'
<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:
<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)
<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:
<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
</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.