INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Performance Issue When Filtering on Nulls

Performance Issue When Filtering on Nulls

(OP)
I have a fairly simple query that runs in a few seconds - similar to this:

CODE

select
patient,
room,
max(intime) as InTime,
OpDateTime

from some tables

group by patient, room, OpDateTime

where OpDateTime is not null 

However, when changing to

CODE

where OpDateTime is null 

then the query takes ten minutes or more.

Why would that be?

So I changed to :

CODE

with cte_test as
(
select
patient,
room,
max(intime) as InTime,
OpDateTime

from some tables

group by patient, room, OpDateTime
)

select * from cte_test where OpDateTime is not null 

and I get the same results when changing to include or exclude nulls. It seems that once the cte values have been retrieved then getting those values would be immediate regardless of the where clause. This query only returns about ten records including nulls

I would appreciate an explanation as to why this is happening and what I should do about it. TIA


RE: Performance Issue When Filtering on Nulls

What is actual execution plan?

Borislav Borissov
VFP9 SP2, SQL Server

RE: Performance Issue When Filtering on Nulls

Is there an index on the column "OpDateTime"?

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive agile big data clouds)


RE: Performance Issue When Filtering on Nulls

(OP)
I didn't see a big difference in the execution plans, but I'm not too skilled at reading them. Regardless, they must have shown the problem somewhere because when I added the index to that field I get subsecond response time when running the query to either include or exclude nulls.

I'm left with two questions - why would it matter whether your are searching for nulls vs. non-nulls in a non-indexed field?

Also, I had ended up putting the unfiltered data into a variable table, then filtering when I did the select. But that approach didn't work with a cte - why the difference? I had thought both record sets were in memory and would be return the same results.

Thanks for the educating.

RE: Performance Issue When Filtering on Nulls

The timing might just be caused by retrieving the data.
If you only want to see how much time it takes to filter data SELECT COUNT(*) FROM (SELECT query), that limits retreiving data to the number of records the inner query WOULD return.

You're under the wrong impression a CTE executes and then the query using the CTE is working on that rertrieved data. That can happen, but indeed the CTE is just naming its query so what executes only results from the last query using the CTE, and that might be optimized by first executing the CTE, the optimizer might decide to do something else or at do things in a different order than you expect. Overall CTE is just "syntactic sugar", it makes code better readable. It has some options to make recursive queries, as it can reference itself, but that's just the special case of a CTE, normally it is just nameing a query and substituting it into the overall query later executing.

What always makes thins faster is indexing, that's the only reason for that.

It's not the case finding NULLs takes longer than finding any other value. I assume most of your records have NULL in OpDateTime and so retrieving all data with NULLs takes longer.

CODE

Select Count(OpDateTime) as OpDateSet ,Count(*)-Count(OpDateTime) as OpDateNotSet, Count(*) as Total from Sometable 

I guess the OpDateTime is a datetime you only set in case of updates. Then most records never had an update, perhaps. A query retrieving data with OpDateTime NULL will simply fetch much more data than a query only fetching records having had any update. No matter, if that's the meaning of the field or not, only the statistics matters. The more a query returns (or aggregates) the longer it takes, of course. But that has nothing to do with the value NULL. If you have a table with a bit field and most records have a 0 and some a 1 fetching data WHERE field = 0 fetches more rows and takes longer. It's not about NULL, it's about majority/minority of data.

Bye, Olaf.

RE: Performance Issue When Filtering on Nulls

"Create a Filtering index where the conditions are not NULL.
"

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close