0

Using: MS SQL SERVER 2008R2 to 2016

I have a stored procedure (simplified version below) which returns a list of products filtered by any of several parameters. The data I return only comes from the primary table (Products) but can be filtered by cross referenced data in any of the other tables.

The code below will function, however efficiency wise if a parameter is not supplied for a join, the server still takes the efficiency hit of linking to that table, even though the data implies there is no filtering required.

I can build this in dynamic sql and add the joins dependant on the parameters, but I'm trying to avoid dynamic as much as possible - it always becomes difficult to maintain.

Does anyone have any better solutions that they have used or can think of? I do have several working options, but looking at the code I feel there has to be a better option.

Many thanks.

CREATE PROC FilteredProducts
    @CatalogueID INT = NULL
,   @ManufacturerID INT = NULL
,   @BrandID INT = NULL
AS
    BEGIN
        SELECT  Products.*
        FROM    Products P
        JOIN    Catalogue C ON P.ID = C.ProductID
                               AND (@CatalogueID IS NULL
                                    OR C.ID = @CatalogueID
                                   )
        JOIN    Manufacturers M ON P.ID = M.Products_ID
                                   AND (@ManufacturerID IS NULL
                                        OR M.ID = @ManufacturerID
                                       )
        JOIN    Brands B ON P.ID = B.Products_ID
                            AND (@BrandID IS NULL
                                 OR B.ID = @BrandID
                                )

    END

Edit:

Added example of code,

DECLARE @InstockOnly AS BIT = 0

SELECT  AP.*
FROM    ABCD.AllowedProducts AP
JOIN    ABCD.DatabaseStockLevels DSL ON DSL.ProductGUID = AP.ProductGUID
                                        AND (@InstockOnly = 0
                                             OR DSL.StockLevel > 0
                                            )
OPTION  (RECOMPILE)

When executing the code above to filter specifically by stock level the actual execution plan shows that the join is evaluated despite having InstockOnly = 0. Although my data is a 1 to 1 relationship, SQL Server doesn't know that, so the join is performed in case there is a 1 to many relationship. While the option (recompile) does simplify the join, I'm looking for a way to remove the join completely.

Matthew Baker
  • 2,637
  • 4
  • 24
  • 49
  • `OPTION(RECOMPILE)` – Martin Smith Jan 08 '18 at 08:37
  • @MartinSmith - Hi Martin, I'd already looked at the option recompile, but that isn't the issue I'm looking to solve. If as an example the parameter ManufacturerID is null, the option recompile won't apply the condition, but will still join to that table. I'm trying to not compile the join at all. This is a different issue to the answer you have compared this too. Unfortunately when I've been searching for the solution, the one above is what I've been getting back anyway. If you could remove the 'duplicate' flag, it would be greatly appreciated. Many thanks. – Matthew Baker Jan 08 '18 at 09:26
  • Have you tried `OPTION(RECOMPILE)` and looked at the actual plan (not estimated)? – Martin Smith Jan 08 '18 at 09:43
  • @MartinSmith - Added more detail to the question - I see where your aiming - and it may work if I use option recompile and then a UDF which is great if I'm by passing the code, but horrible performance if its used. – Matthew Baker Jan 08 '18 at 10:48
  • I think that your code **forces** the evaluation (of the `JOIN`'s) regardless of the presence of values for the parameters. Did you consider the user of dynamic query? True, it introduces some overhead, but in your case you may just find it most suitable. – FDavidov Jan 08 '18 at 12:47
  • 1
    Read Erland's **lengthy** discussion [here](http://www.sommarskog.se/dyn-search.html). And bookmark that website so you can read the other parts as well. – SMor Jan 08 '18 at 14:13
  • @FDavidov I think Dynamic is going to be the most efficient way to do it, but I was trying to avoid it if possible. While I'm comfortable with Dynamic SQL long term support on Dynamic tends to get messy. I try to avoid it where ever possible. I was always told to develop your code like its going to be tested, debugged and maintained by a 6'4" maniac with an axe! – Matthew Baker Jan 08 '18 at 14:13
  • @SMor Is a great page of info there. Not in my book marks, but came across it when researching anyway. Still felt there had to be a better way of doing things than the options I had. A +1 from me for it though, I bet it comes in useful for anyone else that finds this thread in future. – Matthew Baker Jan 08 '18 at 14:16
  • Matthew, Dynamic SQL is available in most DBMSs I know, and that is for a reason. True, it should be used with caution, but there are cases that it (paradoxically) is the most efficient and elegant way to solve a problem. So, as long as you are using it properly, don't be afraid of tall and scary people :-). – FDavidov Jan 09 '18 at 07:56

0 Answers0