Performance Issue When Filtering on Nulls 3

[briangriffin]

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

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

where OpDateTime is null

then the query takes ten minutes or more.

Why would that be?

So I changed to :

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

 

[bborissov]

What is actual execution plan?

Borislav Borissov
VFP9 SP2, SQL Server

 

[johnherman]

Is there an index on the column "OpDateTime"?

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

 

[briangriffin]

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.

 

[Olaf Doschke]

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.

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.

 

[sqlprogrammers]

Create a Filtering index where the conditions are not NULL.