I have to admit I am a computer programmer, and not a DBA so I lack a few prerequisites to do this analysis work properly.
That said, I have optimized an ORM generated query, and now the performance is quite acceptable, except sometimes (with some parameter) performance drops dramatically and I don't know how to solve the issue.
Looking around, I discovered this thing called 'parameter sniffing' and a quick tutorial on how to analyze the situation, but need some help to validate my assumption, and suggestions on how to resolve the issue.
I have an articles list filtered characteristics this generate the following query (in all the subsequent queries, all parameters are replaced by their actual value):
SELECT [t9].[ID], [t9].[ID_BRAND], [t9].[CODE], [t9].[CODFOR], [t9].[COD_ALT01], [t9].[COD_ALT02], [t9].[COD_ALT03], [t9].[ID_UOM], [t9].[IS_ACTIVE], [t9].[_ATTRIBUTES] AS [_ATTRIBUTES], [t9].[_DOCUMENTS] AS [_DOCUMENTS], [t9].[_SEO] AS [_SEO], [t9].[_TRANSLATIONS] AS [_TRANSLATIONS], [t9].[_TAGS] AS [_TAGS], [t9].[_NOTES] AS [_NOTES], [t9].[_METADATA] AS [_METADATA], [t9].[IS_B2B], [t9].[IS_B2C], [t9].[IS_PROMO], [t9].[IS_NEWS], [t9].[CAN_BE_RETURNED], [t9].[IS_SHIPPABLE], [t9].[HAS_SHIPPING_COSTS], [t9].[IS_PURCHEASABLE], [t9].[test], [t9].[ID2], [t9].[ID_LINE], [t9].[ID_GROUP], [t9].[ID_CLASS], [t9].[ID_FAM], [t9].[ID_ARTICLE], [t9].[LINE_CODE], [t9].[GROUP_CODE], [t9].[CLASS_CODE], [t9].[FAMILY_CODE], [t9].[ARTICLE_CODE], [t9].[test2], [t9].[ID3], [t9].[CODE2], [t9].[BUSINESS_NAME], [t9].[NAME], [t9].[PHONE_01], [t9].[PHONE_02], [t9].[PHONE_03], [t9].[FAX_01], [t9].[FAX_02], [t9].[COUNTRY_01], [t9].[CITY_01], [t9].[ADDRESS_01], [t9].[COUNTRY_02], [t9].[CITY_02], [t9].[ADDRESS_02], [t9].[EMAIL_01], [t9].[EMAIL_02], [t9].[PEC], [t9].[SITE_01], [t9].[SITE_02], [t9].[SITE_03], [t9].[SITE_04], [t9].[VAT_NUMBER], [t9].[SORT], [t9].[GROUPID_01], [t9].[IS_GROUPLEADER_01], [t9].[GROUPID_02], [t9].[IS_GROUPLEADER_02], [t9].[IS_ACTIVE2], [t9].[[_DOCUMENTS]]2] AS [_DOCUMENTS2], [t9].[[_SEO]]2] AS [_SEO2], [t9].[[_METADATA]]2] AS [_METADATA2], [t9].[test3], [t9].[ID4], [t9].[CODE3], [t9].[[_TRANSLATIONS]]2] AS [_TRANSLATIONS2], [t9].[[_METADATA]]3] AS [_METADATA3], [t10].[ID_CHARACTERISTIC], [t10].[ID_ARTICLE] AS [ID_ARTICLE2], [t10].[ID_FILTER], (
SELECT COUNT(*)
FROM (
SELECT NULL AS [EMPTY]
FROM [dbo].[tbl_src_ArticlesCharacteristics] AS [t11]
WHERE [t11].[ID_ARTICLE] = [t9].[ID]
) AS [t12]
) AS [value]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t2].[LINE_CODE], [t2].[GROUP_CODE], [t2].[CLASS_CODE], [t2].[FAMILY_CODE], [t0].[COD_ALT01], [t0].[ID], [t2].[ID], [t4].[ID], [t6].[ID]) AS [ROW_NUMBER], [t0].[ID], [t0].[ID_BRAND], [t0].[CODE], [t0].[CODFOR], [t0].[COD_ALT01], [t0].[COD_ALT02], [t0].[COD_ALT03], [t0].[ID_UOM], [t0].[IS_ACTIVE], [t0].[_ATTRIBUTES], [t0].[_DOCUMENTS], [t0].[_SEO], [t0].[_TRANSLATIONS], [t0].[_TAGS], [t0].[_NOTES], [t0].[_METADATA], [t0].[IS_B2B], [t0].[IS_B2C], [t0].[IS_PROMO], [t0].[IS_NEWS], [t0].[CAN_BE_RETURNED], [t0].[IS_SHIPPABLE], [t0].[HAS_SHIPPING_COSTS], [t0].[IS_PURCHEASABLE], [t2].[test], [t2].[ID] AS [ID2], [t2].[ID_LINE], [t2].[ID_GROUP], [t2].[ID_CLASS], [t2].[ID_FAM], [t2].[ID_ARTICLE], [t2].[LINE_CODE], [t2].[GROUP_CODE], [t2].[CLASS_CODE], [t2].[FAMILY_CODE], [t2].[ARTICLE_CODE], [t4].[test] AS [test2], [t4].[ID] AS [ID3], [t4].[CODE] AS [CODE2], [t4].[BUSINESS_NAME], [t4].[NAME], [t4].[PHONE_01], [t4].[PHONE_02], [t4].[PHONE_03], [t4].[FAX_01], [t4].[FAX_02], [t4].[COUNTRY_01], [t4].[CITY_01], [t4].[ADDRESS_01], [t4].[COUNTRY_02], [t4].[CITY_02], [t4].[ADDRESS_02], [t4].[EMAIL_01], [t4].[EMAIL_02], [t4].[PEC], [t4].[SITE_01], [t4].[SITE_02], [t4].[SITE_03], [t4].[SITE_04], [t4].[VAT_NUMBER], [t4].[SORT], [t4].[GROUPID_01], [t4].[IS_GROUPLEADER_01], [t4].[GROUPID_02], [t4].[IS_GROUPLEADER_02], [t4].[IS_ACTIVE] AS [IS_ACTIVE2], [t4].[_DOCUMENTS] AS [[_DOCUMENTS]]2], [t4].[_SEO] AS [[_SEO]]2], [t4].[_METADATA] AS [[_METADATA]]2], [t6].[test] AS [test3], [t6].[ID] AS [ID4], [t6].[CODE] AS [CODE3], [t6].[_TRANSLATIONS] AS [[_TRANSLATIONS]]2], [t6].[_METADATA] AS [[_METADATA]]3]
FROM [dbo].[tbl_ana_Articles] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[ID], [t1].[ID_LINE], [t1].[ID_GROUP], [t1].[ID_CLASS], [t1].[ID_FAM], [t1].[ID_ARTICLE], [t1].[LINE_CODE], [t1].[GROUP_CODE], [t1].[CLASS_CODE], [t1].[FAMILY_CODE], [t1].[ARTICLE_CODE]
FROM [dbo].[tbl_src_ArticlesCategories] AS [t1]
) AS [t2] ON [t2].[ID_ARTICLE] = [t0].[ID]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t3].[ID], [t3].[CODE], [t3].[BUSINESS_NAME], [t3].[NAME], [t3].[PHONE_01], [t3].[PHONE_02], [t3].[PHONE_03], [t3].[FAX_01], [t3].[FAX_02], [t3].[COUNTRY_01], [t3].[CITY_01], [t3].[ADDRESS_01], [t3].[COUNTRY_02], [t3].[CITY_02], [t3].[ADDRESS_02], [t3].[EMAIL_01], [t3].[EMAIL_02], [t3].[PEC], [t3].[SITE_01], [t3].[SITE_02], [t3].[SITE_03], [t3].[SITE_04], [t3].[VAT_NUMBER], [t3].[SORT], [t3].[GROUPID_01], [t3].[IS_GROUPLEADER_01], [t3].[GROUPID_02], [t3].[IS_GROUPLEADER_02], [t3].[IS_ACTIVE], [t3].[_DOCUMENTS], [t3].[_SEO], [t3].[_METADATA]
FROM [dbo].[tbl_ana_Brands] AS [t3]
) AS [t4] ON [t4].[ID] = [t0].[ID_BRAND]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t5].[ID], [t5].[CODE], [t5].[_TRANSLATIONS], [t5].[_METADATA]
FROM [dbo].[tbl_ana_UoMs] AS [t5]
) AS [t6] ON [t6].[ID] = [t0].[ID_UOM]
WHERE (((
SELECT COUNT(*)
FROM (
SELECT NULL AS [EMPTY]
FROM [dbo].[tbl_src_ArticlesCharacteristics] AS [t7]
WHERE ([t7].[ID_CHARACTERISTIC] IN (121, 122, 92, 93, 94, 95, 96)) AND ([t7].[ID_ARTICLE] = [t0].[ID])
GROUP BY [t7].[ID_FILTER]
) AS [t8]
)) = 2) AND ([t0].[IS_ACTIVE] = 1)
) AS [t9]
LEFT OUTER JOIN [dbo].[tbl_src_ArticlesCharacteristics] AS [t10] ON [t10].[ID_ARTICLE] = [t9].[ID]
WHERE [t9].[ROW_NUMBER] BETWEEN 1 AND 36
ORDER BY [t9].[ROW_NUMBER], [t10].[ID_CHARACTERISTIC], [t10].[ID_ARTICLE]
The execution plan is: https://www.brentozar.com/pastetheplan/?id=HJ-HfRfi9
So far so good execution takes less than a second. But if I filter for one more characteristic the query becomes:
SELECT [t9].[ID], [t9].[ID_BRAND], [t9].[CODE], [t9].[CODFOR], [t9].[COD_ALT01], [t9].[COD_ALT02], [t9].[COD_ALT03], [t9].[ID_UOM], [t9].[IS_ACTIVE], [t9].[_ATTRIBUTES] AS [_ATTRIBUTES], [t9].[_DOCUMENTS] AS [_DOCUMENTS], [t9].[_SEO] AS [_SEO], [t9].[_TRANSLATIONS] AS [_TRANSLATIONS], [t9].[_TAGS] AS [_TAGS], [t9].[_NOTES] AS [_NOTES], [t9].[_METADATA] AS [_METADATA], [t9].[IS_B2B], [t9].[IS_B2C], [t9].[IS_PROMO], [t9].[IS_NEWS], [t9].[CAN_BE_RETURNED], [t9].[IS_SHIPPABLE], [t9].[HAS_SHIPPING_COSTS], [t9].[IS_PURCHEASABLE], [t9].[test], [t9].[ID2], [t9].[ID_LINE], [t9].[ID_GROUP], [t9].[ID_CLASS], [t9].[ID_FAM], [t9].[ID_ARTICLE], [t9].[LINE_CODE], [t9].[GROUP_CODE], [t9].[CLASS_CODE], [t9].[FAMILY_CODE], [t9].[ARTICLE_CODE], [t9].[test2], [t9].[ID3], [t9].[CODE2], [t9].[BUSINESS_NAME], [t9].[NAME], [t9].[PHONE_01], [t9].[PHONE_02], [t9].[PHONE_03], [t9].[FAX_01], [t9].[FAX_02], [t9].[COUNTRY_01], [t9].[CITY_01], [t9].[ADDRESS_01], [t9].[COUNTRY_02], [t9].[CITY_02], [t9].[ADDRESS_02], [t9].[EMAIL_01], [t9].[EMAIL_02], [t9].[PEC], [t9].[SITE_01], [t9].[SITE_02], [t9].[SITE_03], [t9].[SITE_04], [t9].[VAT_NUMBER], [t9].[SORT], [t9].[GROUPID_01], [t9].[IS_GROUPLEADER_01], [t9].[GROUPID_02], [t9].[IS_GROUPLEADER_02], [t9].[IS_ACTIVE2], [t9].[[_DOCUMENTS]]2] AS [_DOCUMENTS2], [t9].[[_SEO]]2] AS [_SEO2], [t9].[[_METADATA]]2] AS [_METADATA2], [t9].[test3], [t9].[ID4], [t9].[CODE3], [t9].[[_TRANSLATIONS]]2] AS [_TRANSLATIONS2], [t9].[[_METADATA]]3] AS [_METADATA3], [t10].[ID_CHARACTERISTIC], [t10].[ID_ARTICLE] AS [ID_ARTICLE2], [t10].[ID_FILTER], (
SELECT COUNT(*)
FROM (
SELECT NULL AS [EMPTY]
FROM [dbo].[tbl_src_ArticlesCharacteristics] AS [t11]
WHERE [t11].[ID_ARTICLE] = [t9].[ID]
) AS [t12]
) AS [value]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t2].[LINE_CODE], [t2].[GROUP_CODE], [t2].[CLASS_CODE], [t2].[FAMILY_CODE], [t0].[COD_ALT01], [t0].[ID], [t2].[ID], [t4].[ID], [t6].[ID]) AS [ROW_NUMBER], [t0].[ID], [t0].[ID_BRAND], [t0].[CODE], [t0].[CODFOR], [t0].[COD_ALT01], [t0].[COD_ALT02], [t0].[COD_ALT03], [t0].[ID_UOM], [t0].[IS_ACTIVE], [t0].[_ATTRIBUTES], [t0].[_DOCUMENTS], [t0].[_SEO], [t0].[_TRANSLATIONS], [t0].[_TAGS], [t0].[_NOTES], [t0].[_METADATA], [t0].[IS_B2B], [t0].[IS_B2C], [t0].[IS_PROMO], [t0].[IS_NEWS], [t0].[CAN_BE_RETURNED], [t0].[IS_SHIPPABLE], [t0].[HAS_SHIPPING_COSTS], [t0].[IS_PURCHEASABLE], [t2].[test], [t2].[ID] AS [ID2], [t2].[ID_LINE], [t2].[ID_GROUP], [t2].[ID_CLASS], [t2].[ID_FAM], [t2].[ID_ARTICLE], [t2].[LINE_CODE], [t2].[GROUP_CODE], [t2].[CLASS_CODE], [t2].[FAMILY_CODE], [t2].[ARTICLE_CODE], [t4].[test] AS [test2], [t4].[ID] AS [ID3], [t4].[CODE] AS [CODE2], [t4].[BUSINESS_NAME], [t4].[NAME], [t4].[PHONE_01], [t4].[PHONE_02], [t4].[PHONE_03], [t4].[FAX_01], [t4].[FAX_02], [t4].[COUNTRY_01], [t4].[CITY_01], [t4].[ADDRESS_01], [t4].[COUNTRY_02], [t4].[CITY_02], [t4].[ADDRESS_02], [t4].[EMAIL_01], [t4].[EMAIL_02], [t4].[PEC], [t4].[SITE_01], [t4].[SITE_02], [t4].[SITE_03], [t4].[SITE_04], [t4].[VAT_NUMBER], [t4].[SORT], [t4].[GROUPID_01], [t4].[IS_GROUPLEADER_01], [t4].[GROUPID_02], [t4].[IS_GROUPLEADER_02], [t4].[IS_ACTIVE] AS [IS_ACTIVE2], [t4].[_DOCUMENTS] AS [[_DOCUMENTS]]2], [t4].[_SEO] AS [[_SEO]]2], [t4].[_METADATA] AS [[_METADATA]]2], [t6].[test] AS [test3], [t6].[ID] AS [ID4], [t6].[CODE] AS [CODE3], [t6].[_TRANSLATIONS] AS [[_TRANSLATIONS]]2], [t6].[_METADATA] AS [[_METADATA]]3]
FROM [dbo].[tbl_ana_Articles] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[ID], [t1].[ID_LINE], [t1].[ID_GROUP], [t1].[ID_CLASS], [t1].[ID_FAM], [t1].[ID_ARTICLE], [t1].[LINE_CODE], [t1].[GROUP_CODE], [t1].[CLASS_CODE], [t1].[FAMILY_CODE], [t1].[ARTICLE_CODE]
FROM [dbo].[tbl_src_ArticlesCategories] AS [t1]
) AS [t2] ON [t2].[ID_ARTICLE] = [t0].[ID]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t3].[ID], [t3].[CODE], [t3].[BUSINESS_NAME], [t3].[NAME], [t3].[PHONE_01], [t3].[PHONE_02], [t3].[PHONE_03], [t3].[FAX_01], [t3].[FAX_02], [t3].[COUNTRY_01], [t3].[CITY_01], [t3].[ADDRESS_01], [t3].[COUNTRY_02], [t3].[CITY_02], [t3].[ADDRESS_02], [t3].[EMAIL_01], [t3].[EMAIL_02], [t3].[PEC], [t3].[SITE_01], [t3].[SITE_02], [t3].[SITE_03], [t3].[SITE_04], [t3].[VAT_NUMBER], [t3].[SORT], [t3].[GROUPID_01], [t3].[IS_GROUPLEADER_01], [t3].[GROUPID_02], [t3].[IS_GROUPLEADER_02], [t3].[IS_ACTIVE], [t3].[_DOCUMENTS], [t3].[_SEO], [t3].[_METADATA]
FROM [dbo].[tbl_ana_Brands] AS [t3]
) AS [t4] ON [t4].[ID] = [t0].[ID_BRAND]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t5].[ID], [t5].[CODE], [t5].[_TRANSLATIONS], [t5].[_METADATA]
FROM [dbo].[tbl_ana_UoMs] AS [t5]
) AS [t6] ON [t6].[ID] = [t0].[ID_UOM]
WHERE (((
SELECT COUNT(*)
FROM (
SELECT NULL AS [EMPTY]
FROM [dbo].[tbl_src_ArticlesCharacteristics] AS [t7]
WHERE ([t7].[ID_CHARACTERISTIC] IN (121, 122, 92, 93, 94, 95, 96,97)) AND ([t7].[ID_ARTICLE] = [t0].[ID])
GROUP BY [t7].[ID_FILTER]
) AS [t8]
)) = 2) AND ([t0].[IS_ACTIVE] = 1)
) AS [t9]
LEFT OUTER JOIN [dbo].[tbl_src_ArticlesCharacteristics] AS [t10] ON [t10].[ID_ARTICLE] = [t9].[ID]
WHERE [t9].[ROW_NUMBER] BETWEEN 1 AND 36
ORDER BY [t9].[ROW_NUMBER], [t10].[ID_CHARACTERISTIC], [t10].[ID_ARTICLE]
The changed execution plan is: https://www.brentozar.com/pastetheplan/?id=Byf6M0Giq
The execution now takes almost 13s.
What I have noticed comparing the two execution plans is that even if they have the same shape, (structure and nodes type) they hugely change in the time taken by Hash Match (right outer join) node. But I have no idea on how to improve that.
Here I report the structure of involved tables so you have an idea of the indexes involved:
CREATE TABLE [dbo].[tbl_ana_Articles](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ID_BRAND] [int] NOT NULL,
[CODE] [nvarchar](40) NOT NULL,
[CODFOR] [nvarchar](40) NOT NULL,
[COD_ALT01] [nvarchar](50) NOT NULL,
[COD_ALT02] [nvarchar](50) NOT NULL,
[COD_ALT03] [nvarchar](50) NOT NULL,
[ID_UOM] [int] NOT NULL,
[IS_ACTIVE] [bit] NOT NULL,
[_ATTRIBUTES] [nvarchar](max) NOT NULL,
[_DOCUMENTS] [nvarchar](max) NOT NULL,
[_SEO] [nvarchar](max) NOT NULL,
[_TRANSLATIONS] [nvarchar](max) NOT NULL,
[_TAGS] [nvarchar](max) NOT NULL,
[_NOTES] [nvarchar](max) NOT NULL,
[_METADATA] [nvarchar](max) NOT NULL,
[IS_B2B] [bit] NOT NULL,
[IS_B2C] [bit] NOT NULL,
[IS_PROMO] [bit] NOT NULL,
[IS_NEWS] [bit] NOT NULL,
[CAN_BE_RETURNED] [bit] NOT NULL,
[IS_SHIPPABLE] [bit] NOT NULL,
[HAS_SHIPPING_COSTS] [bit] NOT NULL,
[IS_PURCHEASABLE] [bit] NOT NULL,
CONSTRAINT [PK_tbl_ana_articles] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[tbl_ana_Brands] Script Date: 06/07/2022 11:21:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_ana_Brands](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CODE] [nvarchar](10) NOT NULL,
[BUSINESS_NAME] [nvarchar](50) NOT NULL,
[NAME] [nvarchar](50) NOT NULL,
[PHONE_01] [nvarchar](20) NOT NULL,
[PHONE_02] [nvarchar](20) NOT NULL,
[PHONE_03] [nvarchar](20) NOT NULL,
[FAX_01] [nvarchar](20) NOT NULL,
[FAX_02] [nvarchar](20) NOT NULL,
[COUNTRY_01] [nvarchar](30) NOT NULL,
[CITY_01] [nvarchar](50) NOT NULL,
[ADDRESS_01] [nvarchar](100) NOT NULL,
[COUNTRY_02] [nvarchar](30) NOT NULL,
[CITY_02] [nvarchar](50) NOT NULL,
[ADDRESS_02] [nvarchar](100) NOT NULL,
[EMAIL_01] [nvarchar](100) NOT NULL,
[EMAIL_02] [nvarchar](100) NOT NULL,
[PEC] [nvarchar](100) NOT NULL,
[SITE_01] [nvarchar](400) NOT NULL,
[SITE_02] [nvarchar](400) NOT NULL,
[SITE_03] [nvarchar](400) NOT NULL,
[SITE_04] [nvarchar](400) NOT NULL,
[VAT_NUMBER] [nvarchar](50) NOT NULL,
[SORT] [int] NOT NULL,
[GROUPID_01] [int] NOT NULL,
[IS_GROUPLEADER_01] [bit] NOT NULL,
[GROUPID_02] [int] NOT NULL,
[IS_GROUPLEADER_02] [bit] NOT NULL,
[IS_ACTIVE] [bit] NOT NULL,
[_DOCUMENTS] [nvarchar](max) NOT NULL,
[_SEO] [nvarchar](max) NOT NULL,
[_METADATA] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_tbl_ana_Brands] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[tbl_ana_UoMs] Script Date: 06/07/2022 11:21:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_ana_UoMs](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CODE] [nvarchar](30) NOT NULL,
[_TRANSLATIONS] [nvarchar](max) NOT NULL,
[_METADATA] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_tbl_ana_UoM] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[tbl_src_ArticlesCharacteristics] Script Date: 06/07/2022 11:21:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_src_ArticlesCharacteristics](
[ID_CHARACTERISTIC] [int] NOT NULL,
[ID_ARTICLE] [int] NOT NULL,
[ID_FILTER] [int] NOT NULL,
CONSTRAINT [PK_tbl_src_ArticlesCharacteristics] PRIMARY KEY CLUSTERED
(
[ID_CHARACTERISTIC] ASC,
[ID_ARTICLE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Index [IX_ACTIVES_WITH_CODE] Script Date: 06/07/2022 11:21:05 ******/
CREATE NONCLUSTERED INDEX [IX_ACTIVES_WITH_CODE] ON [dbo].[tbl_ana_Articles]
(
[IS_ACTIVE] ASC
)
INCLUDE([CODE]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
/****** Object: Index [IX_BRANDS] Script Date: 06/07/2022 11:21:05 ******/
CREATE NONCLUSTERED INDEX [IX_BRANDS] ON [dbo].[tbl_ana_Articles]
(
[ID_BRAND] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IX_COD_ALT01] Script Date: 06/07/2022 11:21:05 ******/
CREATE NONCLUSTERED INDEX [IX_COD_ALT01] ON [dbo].[tbl_ana_Articles]
(
[COD_ALT01] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IX_COD_ALT02] Script Date: 06/07/2022 11:21:05 ******/
CREATE NONCLUSTERED INDEX [IX_COD_ALT02] ON [dbo].[tbl_ana_Articles]
(
[COD_ALT02] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IX_COD_ALT03] Script Date: 06/07/2022 11:21:05 ******/
CREATE NONCLUSTERED INDEX [IX_COD_ALT03] ON [dbo].[tbl_ana_Articles]
(
[COD_ALT03] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IX_CODE] Script Date: 06/07/2022 11:21:05 ******/
CREATE NONCLUSTERED INDEX [IX_CODE] ON [dbo].[tbl_ana_Articles]
(
[CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IX_CODFOR] Script Date: 06/07/2022 11:21:05 ******/
CREATE NONCLUSTERED INDEX [IX_CODFOR] ON [dbo].[tbl_ana_Articles]
(
[CODFOR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
/****** Object: Index [IX_UOM] Script Date: 06/07/2022 11:21:05 ******/
CREATE NONCLUSTERED INDEX [IX_UOM] ON [dbo].[tbl_ana_Articles]
(
[ID_UOM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
/****** Object: Index [MISSING_CHARACTERISTING_INDEX] Script Date: 06/07/2022 11:21:05 ******/
CREATE NONCLUSTERED INDEX [MISSING_CHARACTERISTING_INDEX] ON [dbo].[tbl_ana_Articles]
(
[IS_ACTIVE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IX_CODE] Script Date: 06/07/2022 11:21:05 ******/
CREATE NONCLUSTERED INDEX [IX_CODE] ON [dbo].[tbl_ana_Brands]
(
[CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
/****** Object: Index [IX_ID_FILTER] Script Date: 06/07/2022 11:21:05 ******/
CREATE NONCLUSTERED INDEX [IX_ID_FILTER] ON [dbo].[tbl_src_ArticlesCharacteristics]
(
[ID_FILTER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_src_ArticlesCharacteristics] Script Date: 06/07/2022 11:21:05 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_src_ArticlesCharacteristics] ON [dbo].[tbl_src_ArticlesCharacteristics]
(
[ID_ARTICLE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles_ID_BRAND] DEFAULT ((0)) FOR [ID_BRAND]
GO
ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles_COD_ALT03] DEFAULT (' ') FOR [COD_ALT03]
GO
ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles__ATTRIBUTES] DEFAULT ('') FOR [_ATTRIBUTES]
GO
ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles_TEST] DEFAULT ((0)) FOR [IS_B2B]
GO
ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles_IS_B2C] DEFAULT ((0)) FOR [IS_B2C]
GO
ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles_IS_PROMO] DEFAULT ((0)) FOR [IS_PROMO]
GO
ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles_IS_NEWS] DEFAULT ((0)) FOR [IS_NEWS]
GO
ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles_CAN_BE_RETURNED] DEFAULT ((0)) FOR [CAN_BE_RETURNED]
GO
ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles_IS_SHIPPABLE] DEFAULT ((0)) FOR [IS_SHIPPABLE]
GO
ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles_HAS_SHIPPING_COSTS] DEFAULT ((0)) FOR [HAS_SHIPPING_COSTS]
GO
ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles_IS_PURCHEASABLE] DEFAULT ((0)) FOR [IS_PURCHEASABLE]
GO
ALTER TABLE [dbo].[tbl_src_ArticlesCharacteristics] ADD CONSTRAINT [DF_tbl_src_ArticlesCharacteristics_ID_FILTER] DEFAULT ((0)) FOR [ID_FILTER]
GO
It seems if I omit to select these fields:
[_ATTRIBUTES] [nvarchar](max) NOT NULL,
[_DOCUMENTS] [nvarchar](max) NOT NULL,
[_SEO] [nvarchar](max) NOT NULL,
[_TRANSLATIONS] [nvarchar](max) NOT NULL,
[_TAGS] [nvarchar](max) NOT NULL,
[_NOTES] [nvarchar](max) NOT NULL,
[_METADATA] [nvarchar](max) NOT NULL,
performance improves a lot.
Those fields are meant to be extension point into data structure allowing the program to place few KB of XML inside each rows.
Still not clear if this is just a coincidence or may be a problem for the Hash Match operator.
I am working with SQL Server 2019 Standard Edition. The program is a C# application written for Net Framework 4.8 using the Linq To SQL ORM.
A lot of this query should be rewritten, unfortunately I have to deal with ORM query generation. Seems like that a few nvarchar(MAX) fields slow down the query, even if the result is paged. If I omit them I get a big performance boost.
