Query Performance(Is Null) | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Query Performance(Is Null)

Hello Friends,
I read in the SQL performance web site that try to avoid IS NULL in where Clause. I have a query like this, is this query really decrease the performance? Basically I am programmer, I am not perfect in SQL. If this query really decrease the performance, then please advise me to rewrite this query. Thanks in Advance Query:
—–
select top 1 tbl1.dt
from
tbl1 WITH (NOLOCK)
where
(tbl1.isdt= ‘N’
OR tbl1.isdt IS NULL)
AND
tbl1.id = ‘1’
order by
tbl1.dt asc
Don’t worry too much about IS NULL – when you need it, you need it. Blank dates, nullable columns, finding unmatched rows using outer joins … However, I’m a bit puzzled by the column "isdt" – this wouldn’t by any chance be a nullable Y/N column saying the "dt" column is a correct date? Anyway, you can use two ways of filtering for 2 options. The first is the one you’re using. The second is to use UNION – this is without the TOP 1 clause: select tbl1.dt from tbl1 WITH (NOLOCK) where (tbl1.isdt= ‘N’) AND tbl1.id = ‘1’
UNION
select tbl1.dt from tbl1 WITH (NOLOCK) where (tbl1.isdt IS NULL) AND tbl1.id = ‘1’
order by tbl1.dt asc Since you seem to be looking for the earliest "dt", which appears to be a date column, then just remember that when you order by a nullable column then the NULL value comes before any defined value. So you could also use this – assuming that we can indeed ignore the "isdt" column: select top 1 tbl1.dt from tbl1 WITH (NOLOCK) where (tbl1.id = 1) order by tbl1.dt

… and you can’t use this: SELECT MIN(dt) FROM tbl1 … because MIN ignores NULL values. However, depending on what kind of query you’re doing, you could use something like this: IF EXISTS (SELECT dt FROM tbl1 WHERE dt IS NULL)
SELECT NULL
ELSE
SELECT MIN(dt) FROM tbl1

Thank You Adriaan. The "isdt" is varchar, it has null values. I don’t know why it has Null values.I can not change the table design as it is already into production. So i Can not ignore "isdt" column. This is for your info.
I tried with your 2nd Option(UNION) it is taking more reads compare to mine. So I am using the first one only. Actually I bothered about ISNULL, but you suggested in the first line "Don’t Worry too much about IS NULL" so I am using the my query only. Thank you once again for your suggestion.
can you try this: select top 1 tbl1.dt
from
tbl1 WITH (NOLOCK)
where
(isnull(tbl1.isdt,’N’)= ‘N’)
AND
tbl1.id = ‘1’
order by
tbl1.dt asc

One thing that always puzzles me is, that people so frequently use the NOLOCK hint. I think there might be a misunderstanding what this really is. NOLOCK is equivalent to READ UNCOMMITTED isolation level. It will return data at a very high speed, thus increase performance. However, not always this data is reliable. Consider a hypothetical situation in a hospital. There’s a table with medication like this
CREATE TABLE medication
(
lethal_dose INT
) INSERT INTO medication SELECT 10 Now some clown issues the following statements
BEGIN TRAN
UPDATE medication SET lethal_dose = 100 Notice the transaction is not yet committed. At about the same time, some nurse is preparing the medication for the patient and fires a
SELECT lethal_dose FROM medication WITH (NOLOCK) lethal_dose
———–
100 (1 row(s) affected) Reading this information she goes ahead and doesn’t notice that the clown of the first connection realises his mistakes and issues a
ROLLBACK TRAN Hm, I wouldn’t want to play that Russian roulette and bet on the poor patient in this case. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Frank, you are right about the type situation you described. Other example are bank transactions e.g. checking balance on company or personal account. However, there are also business models and app architecture that benefit of nolock without risk. A good example is Monster (company I worked for). When you search for the job the list of jobs matching your criteria is generated. That list may not be quite accurate because of nolock hint used. There may be one or two jobs that are not available actually or you may miss a position that was not actually deleted. However, when you go to the link with position details you will receive an error ‘position is not available any more’ and that would be really low probability exception. You may learn about position that was not on the list if you re-run the search, which can happen anyway in case position is actually posted in the meantime. So it’s not big deal, but you gain much better response time with less processing power. I know your point was: Be carefull with nolock hints. I just wanted to add: but use it when it is acceptable.
Yes, that’s what I wanted to express: Use NOLOCK wisely. Thanks for clarifying. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
Hi ya, as long as there is a non-clustered index on (id, isdt, dt), then that query ought to fly as it is…? if you need it to fly even more then that… an option might be to add a calculated column isdtnotnull as isnull( isdt, ‘N’ ) and then index (id, isdtnotnull, dt) simplifying the query by removing the need for the OR is null bit… however as you say, this may not be something that you are able to do if you have no control over the schema Cheers
Twan
]]>