March 9, 2011 at 2:24 pm
I ran into to a perplexing interesting situation
select * from table where column1 is null
runs slow; takes minutes for 38 records
where as
select * from table where isnull(column1,'')=''
runs at least 10xfaster
column1 is a non-clustered indexed and another ID column is the clustered index. table has about 1000s of records. I have tried after reindexing and the timing doesnt change much
Any thoughts/ ideas why this might happen?
March 9, 2011 at 5:30 pm
did you compare the execution plans? That'll tell you what's going on better than anything else.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 9, 2011 at 10:32 pm
An old trick is to create a composite index with an additional not null column.
Normally, there are no statistics for NULL-values. By adding a not null column you semi-force them to be maintained.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply