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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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