Replacing Is Null Or Exist Inner Query Logic to Reduce Stored Procedure Execution Time

user_2945231 0
Viewed 119

My stored procedure is currently using Is Null Or Exist logic combined with an inner query to filter out the records. The stored procedure is converting multiple comma-separated input values to temp tables (in the production scenario, the input record count will be much higher). And the inner query is using these temp tables for filter conditions. Due to the concern over query execution time would like to change the existing inner-query with an alternate (like left join). But need to retain the same Is Null Or Exist logic. Any suggestions?

DECLARE @SelectedOfferes varchar(1000) = 'FLT10,SPL20'
DECLARE @SelectedBrandCode varchar(1000) = '208,406'
DECLARE @CategoryCode varchar(1000) = 'GMOVN2,CELSMR,LCDTV38IN'

CREATE TABLE #SelectedOfferes 
(
    DiscountCode VARCHAR(20)
)

CREATE TABLE #BrandCode
(
    BrandCode VARCHAR(20)
)

CREATE TABLE #CategoryCode
(
    CategoryCode VARCHAR(20)
)

IF @SelectedOfferes IS NOT NULL
BEGIN
    INSERT INTO #SelectedOfferes
        SELECT part 
        FROM dbo.[FormatTextByDelimiter] (@SelectedOfferes, ',')
END

IF @SelectedBrandCode IS NOT NULL
BEGIN
    INSERT INTO #BrandCode
        SELECT part 
        FROM dbo.[FormatTextByDelimiter] (@SelectedBrandCode, ',')
END

IF @CategoryCode IS NOT NULL
BEGIN
    INSERT INTO #CategoryCode
        SELECT part 
        FROM dbo.[FormatTextByDelimiter] (@CategoryCode, ',')
END

SELECT *    
FROM Products P
INNER JOIN Discount D ON P.DiscountCode = D.DiscountCode
INNER JOIN AvailableBrand AB ON P.BrandCode = AB.BrandCode
INNER JOIN Category C ON P.CategoryCode = C.CategoryCode
WHERE (@SelectedOfferes IS NULL 
       OR (EXISTS (SELECT 1 FROM #SelectedOfferes OFR 
                   WHERE OFR.DiscountCode = P.DiscountCode)))
  AND (@SelectedBrandCode IS NULL 
       OR (EXISTS (SELECT 1 FROM #BrandCode BC 
                   WHERE BC.BrandCode = P.BrandCode)))
  AND (@CategoryCode IS NULL 
       OR (EXISTS (SELECT 1 FROM #CategoryCode CAT 
                   WHERE CAT.CategoryCode = P.CategoryCode)))
tsql sql-server sql
2 Answers

This method doesn't quite do what the OP wanted but is valid in many other cases

I have some questions about your string split function, is it set-based or a looping query? If it's not set-based then you should probably replace it with Jeff Moden's DelimitedSplit8K available at http://www.sqlservercentral.com/articles/Tally+Table/72993/ .

But either way the below change to your last query should help quite a bit. The IS NULL parts aren't needed since it is a LEFT JOIN and the table will be empty if the variable it is built with is NULL, so you get the same result with less work for the engine.

SELECT *    
FROM Products P
INNER JOIN Discount D ON P.DiscountCode = D.DiscountCode
INNER JOIN AvailableBrand AB ON P.BrandCode = AB.BrandCode
INNER JOIN Category C ON P.CategoryCode = C.CategoryCode
LEFT JOIN #SelectedOfferes OFR ON OFR.DiscountCode = P.DiscountCode
LEFT JOIN #BrandCode BC ON BC.BrandCode = P.BrandCode
LEFT JOIN #CategoryCode CAT ON CAT.CategoryCode = P.CategoryCode
user_50493910

Dynamic SQL version

I have some questions about your string split function, is it set-based or a looping query? If it's not set-based then you should probably replace it with Jeff Moden's DelimitedSplit8K available at http://www.sqlservercentral.com/articles/Tally+Table/72993/ .

The below example should work the same as what you supplied but should be faster since it removes the ORs and the correlated subqueries from the WHERE clause. I'm not a fan of using dynamic SQL but sometimes it is the best way to get the job done. Maybe someone else can come up with a non-dynamic solution that works as well or better.

DECLARE @SelectedOfferes varchar(1000) = 'FLT10,SPL20'
DECLARE @SelectedBrandCode varchar(1000) = '208,406'
DECLARE @CategoryCode varchar(1000) = 'GMOVN2,CELSMR,LCDTV38IN'

CREATE TABLE #SelectedOfferes 
(
    DiscountCode VARCHAR(20)
)

CREATE TABLE #BrandCode
(
    BrandCode VARCHAR(20)
)

CREATE TABLE #CategoryCode
(
    CategoryCode VARCHAR(20)
)

IF @SelectedOfferes IS NOT NULL
BEGIN
    INSERT INTO #SelectedOfferes
        SELECT part 
        FROM dbo.[FormatTextByDelimiter] (@SelectedOfferes, ',')
END

IF @SelectedBrandCode IS NOT NULL
BEGIN
    INSERT INTO #BrandCode
        SELECT part 
        FROM dbo.[FormatTextByDelimiter] (@SelectedBrandCode, ',')
END

IF @CategoryCode IS NOT NULL
BEGIN
    INSERT INTO #CategoryCode
        SELECT part 
        FROM dbo.[FormatTextByDelimiter] (@CategoryCode, ',')
END

DECLARE @SQL NVarchar(4000);

SET @SQL = N'SELECT *    
FROM Products P
INNER JOIN Discount D ON P.DiscountCode = D.DiscountCode
INNER JOIN AvailableBrand AB ON P.BrandCode = AB.BrandCode
INNER JOIN Category C ON P.CategoryCode = C.CategoryCode'

IF @SelectedOfferes IS NOT NULL
    SET @SQL = @SQL + N'
INNER JOIN #SelectedOfferes OFR ON OFR.DiscountCode = P.DiscountCode';

IF @SelectedBrandCode IS NOT NULL
    SET @SQL = @SQL + N'
INNER JOIN #BrandCode BC ON BC.BrandCode = P.BrandCode';

IF @CategoryCode IS NOT NULL
    SET @SQL = @SQL + N'
INNER JOIN #CategoryCode CAT ON CAT.CategoryCode = P.CategoryCode';

EXEC sys.sp_executesql @stmt = @SQL;
user_50493910
Related