How can I improve the execution time for a query where the initial Rows in the view were: 16000 and the query worked resonable ( under 1 second ), but now there are : 42487 rows in the View and the execution time is over 25 seconds ... which is not acceptable ?
Hardware&Software: SQL Enterprise 2019 , Azure Virtual Machine : D8s_v3, 8 Vcpu, 32 GB Ram, Premium SSD. The Server use just a small part from available resources arround 5 % .
The View :
USE [TestDB]
GO
/****** Object: View [dbo].[ViewAdminProductAtributeV2HintView] Script Date: 8/24/2020 6:11:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create VIEW [dbo].[ViewAdminProductAtributeV2HintView] with SCHEMABINDING AS
select [pa].[ProductAtributeID], [a].[AtributeID], [av].[AtributeValueID],[pa].[ProductID],
case when [pa].[GroupID] IS NULL then 0 else [pa].[GroupID] end as [GroupID] ,
case when [pa].[PartitionID] IS NULL then 0 else [pa].[PartitionID] end as [PartitionID],
case when [pa].[PricePolicyID] IS NULL then 0 else [pa].[PricePolicyID] end as [PricePolicyID],
case when [pa].[PricePolicyEntityID] IS NULL then 0 else [pa].[PricePolicyEntityID] end as [PricePolicyEntityID],
[pa].[ProductCategoryID],[pa].[ProductCategoryIDP],[pa].[ProductCategoryIDPP],[pa].[IsPublished],
[av].[Title] as AtributeValueTitle, [av].[Importance] as AtributeValueImportance
from [dbo].[ProductAtributes] [pa]
inner join [dbo].[Atributes] [a] on [pa].[AtributeID] = [a].[AtributeID] and ([a].[IsDisplayToFilters]=1 or [a].[IsDisplayToFiltersOnKeySearch]=1)
inner join [dbo].[AtributeValues] [av] on [pa].[AtributeValueID] = [av].[AtributeValueID]
CREATE Unique CLUSTERED INDEX [IX_AdminProductAtributesFiltersHintView] ON [dbo].[ViewAdminProductAtributeHintViewV2]
(
[ProductAtributeID] ASC
)
CREATE NONCLUSTERED INDEX [ViewAdminProductAtributeV2HintView_AtributeID_AtributeValueID]
ON [dbo].[ViewAdminProductAtributeV2HintView] ([AtributeID],[AtributeValueID])
INCLUDE ([ProductID],[GroupID],[PartitionID],[PricePolicyID],[PricePolicyEntityID])
The Query Execution Time is ( 25 Seconds ) ( Updated: Now in 15 Seconds ) . But After 6 Joins the Execution time is : 30 seconds .
SELECT COUNT(*)
FROM [Products] AS [p]
INNER JOIN (
SELECT [v].[ProductID]
FROM [ViewAdminProductAtributeV2HintView] AS [v] WITH (NOEXPAND)
inner hash join [ViewAdminProductAtributeV2HintView] AS [v0] WITH (NOEXPAND) ON [v].[ProductID] = [v0].[ProductID]
inner hash join [ViewAdminProductAtributeV2HintView] AS [v1] WITH (NOEXPAND) ON [v].[ProductID] = [v1].[ProductID]
inner hash join [ViewAdminProductAtributeV2HintView] AS [v2] WITH (NOEXPAND) ON [v].[ProductID] = [v2].[ProductID]
inner hash join [ViewAdminProductAtributeV2HintView] AS [v3] WITH (NOEXPAND) ON [v].[ProductID] = [v3].[ProductID]
inner hash join [ViewAdminProductAtributeV2HintView] AS [v4] WITH (NOEXPAND) ON [v].[ProductID] = [v4].[ProductID]
WHERE ((((((((([v0].[AtributeID] = 133) AND [v0].[AtributeValueID] IN (376)) AND ((([v].[GroupID] = 0) OR ([v0].[GroupID] = 0)) OR ([v].[GroupID] = [v0].[GroupID]))) AND ((([v].[PartitionID] = 0) OR ([v0].[PartitionID] = 0)) OR ([v].[PartitionID] = [v0].[PartitionID]))) AND ((([v].[PricePolicyID] = 0) OR ([v0].[PricePolicyID] = 0)) OR ([v].[PricePolicyID] = [v0].[PricePolicyID]))) AND ((([v].[PricePolicyEntityID] = 0) OR ([v0].[PricePolicyEntityID] = 0)) OR ([v].[PricePolicyEntityID] = [v0].[PricePolicyEntityID]))) AND (((((([v1].[AtributeID] = 242) AND [v1].[AtributeValueID] IN (596)) AND ((([v].[GroupID] = 0) OR ([v1].[GroupID] = 0)) OR ([v].[GroupID] = [v1].[GroupID]))) AND ((([v].[PartitionID] = 0) OR ([v1].[PartitionID] = 0)) OR ([v].[PartitionID] = [v1].[PartitionID]))) AND ((([v].[PricePolicyID] = 0) OR ([v1].[PricePolicyID] = 0)) OR ([v].[PricePolicyID] = [v1].[PricePolicyID]))) AND ((([v].[PricePolicyEntityID] = 0) OR ([v1].[PricePolicyEntityID] = 0)) OR ([v].[PricePolicyEntityID] = [v1].[PricePolicyEntityID])))) AND (((((([v2].[AtributeID] = 243) AND [v2].[AtributeValueID] IN (599)) AND ((([v].[GroupID] = 0) OR ([v2].[GroupID] = 0)) OR ([v].[GroupID] = [v2].[GroupID]))) AND ((([v].[PartitionID] = 0) OR ([v2].[PartitionID] = 0)) OR ([v].[PartitionID] = [v2].[PartitionID]))) AND ((([v].[PricePolicyID] = 0) OR ([v2].[PricePolicyID] = 0)) OR ([v].[PricePolicyID] = [v2].[PricePolicyID]))) AND ((([v].[PricePolicyEntityID] = 0) OR ([v2].[PricePolicyEntityID] = 0)) OR ([v].[PricePolicyEntityID] = [v2].[PricePolicyEntityID])))) AND (((((([v3].[AtributeID] = 157) AND [v3].[AtributeValueID] IN (958)) AND ((([v].[GroupID] = 0) OR ([v3].[GroupID] = 0)) OR ([v].[GroupID] = [v3].[GroupID]))) AND ((([v].[PartitionID] = 0) OR ([v3].[PartitionID] = 0)) OR ([v].[PartitionID] = [v3].[PartitionID]))) AND ((([v].[PricePolicyID] = 0) OR ([v3].[PricePolicyID] = 0)) OR ([v].[PricePolicyID] = [v3].[PricePolicyID]))) AND ((([v].[PricePolicyEntityID] = 0) OR ([v3].[PricePolicyEntityID] = 0)) OR ([v].[PricePolicyEntityID] = [v3].[PricePolicyEntityID])))) AND (((((([v4].[AtributeID] = 158) AND [v4].[AtributeValueID] IN (1298)) AND ((([v].[GroupID] = 0) OR ([v4].[GroupID] = 0)) OR ([v].[GroupID] = [v4].[GroupID]))) AND ((([v].[PartitionID] = 0) OR ([v4].[PartitionID] = 0)) OR ([v].[PartitionID] = [v4].[PartitionID]))) AND ((([v].[PricePolicyID] = 0) OR ([v4].[PricePolicyID] = 0)) OR ([v].[PricePolicyID] = [v4].[PricePolicyID]))) AND ((([v].[PricePolicyEntityID] = 0) OR ([v4].[PricePolicyEntityID] = 0)) OR ([v].[PricePolicyEntityID] = [v4].[PricePolicyEntityID])))
GROUP BY [v].[ProductID]
) AS [t] ON [p].[ProductID] = [t].[ProductID]
WHERE [p].[ProductCategoryID] = 50
I have used inner hash join because in the database execution plans were some suggestions about inner hash join .
The Join number can't be predicted. It depends on what Filters the user will select . The same query with 2-3 joins will execute under 1 second, but after 4-5 joins the execution time goes to 25 seconds ( Updated: Now in 15 Seconds ) .
**This query is an EF Core Translation from a .Net Core 3.1 Project
------------
var query = context.Products.Where(p => p.ProductCategoryID== ProductCategoryID).AsQueryable();
var productAtibutesQuery = context.ViewAdminProductAtributes.WithHashJoin("INNERHASHJOIN").WithHint("NOEXPAND").AsQueryable();
foreach (var filtersGroup in Filters.GroupBy(p => p.AtributeID))
{
int atributeID = filtersGroup.Key;
var atributeValues = filtersGroup.Select(p => p.AtributeValueID).ToList();
productAtibutesQuery = ( from pf in productAtibutesQuery
join y in context.ViewAdminProductAtributes.WithHint("NOEXPAND")
on pf.ProductID equals y.ProductID
where y.AtributeID == atributeID && atributeValues.Contains(y.AtributeValueID)
&&
((pf.GroupID == y.GroupID) ||(pf.GroupID != 0 && y.GroupID == 0) || (pf.GroupID == 0 && y.GroupID != 0))
&&
((pf.PartitionID == y.PartitionID) || (pf.PartitionID != 0 && y.PartitionID == 0) || (pf.PartitionID == 0 && y.PartitionID != 0))
&&
((pf.PricePolicyID == y.PricePolicyID) || (pf.PricePolicyID != 0 && y.PricePolicyID == 0) || (pf.PricePolicyID == 0 && y.PricePolicyID != 0))
&&
((pf.PricePolicyEntityID == y.PricePolicyEntityID) || (pf.PricePolicyEntityID != 0 && y.PricePolicyEntityID == 0) || (pf.PricePolicyEntityID == 0 && y.PricePolicyEntityID != 0))
select pf);
}
query = (from p in query
join y in productAtibutesQuery.GroupBy(p=>p.ProductID)
.Select(
a => new
{
ProductID = a.Key
}
)
on p.ProductID equals y.ProductID
select p);
return await query.CountAsync();
Maybe this happens because of multple OR conditions. I don't know how to write better the some sentences like : ((pf.GroupID == y.GroupID) ||(pf.GroupID != 0 && y.GroupID == 0) || (pf.GroupID == 0 && y.GroupID != 0)), maybe it is a better way.
The Project is a real estate project like booking.com where you need to do some filtering to Products . Filters Will be in left Side , products in the right.
Example:
We Need 3 Query Types:
- Count the Products based on Selected Filters ( This is the example i put in this question ) . We need to improve this one. The others are similar.
- Display Products ( Hotels ) with Pagination
- Display the Filters based on the selected ones (If the selection is: Property type Apartment => only available Filters that matches with products)
**1. Count the Products **, Based on Selected Filters : 2 Filters Selected - Need this query for Pagination
Executed DbCommand (252ms)
SELECT COUNT(*)
FROM [Products] AS [p]
INNER JOIN (
SELECT [v].[ProductID]
FROM [ViewAdminProductAtributeV2HintView] AS [v] WITH (NOEXPAND)
inner hash join [ViewAdminProductAtributeV2HintView] AS [v0] WITH (NOEXPAND) ON [v].[ProductID] = [v0].[ProductID]
inner hash join [ViewAdminProductAtributeV2HintView] AS [v1] WITH (NOEXPAND) ON [v].[ProductID] = [v1].[ProductID]
WHERE (((((([v0].[AtributeID] = @__atributeID_3) AND [v0].[AtributeValueID] IN (596)) AND ((([v].[GroupID] = 0) OR ([v0].[GroupID] = 0)) OR ([v].[GroupID] = [v0].[GroupID]))) AND ((([v].[PartitionID] = 0) OR ([v0].[PartitionID] = 0)) OR ([v].[PartitionID] = [v0].[PartitionID]))) AND ((([v].[PricePolicyID] = 0) OR ([v0].[PricePolicyID] = 0)) OR ([v].[PricePolicyID] = [v0].[PricePolicyID]))) AND ((([v].[PricePolicyEntityID] = 0) OR ([v0].[PricePolicyEntityID] = 0)) OR ([v].[PricePolicyEntityID] = [v0].[PricePolicyEntityID]))) AND (((((([v1].[AtributeID] = @__atributeID_5) AND [v1].[AtributeValueID] IN (599)) AND ((([v].[GroupID] = 0) OR ([v1].[GroupID] = 0)) OR ([v].[GroupID] = [v1].[GroupID]))) AND ((([v].[PartitionID] = 0) OR ([v1].[PartitionID] = 0)) OR ([v].[PartitionID] = [v1].[PartitionID]))) AND ((([v].[PricePolicyID] = 0) OR ([v1].[PricePolicyID] = 0)) OR ([v].[PricePolicyID] = [v1].[PricePolicyID]))) AND ((([v].[PricePolicyEntityID] = 0) OR ([v1].[PricePolicyEntityID] = 0)) OR ([v].[PricePolicyEntityID] = [v1].[PricePolicyEntityID])))
GROUP BY [v].[ProductID]
) AS [t] ON [p].[ProductID] = [t].[ProductID]
WHERE ([p].[ProductCategoryID] = @__ProductCategoryID_1
****2. Display Products**, Based on Selected Filters : 2 Filters Selected **
Executed DbCommand (246ms)
SELECT [p0].[ProductID], [p0].[Title], [p0].[LastUpdated], [p0].[ProductCategoryID], [p0].[Importance], COALESCE((
SELECT MIN([p].[Price])
FROM [ProductPricePolicies] AS [p]
WHERE ([p0].[ProductID] = [p].[ProductID]) AND ([p].[IsMainUnitPrice] = CAST(1 AS bit))), 0.0) AS [PriceMin], [p0].[UrlNice], [p0].[CultureID], [p0].[ProductIDP]
FROM [Products] AS [p0]
INNER JOIN (
SELECT [v].[ProductID]
FROM [ViewAdminProductAtributeV2HintView] AS [v] WITH (NOEXPAND)
inner hash join [ViewAdminProductAtributeV2HintView] AS [v0] WITH (NOEXPAND) ON [v].[ProductID] = [v0].[ProductID]
inner hash join [ViewAdminProductAtributeV2HintView] AS [v1] WITH (NOEXPAND) ON [v].[ProductID] = [v1].[ProductID]
WHERE (((((([v0].[AtributeID] = @__atributeID_3) AND [v0].[AtributeValueID] IN (596)) AND ((([v].[GroupID] = 0) OR ([v0].[GroupID] = 0)) OR ([v].[GroupID] = [v0].[GroupID]))) AND ((([v].[PartitionID] = 0) OR ([v0].[PartitionID] = 0)) OR ([v].[PartitionID] = [v0].[PartitionID]))) AND ((([v].[PricePolicyID] = 0) OR ([v0].[PricePolicyID] = 0)) OR ([v].[PricePolicyID] = [v0].[PricePolicyID]))) AND ((([v].[PricePolicyEntityID] = 0) OR ([v0].[PricePolicyEntityID] = 0)) OR ([v].[PricePolicyEntityID] = [v0].[PricePolicyEntityID]))) AND (((((([v1].[AtributeID] = @__atributeID_5) AND [v1].[AtributeValueID] IN (599)) AND ((([v].[GroupID] = 0) OR ([v1].[GroupID] = 0)) OR ([v].[GroupID] = [v1].[GroupID]))) AND ((([v].[PartitionID] = 0) OR ([v1].[PartitionID] = 0)) OR ([v].[PartitionID] = [v1].[PartitionID]))) AND ((([v].[PricePolicyID] = 0) OR ([v1].[PricePolicyID] = 0)) OR ([v].[PricePolicyID] = [v1].[PricePolicyID]))) AND ((([v].[PricePolicyEntityID] = 0) OR ([v1].[PricePolicyEntityID] = 0)) OR ([v].[PricePolicyEntityID] = [v1].[PricePolicyEntityID])))
GROUP BY [v].[ProductID]
) AS [t] ON [p0].[ProductID] = [t].[ProductID]
WHERE ([p0].[ProductCategoryID] = @__ProductCategoryID_1)
ORDER BY [p0].[Importance], [p0].[ProductID] DESC
OFFSET 0 ROWS FETCH NEXT @__p_7 ROWS ONLY
3. Display the Filters based on the selected ones 2 Filters ( From Left )
Executed DbCommand (295ms)
SELECT [a].[AtributeValueID], [a].[Title], [a].[Importance], [a].[AtributeID], [t0].[c] AS [Count]
FROM [AtributeValues] AS [a]
INNER JOIN (
SELECT [t].[AtributeValueID], COUNT(*) AS [c]
FROM (
SELECT [v].[AtributeValueID], [v].[ProductID]
FROM [ViewAdminProductAtributeV2HintView] AS [v] WITH (NOEXPAND)
inner hash join [ViewAdminProductAtributeV2HintView] AS [v0] WITH (NOEXPAND) ON [v].[ProductID] = [v0].[ProductID]
inner hash join [ViewAdminProductAtributeV2HintView] AS [v1] WITH (NOEXPAND) ON [v].[ProductID] = [v1].[ProductID]
WHERE ((((([v].[ProductCategoryID] = @__ProductCategoryID_0))) AND ([v].[IsPublished] = CAST(1 AS bit))) AND (((((([v0].[AtributeID] = @__atributeID_1) AND (([v].[AtributeValueID] = [v0].[AtributeValueID]) OR [v0].[AtributeValueID] IN (596))) AND ((([v].[GroupID] = 0) OR ([v0].[GroupID] = 0)) OR ([v].[GroupID] = [v0].[GroupID]))) AND ((([v].[PartitionID] = 0) OR ([v0].[PartitionID] = 0)) OR ([v].[PartitionID] = [v0].[PartitionID]))) AND ((([v].[PricePolicyID] = 0) OR ([v0].[PricePolicyID] = 0)) OR ([v].[PricePolicyID] = [v0].[PricePolicyID]))) AND ((([v].[PricePolicyEntityID] = 0) OR ([v0].[PricePolicyEntityID] = 0)) OR ([v].[PricePolicyEntityID] = [v0].[PricePolicyEntityID])))) AND (((((([v1].[AtributeID] = @__atributeID_3) AND (([v].[AtributeValueID] = [v1].[AtributeValueID]) OR [v1].[AtributeValueID] IN (599))) AND ((([v].[GroupID] = 0) OR ([v1].[GroupID] = 0)) OR ([v].[GroupID] = [v1].[GroupID]))) AND ((([v].[PartitionID] = 0) OR ([v1].[PartitionID] = 0)) OR ([v].[PartitionID] = [v1].[PartitionID]))) AND ((([v].[PricePolicyID] = 0) OR ([v1].[PricePolicyID] = 0)) OR ([v].[PricePolicyID] = [v1].[PricePolicyID]))) AND ((([v].[PricePolicyEntityID] = 0) OR ([v1].[PricePolicyEntityID] = 0)) OR ([v].[PricePolicyEntityID] = [v1].[PricePolicyEntityID])))
GROUP BY [v].[ProductID], [v].[AtributeValueID]
) AS [t]
GROUP BY [t].[AtributeValueID]
) AS [t0] ON [a].[AtributeValueID] = [t0].[AtributeValueID]
We build those types of project with Filtration since 2009, but never find a solution or database structure to work smoothly for how many filters the user want.
We are open to create another Views, or everything is necessary.
Filter Examples:
Star Rating :1 star ( 10 Results) , 2 stars ( 12 Results) , 3 stars( 2 Results) ,etc
Property Type: Apartments ( 20 Results), Hotels (30 Results), etc
When a Filter will be selected all the Filters should be recalculated to count again the results you will get
Database Structure
Products (ProductID, Title,etc): Hotel 1, Hotel 2
Atributes (AtrivuteValueID,Title) : Star Rating, Property Type
AtributeValues (AtrivuteValueID,AtributeID,Title): Apartments, Hotels, etc
ProductAtributes (ProductID, AtributeID, AtributeValueID)
[dbo].[ViewAdminProductAtributeV2HintView]: 42487 Rows in View . We have made this View because in the ProductAtributes tabe has arround : 400000 Rows, from where just 42487 are Filters. And i am thinking to improve the searches .
I have attached the Execution Plan
Execution Plan in XML :
20090-sqlexecutionplan.xml
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(134.4, 68%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ELP%3C/text%3E%3C/svg%3E)
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(73.60000000000001, 21%, 17%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDB%3C/text%3E%3C/svg%3E)
