query runs fine when used "IS NOT NULL" ,doesnt works when searched with a value

Question

Post reply

query runs fine when used "IS NOT NULL" ,doesnt works when searched with a value

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
October 19, 2010 at 2:13 pm

#142243

My select query runs fine when i search with IS NOT NULL criteria and works terrible when i assign a value..Example is mentioned below:
SELECT * FROM myview WHERE column_flag = 'N' ---- takes 25 secs to run
SELECT * FROM myview WHERE column_flag IS NOT NULL -- takes11 secs
In the column there is either "N" or NULL values. Any idea how to tune the original one?

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

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

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 1

first thing never use select * instead write column names because it causes table scan in many cases.

any way use

is_flag like N

and try

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

same results no improvement. It is retrieving from a view and it was working fine until yesterday..

David Webb-CDS SSCoach Points: 17398 More actions · Answer 3

Post the view definition.

And then again, I might be wrong ...
David Webb

Leo.Miller SSChampion Points: 13051 More actions · Answer 4

Nullable columns can cause problems for queries like this. NULL is an undefined value, think of it as "noise". This means SQL can't do an EQUALS test on it. To demo this try running SELECT * FROM myview WHERE column_flag != NULL and see the change in behaviour.

If you compare the query plans for your 2 selects you will probably see they are very different,the first is probably doing some type of scan where the second is doing a seek.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 5

same results no improvement. It is retrieving from a view and it was working fine until yesterday..

Run both queries and include exec plans and see you will find the difference

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 6

need to make lot of changes before i post here., i do not think it is possible

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 7

Leo.Miller (10/19/2010)
Nullable columns can cause problems for queries like this. NULL is an undefined value, think of it as "noise". This means SQL can't do an EQUALS test on it. To demo this try running SELECT * FROM myview WHERE column_flag != NULL and see the change in behaviour.
If you compare the query plans for your 2 selects you will probably see they are very different,the first is probably doing some type of scan where the second is doing a seek.
Cheers
Leo

But when i specify where column_name IS NOT NULL , performance is better

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 8

pavan_srirangam (10/19/2010)
same results no improvement. It is retrieving from a view and it was working fine until yesterday..
Run both queries and include exec plans and see you will find the difference

Both the queries take 24 secs to run.?

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 9

My select query runs fine when i search with IS NOT NULL criteria and works terrible when i assign a value..Example is mentioned below:

SELECT * FROM myview WHERE column_flag = 'N' ---- takes 25 secs to run

SELECT * FROM myview WHERE column_flag IS NOT NULL -- takes11 secs

Then what is the issue as you said both run for the same time ?

post it clearly what is the issue?

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Leo.Miller SSChampion Points: 13051 More actions · Answer 10

sqldba_icon (10/19/2010)
pavan_srirangam (10/19/2010)
same results no improvement. It is retrieving from a view and it was working fine until yesterday..
Run both queries and include exec plans and see you will find the difference
Both the queries take 24 secs to run.?

Yes, I expect botthe queries that DON'T use IS NOT NULL to perform poorly. The issue is SQL has to do a SCAN of some sort if you use "=" on a NULLABLE column that contains NULLs, because all NULLs are seen as the same, even if they aren't seen as equal. NULLs can't be properly indexed etc.

Look at the execution plans.

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 11

Pavan, thanks for your help but i have clearly mentioned in my first post i can say it one more time. This is the actual query "SELECT * FROM myview WHERE column_flag = 'N' " takes 25 secs to retrieve data but when the slightly modify the query like "SELECT * FROM myview WHERE column_flag IS NOT NULL -- takes11 secs"..runs faster and also gives the same results. So my question is what is missing in the first query which takes 25 secs and the second one only 11 secs. Sorry for the confusion.thanks

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 12

Please post sqlexecution plans of both queries (saved from SSMS .sqlplan files) as attachment of your reply.

Can you also post the ddl of your view ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 13

Can we do it without exec plan? it has some confidential data, i will try to modify and put it here. Thanks

Leo.Miller SSChampion Points: 13051 More actions · Answer 14

The execution plan won't return any user data, at most it will return some tables and index info.

Also seeing the code for the underlying view makes a big difference. They way the view behaves can depend on the way you query it. Remember that the view is not a table, but another select statement that must be satisfied before the calling select can be satisfied. The way SQL parses the whole query can change depending on the way you select from the view. I've seen performance improve by a factor of 60 times just by changing the way the view was configured to account for the way the higher level select was done.

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.