Geek Answers Handbook

Does multi-channel UPDATE-JOIN with many ISNULLs take a lot of time?

We have a stored proceudre in our database that updates the table, combining 2 tables by 30 columns with the where clause. SQL is in the general format:

UPDATE Target SET col1 = Source.col1 INNER JOIN Source on ISNULL(Target.Col2, '') = ISNULL(Source.Col2, '') and ISNULL(Target.Col3, '') = ISNULL(Source.Col3, '') and . . . ISNULL(Target.Col31, '') = ISNULL(Source.Col31, '') and

A plan is requested here. Save it to your PC and open it again so that it scales better.

enter image description here

The source table has 65M records, Target 165M. He used to work for a few minutes. Given how ugly and potentially inefficient the request is, I find this amazing. This month he worked for 1.5 hours, used 100% of the processor, and we had to kill him.

Any suggestions for improvising the request below and running it on time ..?

We have indices of one column on several columns used in a 30-col join condition.

I know the ISNULL function, and the connection on 30 columns is nuts, and this is a bad design. Do not blame me, I inherited this economy.

Unfortunately, there is no time for redesigning. Any suggestions?

+7
source share
2 answers
  • Please post a screenshot of the assessment of the implementation plan.
  • I suspect that the request previously used a hash join (which it needs), but for some reason the power estimate is currently erroneous, and you get a loop join. Put a hash join on the request to check if this is fixed ( INNER HASH JOIN ). Once we have an accurate plan, we can say more.
  • Change the equality to (A1 = A2 OR (A1 IS NULL AND A2 IS NULL)) . SQL Server actually recognizes this pattern and converts it to "exact equal without silly zero semantics" inside. You can search indexes this way, even with zero values.

If this does not help, be sure to follow step (3) and create a coverage index on col2-col31, including col1. This will give you a merge pool, which in this case is the most efficient. It's very fast. A warning. This will double the size of the disk on disk and slow down updates.

+5
source

DBa suggested that we follow the recommendations of query analysts to add an index with all 30 columns, most of which were “included” columns. This allowed the request to be completed. The next month, when we launched, the same SQL update, which usually works after 1.5 hours, did not complete after 24 hours. When we launched the update statistics, it ended in an hour.

0
source

More articles:


All Articles