none
How to solve in SQL Server sintax product selection by multiple filters selections in Online Stores

    Question

  • Hello,

    I need some advice to optimise my store procedures when I try to select products when the users select multiple filters from User View , because my queries takes too long time when I execute them .

    For Example : Show all products with colour : Red and Blue and Size : 12px. (Multi Selection)

    In my online store this Filters are can be created by administrator , so I decided to have a table : Filters that contains filters like Colours , Sisez .

    The table FilterElements contains : filter values like : Red , Blue, 12px , 13 px.

    The table ProductFilters contains the asociations from FilterElements and Products : for example: Product 1, Red , 12 px .

     

    I have made a convention (there can't be more then 15 filters selected 1 time) and I tried the next query, but is very slow because of joins:

     ALTER PROCEDURE dbo.StoredProcedureGetProductsByProductFilters    
    (
        @FilterElementID1 int = 0,
        @FilterElementID2 int = 0,
        @FilterElementID3 int = 0,
        @FilterElementID4 int = 0    
    )


    AS

    Select * from Products where ProductID in
        (

            select distinct p.ProductID from Products p
            left outer join ProductFilters pf1 on pf1.ProductID=p.ProductID
            left outer join ProductFilters pf2 on pf2.ProductID=p.ProductID
            left outer join ProductFilters pf3 on pf3.ProductID=p.ProductID
            left outer join ProductFilters pf4 on pf4.ProductID=p.ProductID
            where              

            (@FilterElementID1= 0 or pf1.FilterElementID = @FilterElementID1 )

            and

            (@FilterElementID2= 0 or pf2.FilterElementID = @FilterElementID2 )

            and

            (@FilterElementID3= 0 or pf3.FilterElementID = @FilterElementID2 )

            and

            (@FilterElementID4= 0 or pf4.FilterElementID = @FilterElementID2 )

        )

        RETURN

    Can somebody help me ?

                                          
    • Changed type Naomi NModerator Tuesday, August 28, 2012 2:15 PM Question rather than discussion
    Tuesday, August 28, 2012 1:44 PM

Answers

  • If you need all filters matched, then you need to use AND condition for checking for each filter if you keep your original solution, e.g.

    select P.* from Products where (@Filter1 = 0 or exists (...)) AND (@Filter2 = 0 or exists (...)) 

    ------------------------

    In other words, you need to properly combine filters - say, for colors you need or (to pick up either red or blue) + condition for size. In other words, filters of the same "type" need to be IN (@Color1, @Color2, ...) AND Size IN (@Size1, @Size2,...) 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Lucian P. _ Wednesday, August 29, 2012 3:34 PM
    Wednesday, August 29, 2012 12:45 PM
    Moderator

All replies

  • Perhaps something like this:

    select distinct p.ProductID
    from Products p
    left outer join ProductFilters pf1 
      on pf1.ProductID=p.ProductID 
     and pf1.filterElementId = nullif(@filterElementId1, 0)
    left outer join ProductFilters pf2 
      on pf2.ProductID=p.ProductID 
     and pf2.filterElementId = nullif(@filterElementId2, 0)
    left outer join ProductFilters pf3 
      on pf3.ProductID=p.ProductID 
     and pf3.filterElementId = nullif(@filterElementId3, 0)
    left outer join ProductFilters pf4 
      on pf4.ProductID=p.ProductID 
     and pf4.filterElementId = nullif(@filterElementId4, 0)
    where (@FilterElementID1= 0 or pf1.FilterElementID = @FilterElementID1 )
      and (@FilterElementID2= 0 or pf2.FilterElementID = @FilterElementID2 )
      and (@FilterElementID3= 0 or pf3.FilterElementID = @FilterElementID2 )
      and (@FilterElementID4= 0 or pf4.FilterElementID = @FilterElementID2 )
    I just doubt that the DISTINCT with all of these LEFT JOINs is going to give the performance that you want.  Most likely you will need to resort to dynamic SQL.  There is a good chance that some dynamic SQL suggestions will follow.


    Tuesday, August 28, 2012 2:09 PM
    Moderator
  • 1. I suggest to switch to use EXISTS and also depending on your SQL Server version you may have two solutions - in SQL 2008 SP2 and later add OPTION (RECOMPILE) to the query. Otherwise dynamic SQL may be a preferred solution.

    So, I would write this as

    Select * from Products p where 
    
    (@FilterElementID1 = 0 or exists (select 1 from
    ProductFilters pf1 where pf1.ProductID = p.ProductID
    and pf1.FilterElementID = @FilterElementID1)
    ))
    etc. for other filters
    
    OPTION (RECOMPILE)
        RETURN

    However, may be you can just pass all filters as a list of filters? Do you need to select rows matching all passed filters or just one of the passed filters?

    See also this blog post

    How to search for all words inclusive without using Full Text search

    if you need to match all filters.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, August 28, 2012 2:22 PM
    Moderator
  • Hello,

    Thank's for your answers .

    I have tested Naomi Solutions and it is ok , the query is very fast but can only select some query like : I need all products with colour Red and size 10px (User has only Drop Down List).

    When I made tests for : I need all products with colour = (Red or Blue) and Size = 10px , there are 0 row(s) returned .

    (In this case user has a multi selection on colour filter)

    So I need to match all passed filters .

    Thank's


    • Edited by Lucian P. _ Wednesday, August 29, 2012 12:42 PM
    Wednesday, August 29, 2012 12:35 PM
  • If you need all filters matched, then you need to use AND condition for checking for each filter if you keep your original solution, e.g.

    select P.* from Products where (@Filter1 = 0 or exists (...)) AND (@Filter2 = 0 or exists (...)) 

    ------------------------

    In other words, you need to properly combine filters - say, for colors you need or (to pick up either red or blue) + condition for size. In other words, filters of the same "type" need to be IN (@Color1, @Color2, ...) AND Size IN (@Size1, @Size2,...) 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Lucian P. _ Wednesday, August 29, 2012 3:34 PM
    Wednesday, August 29, 2012 12:45 PM
    Moderator
  • Hello,

    Thank's Naomi , now it is ok .

    My final solution is : Group Filters from Filters like Colours in (FilterElementIDA1 , FilterElementIDA2...) , Filters from Size in (FilterElementIDB1, FilterElementIDB2... )


    ALTER PROCEDURE [dbo].[StoredProcedureGetProductsByProductFilters2]    
    (
        @FilterElementIDA1 int = 1,
        @FilterElementIDA2 int = 0,
        @FilterElementIDA3 int = 0,
        @FilterElementIDB1 int = 0,
        @FilterElementIDB2 int = 0,
        @FilterElementIDB3 int = 0
          
    )
     
    AS
     declare @isFilterElementASelected int =0
     declare @isFilterElementBSelected int =0

    if (@FilterElementIDA1<>0 or @FilterElementIDA2<>0 or @FilterElementIDA3<>0)
       set @isFilterElementASelected = 1   

       if (@FilterElementIDB1<>0 or @FilterElementIDB2<>0 or @FilterElementIDB3<>0)
       set @isFilterElementBSelected = 1   

    Select * from Products p where  
                   (@isFilterElementASelected = 0  
                        or exists (
                            select 1 from
                            ProductFilters pf1 where pf1.ProductID = p.ProductID
                            and (pf1.FilterElementID = @FilterElementIDA1 or pf1.FilterElementID = @FilterElementIDA2 or pf1.FilterElementID = @FilterElementIDA3    )                        
                                
                            )                                                  
                    )        
                    
                and
                
                (@isFilterElementBSelected = 0 or exists (select 1 from
                    ProductFilters pf2 where pf2.ProductID = p.ProductID
                    and pf2.FilterElementID = @FilterElementIDB1  or pf2.FilterElementID = @FilterElementIDB2 or pf2.FilterElementID = @FilterElementIDB3)
                 )

        OPTION (RECOMPILE)                        
                                    
        RETURN

     

    Wednesday, August 29, 2012 3:34 PM