Execution plan - Not using Index seek due to ISNULL

Question

i've got a procedure that i want to improve that uses this where clause:

WHERE   p.PlatformId = ISNULL(@PlatformId, p.PlatformId)
and p.PayeeStsId     = 1
and p.PlatformPayeeId   = ISNULL(@platformPayeeId, p.PlatformPayeeId)
and pba.PayeeBankStsId=1
and a.Gcid = ISNULL(@Gcid, a.Gcid)

the ISNULL is causing me problems with using the index how can i avoid using this format of ISNULL?

p.PlatformId = ISNULL(@PlatformAccountId, p.PlatformId)

this format is causing the execution plan to do index scan.

Answer 1

door #1

You can slap an OPTION(RECOMPILE) hint on the end of your query. The downside here is that SQL Server will have to compile a plan for it each time, and you won't have much of a trail in the plan cache (which isn't as big of a deal if you have Query Store enabled).

The upside is that every time this runs, the optimizer will effectively execute your query with the literal values replacing parameter or variable placeholders. This will allow for seeks when indexes are suitable.

door #2

You can use parameterized dynamic SQL to generate a where clause for any non-NULL parameters. Here's a loose example based on the limited information you've provided.

DECLARE
    @s nvarchar(MAX) = N'',
    @parameters nvarchar(MAX) = N'@PlatformId integer, @platformPayeeId integer, @Gcid integer';

SELECT
    @s += N'
SELECT
    columns
/*Put the name of your stored procedure in a comment so people know where it comes from*/
FROM dbo.YourTable AS p
JOIN dbo.YourOtherTable AS pba
  ON p.Id = pba.Id
JOIN dbo.YourOtherOtherTable AS a
  ON p.Id = a.Id
WHERE p.PayeeStsId = 1
AND   pba.PayeeBankStsId = 1';

IF @PlatformId IS NOT NULL
BEGIN
    SELECT
        @s += N'
AND   p.PlatformId = @PlatformId'
END;

IF @platformPayeeId IS NOT NULL
BEGIN
    SELECT
        @s += N'
AND   p.PlatformPayeeId = @platformPayeeId'
END;

IF @Gcid IS NOT NULL
BEGIN
    SELECT
        @s += N'
AND   a.Gcid = @Gcid'
END;

EXEC sys.sp_executesql
    @s,
    @parameters,
    @PlatformId,
    @platformPayeeId,
    @Gcid;

You'll need to adapt it to your specific situation, of course, but this should get you going in the right direction.

Answer 2

From what I understand a function is applied to a column, SQL Server often cannot use the index efficiently, leading to a full index scan. I've seen this in a past data conversion I ran where the inclusion o functions into WHERE and SELECT clauses just crippled my scripts.

The obvious solution to this is not to use the IsNull() function on an indexed column. One way is to precompute value of PlatformID as you are passing this in as a variable and provide the computed result into your query:

SET @DefaultPlatformAccountId = @PlatformAccountId;
            
IF @DefaultPlatformAccountId  IS NULL 
    SET @DefaultPlatformAccountId = 
    <INSERT LOGIC TO OBTAIN PLATFORMID HERE FOR WHEN IT IS NULL HERE>
    

Which leads to something like this:

WHERE   p.PlatformId = @DefaultPlatformAccountId  
and p.PayeeStsId     = 1
and p.PlatformPayeeId   = ISNULL(@platformPayeeId, p.PlatformPayeeId)
and pba.PayeeBankStsId=1
and a.Gcid = ISNULL(@Gcid, a.Gcid)

Obviously also precompute the value for: p.PlatformPayeeId as well :-)