Skip to Content
Former Member
Feb 05, 2016 at 07:23 PM

ADS Performance Issue


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;

create table #TEMP ( AccountNumber integer, PostingDate date );
create index AccountNumber on #TEMP(AccountNumber);
create index PostingDate on #TEMP(PostingDate);
delete from #TEMP;

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 )