rewrite code to eliminate isnull

Question

rewrite code to eliminate isnull

U Matty

SSC Veteran

Points: 204
More actions
October 25, 2004 at 5:17 pm

#61175

Hi
Can some one aid in rewriting this code to eliminate the isnull function.

Select col_a
from some_table
where Date_col = isnull((select date from tableA where id=host_id()),getdate())

Thank You
Matty

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

TriggerMe SSChampion Points: 11752 More actions · Answer 1

The ISNULL Function is simply replacing all NULL host_id with the GETDATE() Function. If you do not want to do that then just remove the ISNULL Function.

Have I understood, or have I missed something here?

Kindest Regards,

U Matty SSC Veteran Points: 204 More actions · Answer 2

The Getdate() Function is replacing the entire select staement when it returns a null. The isnull function first examines the result of the select statement and if the result returned is null then it uses Getdate() which is the present day. example isnull(a,b), a would be the select statement and b would be the the getdate() function.

I need similar functionality as per the isnull function, if results returned from the select statement is null I would like to use the present date.

Thanks for your assistance thus far....

kindest regards

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

It seems to me that your code is doing exactly what you want it to do, so where is the problem?

Anyway, if you have some sort of nullophobia (), you could check out the CASE construction, as follows:

-- initial select here

where date_col =

case

when exists(select date from tableA where id=host_id()) then (select date from tableA where id=host_id())

else getdate()

end

But I like your code better!

Regards

Phil

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

noeld SSC Guru Points: 96590 More actions · Answer 4

Don't understand yet the purpose but:

Select col_a

from some_table

where

Date_col =

coalesce (select date from tableA where id = host_id(), getdate())

OR

Select col_a

from some_table

where

Date_col =

(case when exists (select * from tableA where id = host_id())

then select date from tableA where id = host_id()

else getdate()

end)

OR

Select col_a

from some_table t

join

(Select Top 1 date

from (select date from from tableA where id = host_id()

union all

select getdate()) Q

order by date desc) H on t.Date_col = H.date

* Noel

U Matty SSC Veteran Points: 204 More actions · Answer 5

Select col_a

from some_table t

join

(Select Top 1 date

from (select date from from tableA where id = host_id()

union all

select getdate()) Q

order by date desc) H on t.Date_col = H.date

--------------------------------------------------------------------

The above code seems very close to what I may need, I have yet to test, but essentially I need to rewrite the previous code removing all functions including 'isnull, coalesce and case statements" from the t-sql code. This requirement is part of some very strict coding standards that need to be adhered to. It was found that the isnull function causes severe performance degradation over time. This has already been brought up with MS who thus far believe that this may an issue when using hyper threading on cpu's on servers, they are still investigating, I essentially have to evaluate the below statement against any date. e.g

date1 = isnull((select date from table where id = host_ID()),getdate())

or

date <= isnull((select date from table where id = host_ID()),getdate())

or

isnull((select date from table where id = host_ID()),getdate()) is between date3 and date4.

thanks for responces thus far.......

kind regards

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Only way to get rid of the IsNull function is use a case statement to check IS NULL. Technically the Coalesce function is an extended version of the IsNull function so I would say that unfortunately is not really an answer for your question.

The question thou I have is "Why?".

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

ISNULL or COALESCE (for that matter) come in very handy at times.

To add to Antares; are you trying to reinvent the wheel?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Douglas De Ivey Old Hand Points: 396 More actions · Answer 8

I can't believe that there is a general performance problem with ISNULL as it would have been spotted long ago. I have never had any trouble with it and I have used it extensively for many years.

If you have a performance problem then it must be related to your specific query or the database design rather than the ISNULL generally. To help, we would need to see the whole query and understand the data a bit better.

David Burrows SSC Guru Points: 65143 More actions · Answer 9

As Douglas has mentioned a lot depends on the whole query and data structure and whether the date column is indexed.

If the date column is indexed isnull will use index seek but with the subquery sql will invoke a scan instead, I wonder if this is causing the performance problem.

Also you are substituting with GETDATE which will include the time portion, is this right?

I would write the query this way

declare @selectdate datetime

set @selectdate = dateadd(d,datediff(day,0,getdate()),0)

select @selectdate from tableA where id = host_id()

Select col_a

from some_table

where Date_col = @selectdate

Far away is close at hand in the images of elsewhere.
Anon.

U Matty SSC Veteran Points: 204 More actions · Answer 10

This scenario explained above might sound unbelievable but at the time we experienced this problem we felt mystified also. We have had three separate SQL persons (each one averaging 15 years in db's and systems) examine this scenario and the problem once manifested, continues.

At first when the query is written there are no performance issues but over time these same queries which ran for 2-3 seconds average 5 - 6 minutes. As for the data structure, very simple, the isnull function runs against two columns in the where part of the query. The first column always contains a date and represents a time constraint; the second column is updated manually by a user at some point. The second columns is first examined for a value and if none it uses the first column value. e.g.

where isnull(columnb,columna) = somedate

As mentioned previously this bug has already been raised with Microsoft.

When the problem is examined further it seems to lie in the query optimizer path that that sql decides to take. It takes a more convoluted path when it runs for 5-6 minutes.

When "set statistic io" is turned on we also realized that when the query degrades it does millions more reads and scans than before.

Hence the reason why getting rid of isnull has become necessary, not trying to reinvent the wheel but my problem is genuine and reproducible.

isnull((select date from tabelA where id=host_id()),getdate())

I have already gotten rid of isnull in all the situations mentioned at the top, it just so happens that there is was not a graceful way of getting rid of it in the former example, since this little snippet of code was used in a view (cannot use variables).

Thanks thus far for all responses

Kindest regards.

Antares686 SSC Guru Points: 125444 More actions · Answer 11

Sorry but this is not a bug.

where isnull(columnb,columna) = somedate

The problem is once the query has developed an execution plan it will abide by it. So if the choice is that columnb should be used for data lookup then columna will slow things down if columnb is null and columnb happens to have an index on it the engine was using. Vice versa, it could have choosen columna as the column to read especially if there is an index but is in columnb at runtime more often.

isnull((select date from tabelA where id=host_id()),getdate())

This should not really produce the same issue.However I would have to see the Exectuion Plan to know why it seemingly does on your system.

Could simply be a maintainecnce issue where you need to update statistics, rebuild indexes and other general maintainence task for the later issue.

noeld SSC Guru Points: 96590 More actions · Answer 12

this looks like an indexing problem to me

* Noel

David Burrows SSC Guru Points: 65143 More actions · Answer 13

as I said before your query

Select col_a

from some_table

where Date_col = isnull((select date from tableA where id=host_id()),getdate())

will always invoke a table or index scan

If [tableA] has [id] as a primary key and [some_table] has an index for [date], then the following will utilise index seeks

Select b.col_a

from some_table b

inner join (select [date] from tableA where id=host_id()) a

on b.Date_col = isnull(a.[date],getdate())

an again this all depends on how this fits in with the overall query in the view

Far away is close at hand in the images of elsewhere.
Anon.

Daniel Ruehle SSC-Addicted Points: 411 More actions · Answer 14

Couldn't you simply create a function:

create function dbo.fnViewDate()
returns datetime
as
declare @ret datetime
select @ret = date from tableA where id = host_id()
if @ret is null set @ret = getdate()
return @ret
go

and then your view can be:

select col_a from some_table where Date_col = dbo.fnViewDate()

This is assuming, of course, that a UDF wouldn't cause the same issues with HT machines as ISNULL seems to be doing and your coding standards allow the use of UDF's.