I am having an issue with a script that I am running. In the example below, it takes a lot longer to run the query if @InputAccountNumber is equal to 1 instead of null, when I would expect the query to filter the records and be significantly faster. In contrast, if I set the @InputPostingDate value to be today I don’t see the same slowness issue.
//Create some test data
DECLARE i integer;
TRY
create table #TEMP ( AccountNumber integer, PostingDate date );
create index AccountNumber on #TEMP(AccountNumber);
create index PostingDate on #TEMP(PostingDate);
CATCH ALL
delete from #TEMP;
END TRY;
insert into #TEMP ( AccountNumber, PostingDate )
values ( 1, CURDATE() );
i = 1;
while i < 10 do
insert into #TEMP ( AccountNumber, PostingDate )
select AccountNumber + 1, PostingDate + 1
from #TEMP;
i = i + 1;
end while;
select count(*) from #TEMP
//Run the script to test the issue
DECLARE @InputAccountNumber integer;
DECLARE @InputPostingDate date;
@InputPostingDate = null;
@InputAccountNumber = 1;
select *
from #TEMP
where ( @InputAccountNumber is null or AccountNumber = @InputAccountNumber )
and ( @InputPostingDate is null or PostingDate <= @InputPostingDate )