SQL Server Performance Forum – Threads Archive
Query hangs on NOT INI am not sure why this query is hanging when I add in the NOT IN statement.<br />Without the NOT IN statement, it returns the result lightning fast.<br /><br />I have a non-clustered index on WellsUniqueID<br />I also have a non-clustered index on ChangeID<br /><br />Here is the query:<br />SELECT Wells.WellsUniqueID, <br /> Wells.ChangeID, <br /> Wells.WellsID, <br /> Wells.LegalName, <br /> Company.CompanyName, <br /> Company_1.CompanyName AS CompanyName_1, <br /> Wells.Mer, <br /> Wells.Twp, <br /> Wells.Rge, <br /> Wells.Sec, <br /> Wells.LSD<br /> FROM WellAssignments INNER JOIN ((Wells LEFT JOIN Company AS Company_1 ON <br /> Wells.Licencee = Company_1.CompanyID) LEFT JOIN Company ON <br /> Wells.Operator = Company.CompanyID) ON <br /> WellAssignments.WellsUniqueID = Wells.WellsUniqueID<br /> WHERE WellAssignments.PropertyID=1660<br /> AND WellAssignments.CreatedBy=6623<br /> AND WellAssignments.Display=’1′ <br /> AND (Wells.CreatedBy = 6624<br /> OR Wells.CreatedBy = 6623)<br /> AND Wells.WellsUniqueID NOT IN (SELECT ChangeID FROM Wells WHERE ChangeID IS NOT NULL AND CreatedBy = 6623) <br /><br />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. _________
"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? _________
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. _________
I decided to take ChrisFretwell’s advice and change my query to a NOT EXISTS.<br />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.<br /><br />Thanks again Chris & Satya