SQL Server Performance Forum – Threads Archive

Query hangs on NOT IN

I am not sure why this query is hanging when I add in the NOT IN statement. Without the NOT IN statement, it returns the result lightning fast. I have a non-clustered index on WellsUniqueID I also have a non-clustered index on ChangeID Here is the query: SELECT Wells.WellsUniqueID, Wells.ChangeID, Wells.WellsID, Wells.LegalName, Company.CompanyName, Company_1.CompanyName AS CompanyName_1, Wells.Mer, Wells.Twp, Wells.Rge, Wells.Sec, Wells.LSD FROM WellAssignments INNER JOIN ((Wells LEFT JOIN Company AS Company_1 ON Wells.Licencee = Company_1.CompanyID) LEFT JOIN Company ON Wells.Operator = Company.CompanyID) ON WellAssignments.WellsUniqueID = Wells.WellsUniqueID WHERE WellAssignments.PropertyID=1660 AND WellAssignments.CreatedBy=6623 AND WellAssignments.Display=’1′ AND (Wells.CreatedBy = 6624 OR Wells.CreatedBy = 6623) AND Wells.WellsUniqueID NOT IN (SELECT ChangeID FROM Wells WHERE ChangeID IS NOT NULL AND CreatedBy = 6623) Thanks for any help you can give me [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

If you really need to compare every single field your ON clause will get very large (because you will join on every field) and this query will get very slow. Try to compile execution plan with and without NOT IN clause. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

"where Not exists" works faster than "where not in" — not in has to compare to every value returned, not exists will stop as soon as if finds a single matching value
You shouldnt have to say changeid is not null since any returned null will never equate to a value – might not matter since you index changeid, and I dont know how many rows would be returned Also, not knowing the rest of your app, but you also seem to do lots of where type clauses on the assorted createdby fields. These may be good candidates for indexes. What does the query plan show? Chris

Hi Sayta, Thanks for your quick reply. I performed both execution plans and the one with the NOT IN shows a Bookmark Lookup cost of 50%. I understand that 50% is probably really high, but I am not sure what to do from here. It says to use a Bookmark (RID or Clustering Key) to look up the corresponding row in the Table or Clustered Index. I made a screen shot of the execution plan if you want to take a look at it.
The url is : http://www.radikel.com/ExecutionPlan/ExecutionPlanWithNotIn.jpg Thanks again

Bookmark lookups are quite common to see. Essentially, they are telling you that the Query Processor had to look up the row columns it needs from the table or a clustered index, instead of being able to read it directly from a non-clustered index. Bookmark lookups are not ideal from a performance perspective because extra I/O is required to look up all the columns for the rows to be returned. SO do you have clustered index on the table? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

I don’t have a clustered index on the table. Should I created a clustered index on the primary key?

See that helps or not, if not on most queried column. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

I decided to take ChrisFretwell’s advice and change my query to a NOT EXISTS. My query pulls back all the records lightning fast[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />], so I might leave the indexes set as they are for now. I need to learn more about that how to set indexes correctly before I start deleting the ones that are already there. Thanks again Chris & Satya

]]>