Index Tuning on Queries with IS NULL clause

[pgthyagu]

Hi all

Look at the example given and pls provide a suggestion on how to improve the performance

Table : Test , Rows : 2243434
Columns : 5
Column Details : Col1 Numeric (primary key)
Col2 Numeric (references other table FKEY)
Col3 Varchar(10)
Col4 Datetime Null
Col5 Datetime Null

Indexes : Clustered on the primary key
Seperate Non Clustered on col4,col5

Query
Select col1,2,3,4,5 from test
where col4 > getdate()
and (col3 > getdate or col3 is Null)**

**U can even try with out the OR clause

Problem
Without "Is Null" , this query takes less than 1 sec. But with "Is Null" the query takes 12 secs.

Question
Should the "IS Null" queries be handled in any other way to improve the performance.

Pls let me know ur suggestion at the earliest.

Thanks in advance

 

[rt63]

There is a little confusion in the query syntax u have posted

col3 > getdate or col3 is Null

is it col3 or col5
is it

col5 > getdate() or col3 is null

or is it

col5 > getdate() or col5 is null


It will be better if instead of primary key as clustered, u make one of the col4/col5 as clustered index as clustered indexes are better at handling the kind of querying u are using. Also if col3 is actually in the query where clause, u can consider in index on that column too.

RT