August 17, 2009 at 8:41 am
And free onion rings.
August 17, 2009 at 5:17 pm
GilaMonster (8/17/2009)
Paul White (8/16/2009)
The behaviour of the second statement rather depends on the setting of ANSI_NULLS.Considering I never switch it off, anything I post about nulls can be assumed Ansi_nulls on
It is still a true statement 🙂
If nothing else, think of all the stored procedures out there with ANSI_NULLS OFF.
GilaMonster (8/17/2009)
If set to ON, we get a constant scan since it can never return rows because of the NULL comparison.
The exec plan's a lot more complex than that. The one I got in testing last night (with subquery, not a list) had 4 clustered index scans, one row spool, two nested loop joins and a hash join. (SQL 2008 SP1)
Edit: That was with no indexes present on the 'join' columns. I didn't get around to testing the case where there were indexes.
Well of course the execution plan is more complex - unless you include a NULL in a hard-coded list with ANSI_NULLS ON, in which case you get a constant scan and that's it. Your posted examples were lists not sub-queries, so I don't really see what you are objecting to?
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 18, 2009 at 2:22 am
Paul White (8/17/2009)Your posted examples were lists not sub-queries, so I don't really see what you are objecting to?
I posted one list as a quick way to show behaviour. All the other queries that I'd been talking about in that post and the one that was quoted, and the one where the relative performance was questioned and mentioned were IN and EXISTS with subqueries.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2009 at 6:25 am
Gail,
I think you have mis-read one or more posts at some stage.
My points about ANSI_NULL and the Constant Scan iterator quoted this code:
select * from sys.databases where name not in ('master','model')
select * from sys.databases where name not in ('master','model', null)
So some of your replies have not made sense to me.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 18, 2009 at 12:13 pm
rja.carnegie (8/17/2009)
IN(...) doesn't seem to mind if a NULL is in there. But NOT (name IN (...) ) apparently also does the same as (name NOT IN (...) ). Can someone walk me through this please? :kiss:
I see Paul already gave this answer to rja.carnegie, so please forgive me for adding more detail. What he is saying is that with ANSI NULLS ON, the WHERE clause will always fail if the list of "IN (...) items" contains one or more NULLS.
(This holds true for a subquery, or a "hardcoded" list, but if a hardcoded list contains a NULL, the Query Optimizer can greatly simplify the plan because it "knows" that the WHERE clause will never evaluate to TRUE. It will always be UNKOWN and therefore will always fail)
For example:
WHERE somecol NOT IN (1,2, NULL) is equivalent to
WHERE NOT (somecol = 1 OR somecol = 2 OR somecol = NULL)
ANSI NULLS ON causes NULL comparisons to be evaluated as UNKNOWN, so if somecol contains, let's say a 3, this evaluates to
WHERE NOT (FALSE OR FALSE OR UNKNOWN) which is equivalant to
WHERE NOT (UNKNOWN) which is equivalant to
WHERE UNKNOWN
Which will fail and not return any rows.
Obviously if a column is defined as NOT NULL, it can't contain NULLs, so a simple subquery list that just selects from that column will never contain NULLs and there is no issue with using the NOT IN technique.
In my opinion, it's much safer to leave ANSI NULLS ON and avoid using the NOT IN technique if your comparison list allows NULLs.
Decide if you want NULL compared to NULL to be considered a "match" and explicitly build it into your query as shown in these simple examples (Yes, the tables should have indexes 😉 Remember, the goal here is to list parent records that DON'T have a matching column (match_col) in any of the child records.
IF OBJECT_ID(N'parent', N'U') IS NOT NULL
DROP TABLE parent
Create table parent
(
match_col int NULL,
id_description nvarchar(255) NULL
)
IF OBJECT_ID(N'child', N'U') IS NOT NULL
DROP TABLE child
Create table child
(
childid int NULL,
match_col int NULL
)
--Put in some test data
INSERT parent
values(1,'match_col is 1. Have a match in child table.')
INSERT parent
values(2,'match_col is 2. No match in child table')
INSERT parent
values(NULL,'match_col is null. Does it match NULLs in child table?')
INSERT child
values(1,1)
INSERT child
values(2,NULL)
--NULL compared to NULL is considered a match
--LEFT JOIN technique
select p.* from parent P
left join
child C on P.match_col = C.match_col or (P.match_col is NULL and C.match_col is NULL)
where C.childid is null --notice we're checking the childid here and not match_col
--NULL compared to NULL is considered a match
--NOT EXISTS technique
select * from parent P
where
NOT EXISTS (select 1 from child C where C.match_col = P.match_col) AND
NOT (P.match_col IS NULL and EXISTS (select 1 from child D where D.match_col IS NULL))
--equivalent result sets but very different execution plans
--NULL compared to NULL not considered a match
--LEFT JOIN technique
select P.* from parent P
left join
child C on P.match_col = C.match_col
where C.childid is null
--NULL compared to NULL not considered a match
--NOT EXISTS technique
select * from parent P
where
NOT EXISTS (select 1 from child C where C.match_col = P.match_col)
August 18, 2009 at 11:55 pm
Hi,
Sorry for late replying, actually I was out of net for last 3 weeks. I have seen the comments. I do agree that I had some misconceptions, which are now very clear to me. Thanks to every body...but my main aim is fulfilled I.E. LEARNING. If sqlservercentral.com will not publish this article, then probably I would apply these in my application (I think some of u would do the same). Again, thanks to sqlservercentral.com
Anyway, I need some more time to go in depth and reply. And obviously I will do that.
Thanks to all again.
August 19, 2009 at 2:07 am
arup_kc (8/18/2009)
Hi,Sorry for late replying, actually I was out of net for last 3 weeks. I have seen the comments. I do agree that I had some misconceptions, which are now very clear to me. Thanks to every body...but my main aim is fulfilled I.E. LEARNING. If sqlservercentral.com will not publish this article, then probably I would apply these in my application (I think some of u would do the same). Again, thanks to sqlservercentral.com
Anyway, I need some more time to go in depth and reply. And obviously I will do that.
Thanks to all again.
He speaks! Thanks for the comment 🙂 I totally agree - I'm here on SSC to learn as well. Sometimes the only way is by making mistakes...
August 19, 2009 at 2:44 am
August 21, 2009 at 5:07 am
Anton Kaborka (8/10/2009)
Don't you mean to advise "set nocount on"?
Hi Anton, u r right...sorry 4 the typo error
August 21, 2009 at 7:06 am
arup_kc (8/18/2009)
Hi,Sorry for late replying, actually I was out of net for last 3 weeks. I have seen the comments. I do agree that I had some misconceptions, which are now very clear to me. Thanks to every body...but my main aim is fulfilled I.E. LEARNING. If sqlservercentral.com will not publish this article, then probably I would apply these in my application (I think some of u would do the same). Again, thanks to sqlservercentral.com
Anyway, I need some more time to go in depth and reply. And obviously I will do that.
Thanks to all again.
Ya know... that's a outstanding attitude. Well done, Arup.
--Jeff Moden
August 24, 2009 at 2:25 am
Andy DBA (8/18/2009)
For example:
WHERE somecol NOT IN (1,2, NULL)
is equivalent to
WHERE NOT (somecol = 1 OR somecol = 2 OR somecol = NULL)
Wait, isn't that backwards? IN is equivalent to a list of ORs, NOT IN is equivalent to a list of NOT... AND NOT... AND NOT?
August 24, 2009 at 3:09 am
Hi everybody,
Thanks for constant support. At least I have got a lot of tips fromt he forum discussions.
The first thing I should say, I had some misconceptions which are rectified now.
Along with that, I am trying to modify the article with the newly learned tips. Hope this will be helpful for all of us.
But one thing I must say, this article is on PERFORMANCE. For me, its a very relative term. Thats why I have advised everybody atleast to check with profiler before/after applying the tips. Its difficult to frame rigid performance tips because of the relativity.
As an example, the cursor/temp table issue. In several SP's I have replaced the cursor with a combination of temaptable+while and it gave me better performance.
Again, I am thanking to Steve and SSC and all forumaists...for there time and energy.
August 24, 2009 at 6:50 am
arup_kc (8/24/2009)
As an example, the cursor/temp table issue. In several SP's I have replaced the cursor with a combination of temaptable+while and it gave me better performance.
Unfortunately, in this case, you only substitted one form of RBAR for another form of RBAR. The best solution in this case is to find a set-based solution to replace a cursor-based solution (and a WHILE + Temp Table is still a cursor-based solution).
RBAR = Row By Agonizing Row (A Modenism)
August 24, 2009 at 7:02 am
Unfortunately, in this case, you only substitted one form of RBAR for another form of RBAR. The best solution in this case is to find a set-based solution to replace a cursor-based solution (and a WHILE + Temp Table is still a cursor-based solution).
RBAR = Row By Agonizing Row (A Modenism)[/quote]
Hi Lenn,
What you are telling is absolutely correct. Some there are some situations where we cant avoid cursors...I am telling abt that situation. Also, I have checked the Temp+While solutions, indeed it gave better performance...thats why I included that in this article.
August 24, 2009 at 8:05 am
rja.carnegie (8/24/2009)
Andy DBA (8/18/2009)
For example:
WHERE somecol NOT IN (1,2, NULL)
is equivalent to
WHERE NOT (somecol = 1 OR somecol = 2 OR somecol = NULL)
Wait, isn't that backwards? IN is equivalent to a list of ORs, NOT IN is equivalent to a list of NOT... AND NOT... AND NOT?
It's not backwards because I still have the NOT outside the parens. The important point here is that because there is a NULL comparison in the expression, the entire expression evaluates to UNKNOWN regardless of the other items in the list or the value in the comparison column. If you're trying to produce a list of records without matching records in another table, (sometimes called "orphans" eg. customers without address records) a NULL in the comparison list will cause the NOT IN query to NEVER return any results. Any orphans that may be present will be missed.
The NOT EXISTS and LEFT JOIN queries don't have this problem and can even be coded to treat NULL = NULL as a "match" if desired. I wouldn't design a new system like that, but you don't always have control over the data set or business rules.
Viewing 15 posts - 211 through 225 (of 244 total)
You must be logged in to reply to this topic. Login to reply