Improve the execution time for a complex SQL Query from 25 seconds to be under 1 second in SQL Server Enterprise 2019

Question

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

Answer 1

Hi @Lucian Parvu ,

Thanks for your update.

Glad to hear that you have a huge enhancement with your query using exists.

In addition, there are some more tips which may be helpful to you.

1. Update statistics.
2. Use trace flag 2371.
3. Try with Hash Join, Merge Join and Nested Loop Join and validate which could be faster.
4. Check whether it is possible to remove the sort(group by/order by/distinct).

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa

Answer 2

Hi @MelissaMa-MSFT

We have updated, Thank's

Until we will make the Flat Table (as your colleague @Tom Phillips suggested) , we have tried your suggestion with EXISTS, and there is a huge difference, now almost everything is ~1 second, except the Query for Listing Attributes and Count Available Products for each Filter, based on the selected filters some time it takes : 2 Seconds , and I think the difference is the Group By ProductID, AtributeValueID

Please see the Queries Bellow:

1. The Query : Count Products and Display Products seems to execute under 1 second .

   SELECT COUNT(*)  
     FROM [Products] AS [p]  
     WHERE [p].[ProductCategoryID] = @__ProductCategoryID_1  
     AND EXISTS (  
          SELECT 1  
         FROM [ViewAdminProductAtributeV2HintView] AS [v] WITH (NOEXPAND)  
         WHERE (EXISTS (  
            SELECT 1  
              FROM [ViewAdminProductAtributeV2HintView] AS [v0] WITH (NOEXPAND)  
            WHERE (((((([v].[ProductID] = [v0].[ProductID]) AND ([v0].[AtributeID] = @__atributeID_3)) AND [v0].[AtributeValueID] IN (596)) AND (([v].[GroupID] = [v0].[GroupID]) OR (([v].[GroupID] * [v0].[GroupID]) = 0))) AND (([v].[PartitionID] = [v0].[PartitionID]) OR (([v].[PartitionID] * [v0].[PartitionID]) = 0))) AND (([v].[PricePolicyID] = [v0].[PricePolicyID]) OR (([v].[PricePolicyID] * [v0].[PricePolicyID]) = 0))) AND (([v].[PricePolicyEntityID] = [v0].[PricePolicyEntityID]) OR (([v].[PricePolicyEntityID] * [v0].[PricePolicyEntityID]) = 0)))   
           AND EXISTS (  
             SELECT 1  
              FROM [ViewAdminProductAtributeV2HintView] AS [v1] WITH (NOEXPAND)  
             WHERE (((((([v].[ProductID] = [v1].[ProductID]) AND ([v1].[AtributeID] = @__atributeID_5)) AND [v1].[AtributeValueID] IN (599)) AND (([v].[GroupID] = [v1].[GroupID]) OR (([v].[GroupID] * [v1].[GroupID]) = 0))) AND (([v].[PartitionID] = [v1].[PartitionID]) OR (([v].[PartitionID] * [v1].[PartitionID]) = 0))) AND (([v].[PricePolicyID] = [v1].[PricePolicyID]) OR (([v].[PricePolicyID] * [v1].[PricePolicyID]) = 0))) AND (([v].[PricePolicyEntityID] = [v1].[PricePolicyEntityID]) OR (([v].[PricePolicyEntityID] * [v1].[PricePolicyEntityID]) = 0))))   
      AND ([p].[ProductID] = [v].[ProductID]))  
   

2. The Query where we need to List and Count Available Products for each Filter, based on the selected filters take some time : 2 Seconds

Can be done some improvments to Exists ? Or maybe to GroupBy (in this case is like a distinct) ?

This is the Generated EF Core, where we can use only ANY Operator who is translated in Exists , but if we need we can switch to ADO.NET where we can write the query as we want

We Have notice if we add: OPTION (HASH JOIN, MERGE JOIN); There is a small improvment : arround 1 Second and HAlf . There is something else we can add as an Option ?

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)  
              WHERE [v].[ProductCategoryID] = @__ProductCategoryID_0  
              AND EXISTS (  
                   SELECT 1  
                   FROM [ViewAdminProductAtributeV2HintView] AS [v0] WITH (NOEXPAND)  
                   WHERE (((((([v].[ProductID] = [v0].[ProductID]) AND ([v0].[AtributeID] = @__atributeID_1)) AND (([v].[AtributeValueID] = [v0].[AtributeValueID]) OR [v0].[AtributeValueID] IN (596))) AND (([v].[GroupID] = [v0].[GroupID]) OR (([v].[GroupID] * [v0].[GroupID]) = 0))) AND (([v].[PartitionID] = [v0].[PartitionID]) OR (([v].[PartitionID] * [v0].[PartitionID]) = 0))) AND (([v].[PricePolicyID] = [v0].[PricePolicyID]) OR (([v].[PricePolicyID] * [v0].[PricePolicyID]) = 0))) AND (([v].[PricePolicyEntityID] = [v0].[PricePolicyEntityID]) OR (([v].[PricePolicyEntityID] * [v0].[PricePolicyEntityID]) = 0))))   
               AND EXISTS (  
                   SELECT 1  
                   FROM [ViewAdminProductAtributeV2HintView] AS [v1] WITH (NOEXPAND)  
                   WHERE (((((([v].[ProductID] = [v1].[ProductID]) AND ([v1].[AtributeID] = @__atributeID_3)) AND (([v].[AtributeValueID] = [v1].[AtributeValueID]) OR [v1].[AtributeValueID] IN (599))) AND (([v].[GroupID] = [v1].[GroupID]) OR (([v].[GroupID] * [v1].[GroupID]) = 0))) AND (([v].[PartitionID] = [v1].[PartitionID]) OR (([v].[PartitionID] * [v1].[PartitionID]) = 0))) AND (([v].[PricePolicyID] = [v1].[PricePolicyID]) OR (([v].[PricePolicyID] * [v1].[PricePolicyID]) = 0))) AND (([v].[PricePolicyEntityID] = [v1].[PricePolicyEntityID]) OR (([v].[PricePolicyEntityID] * [v1].[PricePolicyEntityID]) = 0)))  
               GROUP BY [v].[ProductID], [v].[AtributeValueID]  
           ) AS [t]  
           GROUP BY [t].[AtributeValueID]  
       ) AS [t0] ON [a].[AtributeValueID] = [t0].[AtributeValueID]