标签云

微信群

扫码加入我们

WeChat QR Code

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

-- RESOLVED

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...

2018年07月17日10分20秒

Post a query plan.

2018年07月18日10分20秒

Yes, it can be null. Very sketchy I know. This is the hand I've been dealt

2018年07月17日10分20秒

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?

2018年07月17日10分20秒

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?

2018年07月18日10分20秒

The produces the same query plan, oddly. Very interesting approach though

2018年07月17日10分20秒

user1002479: try forcing another join method (replace JOIN with INNER MERGE JOIN)

2018年07月18日10分20秒