I need advice on how to break up this query so it's not so slow. I've been reading up on EXISTS and UNION but I'm not sure how they can be used in this situation. LineA/B/C are all nvarchar(max) and I can't change the join to be on something other than text. Has to be line this, unfortunately.
UPDATE n SET AddressID = a.AddressID FROM #NewAddress n JOIN dbo.Address a ON (a.[LineA] = n.[LineA] OR (a.[LineA] is null AND n.[LineA] is null)) AND (a.[LineB] = n.[LineB] OR (a.[LineB] is null AND n.[LineB] is null)) AND (a.[LineC] = n.[LineC] OR (a.[LineC] is null AND n.[LineC] is null)) WHERE n.Processed = 0
Any ideas on how to UNION or EXISTs this query? This seems to be a pretty common join condition I'm encountering
I'm not sure how to post a query plan, but it's really small like this
SELECT 0% <---- Hash Match (Inner Join) 75% <------- Clustered Index Scan 0% <------- Clustered Index Scan 25%
None of these rows are indexed as they are nvarchar(max). I can't index dbo.Address either
I added an index to the Processed column in #NewAddress. Apparently, that was the slow part
Can LineA really be null? If LineA means AddressLine1 I'm highly skeptical of your data integrity...
Post a query plan.
Yes, it can be null. Very sketchy I know. This is the hand I've been dealt
Are there any indexes already on dbo.Address? Can you give us some idea of the numbers of records on each table? Do you actually want all the fields from both tables in your select?
Does coalesce() yield any improvement? on coalesce(a.LineA,'<null>') = coalesce(n.LineA,'<null>') And what happens if the addresses match but on different lines, e.g. LineA matches LineB?
The produces the same query plan, oddly. Very interesting approach though
user1002479: try forcing another join method (replace JOIN with INNER MERGE JOIN)