How To Improve SQL Query Speed from 4 seconds to maximum 500 ms in Net Core 3.1 in a filtering situation, SQL Server Enterprise 2019, Windows Azure

Question

We need to improve some queries into a Real Estate Project ~ similar with booking.com where we need to do some filtering to Products . Filters Will be in left Side , products in the right.

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

Project Specifications: Net Core 3.1, Entity Core, 1 Server Windows 2019 SQL Server Enterprise 2019 (4 CPU, 16 GB RAM) , 1 Windows Server Azure , SSD

Database Structure

• Products (ProductID, Title,etc): Hotel 1, Hotel 2 : 1400 Items in Table

• Atributes (AtrivuteValueID,Title) : Star Rating, Property Type : 255 Items in Table

• AtributeValues (AtrivuteValueID,AtributeID,Title): Apartments, Hotels, 1, 2, etc : 1025 Items in Table

• ProductAtributes(ProductAtributeID,ProductID,AtributeID,AtributeValueID) : 140000 Items in Table

• Indexed View: ViewAdminProductAtributes : 11900 Items in View

      CREATE VIEW[dbo].[ViewAdminProductAtributeHintView] with SCHEMABINDING AS
  select[pa].[ProductAtributeID], [a].[AtributeID], [av].[AtributeValueID],[pa].[ProductID],
  [pa].[GroupID],[pa].[PartitionID],[pa].[PricePolicyID],[pa].[PricePolicyEntityID],
  [pa].[ProductCategoryID],[pa].[ProductCategoryIDP],[pa].[ProductCategoryIDPP],[pa].[IsPublished]         
  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].[ViewAdminProductAtributeHintView]( [ProductAtributeID] ASC)
  CREATE NONCLUSTERED INDEX[IX_NC_AdminProductAtributesFiltersHintView_AtributeValueID] ON[dbo].[ViewAdminProductAtributeHintView]
  ([AtributeValueID] ASC)Include([ProductID] , [AtributeID], [GroupID], [PartitionID], [PricePolicyID], [PricePolicyEntityID], [ProductCategoryID], [ProductCategoryIDP], [ProductCategoryIDPP], [IsPublished])
  

When the user select only 1-3 Filters The query time is ~ 300 ms, but after 4 Filters the query time is ~ 4 seconds .

We need this query time to be under 800 ms for ~ 10 selected Filters

We have Tried Everything : Indexing the View, query the view with (NOEXPAND), replace distinct with Group By, Testing the Live Execution Plan ( We have Seek Everywhere ), but nothing works .

The Query Time is The same in SQL Management Studio ( 4 Seconds ), so there is not a problem with EF Core.

The Server has enough Resources, when a user filter results there is no big changes in CPU ( 3 % ) or RAM Memory .

Is there anything we can do with SQL Server ? Some Optimizations ?

The queries for Filtering the Products works Great : (206ms)

 Executed DbCommand (206ms) [Parameters=[@__atributeID_3='?' (DbType = Int32), @__atributeID_5='?' (DbType = Int32), @__atributeID_7='?' (DbType = Int32), @__atributeID_9='?' (DbType = Int32), @__atributeID_11='?' (DbType = Int32), @__atributeID_13='?' (DbType = Int32), @__ProductCategoryID_1='?' (DbType = Int32), @__IsPublished_2='?' (DbType = Boolean), @__p_15='?' (DbType = Int32), @__p_16='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
  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 DISTINCT [v].[ProductID]
      FROM [ViewAdminProductAtributeHintView] AS [v] WITH (NOEXPAND)
      INNER JOIN [ViewAdminProductAtributeHintView] AS [v0] WITH (NOEXPAND) ON [v].[ProductID] = [v0].[ProductID]
      INNER JOIN [ViewAdminProductAtributeHintView] AS [v1] WITH (NOEXPAND) ON [v].[ProductID] = [v1].[ProductID]
      INNER JOIN [ViewAdminProductAtributeHintView] AS [v2] WITH (NOEXPAND) ON [v].[ProductID] = [v2].[ProductID]
      INNER JOIN [ViewAdminProductAtributeHintView] AS [v3] WITH (NOEXPAND) ON [v].[ProductID] = [v3].[ProductID]
      INNER JOIN [ViewAdminProductAtributeHintView] AS [v4] WITH (NOEXPAND) ON [v].[ProductID] = [v4].[ProductID]
      INNER JOIN [ViewAdminProductAtributeHintView] AS [v5] WITH (NOEXPAND) ON [v].[ProductID] = [v5].[ProductID]
      WHERE (((((((((([v0].[AtributeID] = @__atributeID_3) AND [v0].[AtributeValueID] IN (224)) AND (((([v].[GroupID] = [v0].[GroupID]) OR ([v].[GroupID] IS NULL AND [v0].[GroupID] IS NULL)) OR ([v].[GroupID] IS NOT NULL AND [v0].[GroupID] IS NULL)) OR ([v].[GroupID] IS NULL AND [v0].[GroupID] IS NOT NULL))) AND (((([v].[PartitionID] = [v0].[PartitionID]) OR ([v].[PartitionID] IS NULL AND [v0].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NOT NULL AND [v0].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NULL AND [v0].[PartitionID] IS NOT NULL))) AND (((([v].[PricePolicyID] = [v0].[PricePolicyID]) OR ([v].[PricePolicyID] IS NULL AND [v0].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NOT NULL AND [v0].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NULL AND [v0].[PricePolicyID] IS NOT NULL))) AND (((([v].[PricePolicyEntityID] = [v0].[PricePolicyEntityID]) OR ([v].[PricePolicyEntityID] IS NULL AND [v0].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NOT NULL AND [v0].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NULL AND [v0].[PricePolicyEntityID] IS NOT NULL))) AND (((((([v1].[AtributeID] = @__atributeID_5) AND [v1].[AtributeValueID] IN (307)) AND (((([v].[GroupID] = [v1].[GroupID]) OR ([v].[GroupID] IS NULL AND [v1].[GroupID] IS NULL)) OR ([v].[GroupID] IS NOT NULL AND [v1].[GroupID] IS NULL)) OR ([v].[GroupID] IS NULL AND [v1].[GroupID] IS NOT NULL))) AND (((([v].[PartitionID] = [v1].[PartitionID]) OR ([v].[PartitionID] IS NULL AND [v1].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NOT NULL AND [v1].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NULL AND [v1].[PartitionID] IS NOT NULL))) AND (((([v].[PricePolicyID] = [v1].[PricePolicyID]) OR ([v].[PricePolicyID] IS NULL AND [v1].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NOT NULL AND [v1].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NULL AND [v1].[PricePolicyID] IS NOT NULL))) AND (((([v].[PricePolicyEntityID] = [v1].[PricePolicyEntityID]) OR ([v].[PricePolicyEntityID] IS NULL AND [v1].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NOT NULL AND [v1].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NULL AND [v1].[PricePolicyEntityID] IS NOT NULL)))) AND (((((([v2].[AtributeID] = @__atributeID_7) AND [v2].[AtributeValueID] IN (371)) AND (((([v].[GroupID] = [v2].[GroupID]) OR ([v].[GroupID] IS NULL AND [v2].[GroupID] IS NULL)) OR ([v].[GroupID] IS NOT NULL AND [v2].[GroupID] IS NULL)) OR ([v].[GroupID] IS NULL AND [v2].[GroupID] IS NOT NULL))) AND (((([v].[PartitionID] = [v2].[PartitionID]) OR ([v].[PartitionID] IS NULL AND [v2].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NOT NULL AND [v2].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NULL AND [v2].[PartitionID] IS NOT NULL))) AND (((([v].[PricePolicyID] = [v2].[PricePolicyID]) OR ([v].[PricePolicyID] IS NULL AND [v2].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NOT NULL AND [v2].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NULL AND [v2].[PricePolicyID] IS NOT NULL))) AND (((([v].[PricePolicyEntityID] = [v2].[PricePolicyEntityID]) OR ([v].[PricePolicyEntityID] IS NULL AND [v2].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NOT NULL AND [v2].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NULL AND [v2].[PricePolicyEntityID] IS NOT NULL)))) AND (((((([v3].[AtributeID] = @__atributeID_9) AND [v3].[AtributeValueID] IN (596)) AND (((([v].[GroupID] = [v3].[GroupID]) OR ([v].[GroupID] IS NULL AND [v3].[GroupID] IS NULL)) OR ([v].[GroupID] IS NOT NULL AND [v3].[GroupID] IS NULL)) OR ([v].[GroupID] IS NULL AND [v3].[GroupID] IS NOT NULL))) AND (((([v].[PartitionID] = [v3].[PartitionID]) OR ([v].[PartitionID] IS NULL AND [v3].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NOT NULL AND [v3].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NULL AND [v3].[PartitionID] IS NOT NULL))) AND (((([v].[PricePolicyID] = [v3].[PricePolicyID]) OR ([v].[PricePolicyID] IS NULL AND [v3].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NOT NULL AND [v3].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NULL AND [v3].[PricePolicyID] IS NOT NULL))) AND (((([v].[PricePolicyEntityID] = [v3].[PricePolicyEntityID]) OR ([v].[PricePolicyEntityID] IS NULL AND [v3].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NOT NULL AND [v3].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NULL AND [v3].[PricePolicyEntityID] IS NOT NULL)))) AND (((((([v4].[AtributeID] = @__atributeID_11) AND [v4].[AtributeValueID] IN (599)) AND (((([v].[GroupID] = [v4].[GroupID]) OR ([v].[GroupID] IS NULL AND [v4].[GroupID] IS NULL)) OR ([v].[GroupID] IS NOT NULL AND [v4].[GroupID] IS NULL)) OR ([v].[GroupID] IS NULL AND [v4].[GroupID] IS NOT NULL))) AND (((([v].[PartitionID] = [v4].[PartitionID]) OR ([v].[PartitionID] IS NULL AND [v4].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NOT NULL AND [v4].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NULL AND [v4].[PartitionID] IS NOT NULL))) AND (((([v].[PricePolicyID] = [v4].[PricePolicyID]) OR ([v].[PricePolicyID] IS NULL AND [v4].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NOT NULL AND [v4].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NULL AND [v4].[PricePolicyID] IS NOT NULL))) AND (((([v].[PricePolicyEntityID] = [v4].[PricePolicyEntityID]) OR ([v].[PricePolicyEntityID] IS NULL AND [v4].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NOT NULL AND [v4].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NULL AND [v4].[PricePolicyEntityID] IS NOT NULL)))) AND (((((([v5].[AtributeID] = @__atributeID_13) AND [v5].[AtributeValueID] IN (1529)) AND (((([v].[GroupID] = [v5].[GroupID]) OR ([v].[GroupID] IS NULL AND [v5].[GroupID] IS NULL)) OR ([v].[GroupID] IS NOT NULL AND [v5].[GroupID] IS NULL)) OR ([v].[GroupID] IS NULL AND [v5].[GroupID] IS NOT NULL))) AND (((([v].[PartitionID] = [v5].[PartitionID]) OR ([v].[PartitionID] IS NULL AND [v5].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NOT NULL AND [v5].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NULL AND [v5].[PartitionID] IS NOT NULL))) AND (((([v].[PricePolicyID] = [v5].[PricePolicyID]) OR ([v].[PricePolicyID] IS NULL AND [v5].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NOT NULL AND [v5].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NULL AND [v5].[PricePolicyID] IS NOT NULL))) AND (((([v].[PricePolicyEntityID] = [v5].[PricePolicyEntityID]) OR ([v].[PricePolicyEntityID] IS NULL AND [v5].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NOT NULL AND [v5].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NULL AND [v5].[PricePolicyEntityID] IS NOT NULL)))
  ) AS [t] ON [p0].[ProductID] = [t].[ProductID]
  WHERE ([p0].[CultureID] IS NULL AND ((([p0].[ProductCategoryID] = @__ProductCategoryID_1) OR ([p0].[ProductCategoryIDP] IS NOT NULL AND ([p0].[ProductCategoryIDP] = @__ProductCategoryID_1))) OR ([p0].[ProductCategoryIDPP] IS NOT NULL AND ([p0].[ProductCategoryIDPP] = @__ProductCategoryID_1)))) AND ([p0].[IsPublished] = @__IsPublished_2)
  ORDER BY [p0].[Importance], [p0].[ProductID] DESC
  OFFSET @__p_15 ROWS FETCH NEXT @__p_16 ROWS ONLY

But , the main problem is With Get and Count the filters (AtributeValues) NEt Core Query : (3,736ms)

public static async Task<List<AtributeValue>> GetAtributesValues(this DbSet<Atribute> source, ApplicationDbContext context, List<AtributeValue> Filters, int? ProductCategoryID)
    {
        var productAtibutesQuery = context.ViewAdminProductAtributes.WithHint("NOEXPAND").AsQueryable();
        if (ProductCategoryID.HasValue)
        {
            productAtibutesQuery = productAtibutesQuery.Where(p => (p.ProductCategoryID == ProductCategoryID));
        }
        if (Filters?.Any() == true)
        {
            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 && (pf.AtributeValueID == y.AtributeValueID || atributeValues.Contains(y.AtributeValueID))
                                                &&
                                                ((pf.GroupID == y.GroupID) || (pf.GroupID != null && y.GroupID == null) || (pf.GroupID == null && y.GroupID != null))
                                                &&
                                                ((pf.PartitionID == y.PartitionID) || (pf.PartitionID != null && y.PartitionID == null) || (pf.PartitionID == null && y.PartitionID != null))
                                                &&
                                                ((pf.PricePolicyID == y.PricePolicyID) || (pf.PricePolicyID != null && y.PricePolicyID == null) || (pf.PricePolicyID == null && y.PricePolicyID != null))
                                                &&
                                                ((pf.PricePolicyEntityID == y.PricePolicyEntityID) || (pf.PricePolicyEntityID != null && y.PricePolicyEntityID == null) || (pf.PricePolicyEntityID == null && y.PricePolicyEntityID != null))
                                        select pf);
            }
        }

        var results = (from av in context.AtributeValues
                        join p in (from p in productAtibutesQuery
                                    group p by new { p.ProductID, p.AtributeValueID} into g
                                    select new
                                    {
                                        g.Key.AtributeValueID,
                                        g.Key.ProductID,
                                    }
                                    ).GroupBy(p => p.AtributeValueID)
                                        .Select
                                        (
                                            a => new
                                            {
                                                AtributeValueID = a.Key,
                                                Count = a.Count()
                                            }
                                        )
                                on av.AtributeValueID equals p.AtributeValueID
                                select new AtributeValue
                                {
                                    AtributeValueID = av.AtributeValueID,
                                    Title = av.Title,
                                    Importance = av.Importance,
                                    AtributeID = av.AtributeID,
                                    Count = p.Count
                                }
                            ).ToList();

            return results;         

    }

Translated SQL:

 Executed DbCommand (3,736ms) [Parameters=[@__ProductCategoryID_0='?' (DbType = Int32), @__atributeID_1='?' (DbType = Int32), @__atributeID_3='?' (DbType = Int32), @__atributeID_5='?' (DbType = Int32), @__atributeID_7='?' (DbType = Int32), @__atributeID_9='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
  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 [ViewAdminProductAtributeHintView] AS [v] WITH (NOEXPAND)
          INNER JOIN [ViewAdminProductAtributeHintView] AS [v0] WITH (NOEXPAND) ON [v].[ProductID] = [v0].[ProductID]
          INNER JOIN [ViewAdminProductAtributeHintView] AS [v1] WITH (NOEXPAND) ON [v].[ProductID] = [v1].[ProductID]
          INNER JOIN [ViewAdminProductAtributeHintView] AS [v2] WITH (NOEXPAND) ON [v].[ProductID] = [v2].[ProductID]
          INNER JOIN [ViewAdminProductAtributeHintView] AS [v3] WITH (NOEXPAND) ON [v].[ProductID] = [v3].[ProductID]
          INNER JOIN [ViewAdminProductAtributeHintView] AS [v4] WITH (NOEXPAND) ON [v].[ProductID] = [v4].[ProductID]
          WHERE (((((((([v].[ProductCategoryID] = @__ProductCategoryID_0) OR ([v].[ProductCategoryIDP] = @__ProductCategoryID_0)) OR ([v].[ProductCategoryIDPP] = @__ProductCategoryID_0)) AND ([v].[IsPublished] = CAST(1 AS bit))) AND (((((([v0].[AtributeID] = @__atributeID_1) AND (([v].[AtributeValueID] = [v0].[AtributeValueID]) OR [v0].[AtributeValueID] IN (307))) AND (((([v].[GroupID] = [v0].[GroupID]) OR ([v].[GroupID] IS NULL AND [v0].[GroupID] IS NULL)) OR ([v].[GroupID] IS NOT NULL AND [v0].[GroupID] IS NULL)) OR ([v].[GroupID] IS NULL AND [v0].[GroupID] IS NOT NULL))) AND (((([v].[PartitionID] = [v0].[PartitionID]) OR ([v].[PartitionID] IS NULL AND [v0].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NOT NULL AND [v0].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NULL AND [v0].[PartitionID] IS NOT NULL))) AND (((([v].[PricePolicyID] = [v0].[PricePolicyID]) OR ([v].[PricePolicyID] IS NULL AND [v0].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NOT NULL AND [v0].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NULL AND [v0].[PricePolicyID] IS NOT NULL))) AND (((([v].[PricePolicyEntityID] = [v0].[PricePolicyEntityID]) OR ([v].[PricePolicyEntityID] IS NULL AND [v0].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NOT NULL AND [v0].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NULL AND [v0].[PricePolicyEntityID] IS NOT NULL)))) AND (((((([v1].[AtributeID] = @__atributeID_3) AND (([v].[AtributeValueID] = [v1].[AtributeValueID]) OR [v1].[AtributeValueID] IN (371))) AND (((([v].[GroupID] = [v1].[GroupID]) OR ([v].[GroupID] IS NULL AND [v1].[GroupID] IS NULL)) OR ([v].[GroupID] IS NOT NULL AND [v1].[GroupID] IS NULL)) OR ([v].[GroupID] IS NULL AND [v1].[GroupID] IS NOT NULL))) AND (((([v].[PartitionID] = [v1].[PartitionID]) OR ([v].[PartitionID] IS NULL AND [v1].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NOT NULL AND [v1].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NULL AND [v1].[PartitionID] IS NOT NULL))) AND (((([v].[PricePolicyID] = [v1].[PricePolicyID]) OR ([v].[PricePolicyID] IS NULL AND [v1].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NOT NULL AND [v1].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NULL AND [v1].[PricePolicyID] IS NOT NULL))) AND (((([v].[PricePolicyEntityID] = [v1].[PricePolicyEntityID]) OR ([v].[PricePolicyEntityID] IS NULL AND [v1].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NOT NULL AND [v1].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NULL AND [v1].[PricePolicyEntityID] IS NOT NULL)))) AND (((((([v2].[AtributeID] = @__atributeID_5) AND (([v].[AtributeValueID] = [v2].[AtributeValueID]) OR [v2].[AtributeValueID] IN (596))) AND (((([v].[GroupID] = [v2].[GroupID]) OR ([v].[GroupID] IS NULL AND [v2].[GroupID] IS NULL)) OR ([v].[GroupID] IS NOT NULL AND [v2].[GroupID] IS NULL)) OR ([v].[GroupID] IS NULL AND [v2].[GroupID] IS NOT NULL))) AND (((([v].[PartitionID] = [v2].[PartitionID]) OR ([v].[PartitionID] IS NULL AND [v2].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NOT NULL AND [v2].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NULL AND [v2].[PartitionID] IS NOT NULL))) AND (((([v].[PricePolicyID] = [v2].[PricePolicyID]) OR ([v].[PricePolicyID] IS NULL AND [v2].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NOT NULL AND [v2].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NULL AND [v2].[PricePolicyID] IS NOT NULL))) AND (((([v].[PricePolicyEntityID] = [v2].[PricePolicyEntityID]) OR ([v].[PricePolicyEntityID] IS NULL AND [v2].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NOT NULL AND [v2].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NULL AND [v2].[PricePolicyEntityID] IS NOT NULL)))) AND (((((([v3].[AtributeID] = @__atributeID_7) AND (([v].[AtributeValueID] = [v3].[AtributeValueID]) OR [v3].[AtributeValueID] IN (599))) AND (((([v].[GroupID] = [v3].[GroupID]) OR ([v].[GroupID] IS NULL AND [v3].[GroupID] IS NULL)) OR ([v].[GroupID] IS NOT NULL AND [v3].[GroupID] IS NULL)) OR ([v].[GroupID] IS NULL AND [v3].[GroupID] IS NOT NULL))) AND (((([v].[PartitionID] = [v3].[PartitionID]) OR ([v].[PartitionID] IS NULL AND [v3].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NOT NULL AND [v3].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NULL AND [v3].[PartitionID] IS NOT NULL))) AND (((([v].[PricePolicyID] = [v3].[PricePolicyID]) OR ([v].[PricePolicyID] IS NULL AND [v3].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NOT NULL AND [v3].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NULL AND [v3].[PricePolicyID] IS NOT NULL))) AND (((([v].[PricePolicyEntityID] = [v3].[PricePolicyEntityID]) OR ([v].[PricePolicyEntityID] IS NULL AND [v3].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NOT NULL AND [v3].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NULL AND [v3].[PricePolicyEntityID] IS NOT NULL)))) AND (((((([v4].[AtributeID] = @__atributeID_9) AND (([v].[AtributeValueID] = [v4].[AtributeValueID]) OR [v4].[AtributeValueID] IN (1529))) AND (((([v].[GroupID] = [v4].[GroupID]) OR ([v].[GroupID] IS NULL AND [v4].[GroupID] IS NULL)) OR ([v].[GroupID] IS NOT NULL AND [v4].[GroupID] IS NULL)) OR ([v].[GroupID] IS NULL AND [v4].[GroupID] IS NOT NULL))) AND (((([v].[PartitionID] = [v4].[PartitionID]) OR ([v].[PartitionID] IS NULL AND [v4].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NOT NULL AND [v4].[PartitionID] IS NULL)) OR ([v].[PartitionID] IS NULL AND [v4].[PartitionID] IS NOT NULL))) AND (((([v].[PricePolicyID] = [v4].[PricePolicyID]) OR ([v].[PricePolicyID] IS NULL AND [v4].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NOT NULL AND [v4].[PricePolicyID] IS NULL)) OR ([v].[PricePolicyID] IS NULL AND [v4].[PricePolicyID] IS NOT NULL))) AND (((([v].[PricePolicyEntityID] = [v4].[PricePolicyEntityID]) OR ([v].[PricePolicyEntityID] IS NULL AND [v4].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NOT NULL AND [v4].[PricePolicyEntityID] IS NULL)) OR ([v].[PricePolicyEntityID] IS NULL AND [v4].[PricePolicyEntityID] IS NOT NULL)))
          GROUP BY [v].[ProductID], [v].[AtributeValueID]
      ) AS [t]
      GROUP BY [t].[AtributeValueID]
  ) AS [t0] ON [a].[AtributeValueID] = [t0].[AtributeValueID]

Execution Plan ( 2 Filters ) - Seems to look OK .

Execution Plan ( 4-5 Filters ) : It Looks BAD