SQL Server 2008R2. I have a simple join that returns zero rows in less than a second. When I try to run an update on that join, it should also take less than a second but it is taking one minute and 20 seconds.
select * from local_tbl as t1 inner join remote_tbl as t2 on t2.pk=t1.fk
returns zero records in less than a second, with
Query Plan: plan
update t1 set t1.name=t2.name from local_tbl as t1 inner join remote_tbl as t2 on t2.ph=t1.fk
updates zero records in a minute and 20 seconds
Query Plan 2: plan 2
If I restructure the update into a common table expression it still takes 15 seconds to update zero records.
update local_tbl set local_tbl.name='x' where 1=0 takes less than a second.
Some one will surely want to know why I care about updating zero records and of course, that is not what I care about. Normally the join returns more that zero records, it used to and still should run in less than a second but suddenly it started taking many minutes. I changed the content of t2 so the join returned zero records in attempt to isolate the issue.
Questions and clarifications:
update t1 set t1.name=t2.name from local_tbl as t1 inner join remote_tbl as t2 on t2.pk=t1.fkreally run? – "Gerard H. Pille"
Yes. It ran in less than a second every 5 minutes from 12/2018 until recently.It updates an average of 14,000 rows/day, appending a name to a log record. Suddenly it started taking more than a minute. The select from join still runs in less than a second. But the update, no matter if it is updating one record, one hundred records or ZERO records, is now taking over a minute. Personally, I don't think there is anything wrong with the join. It still runs fast. and it is not the actual updating. It is just as slow even if the join returns no records to update.
Did not mentioned it before: One of the tables being joined is on a linked server. That have never been an issue in the past and I will being making a local copy of the table to see if that changes anything. The table being updated is on the local server. I am just pulling the names to be appended to the log records on the local server from the remote server.
Additional information When I initially asked this question, I knew what an execution plan was in concept and had seen examples in print but did not know how to obtain one for my own queries. Thanks to some coaching from one of the first commentors, I was able to learn how to obtain and share the query plans. Studying the two query plans in SQL Server Management Studio, I was shocked to see that even though the join was identical in the two queries, the query plans where dramatically different and totally explain the difference in execution time. The fact that T2 is remote magnifies the difference but there would still be a difference even if T2 was local because in the case of the update query, it reads and returns the entire T2, then it passes it through a filter that it turns out does not actually filter (remove) any records (due to the nature of the data) but it might have and then it does a merge (inner Join) with the T1 rowset which contains zero rows giving the final rowset of zero rows. When I do the second query ("select from join" instead of "update from join") the query plan passes a few (in this case zero) row ids, one for each row in the T1 result set, to the remote server which can use the primary, clustered index to efficiently, just return those rows that are needed to join to the rows in T1. In my extreme test case that is zero rows to move across the wire instead of millions of rows, none of which are needed. In actual production it might be 500 rows of 68 bytes each (key and average name length) to move across the wire which can be done in a trivial amount of time. Now that I know what SQL server is actually doing in that minute, my focus switched to how to communicate to SQL server to pass the keys that I will need in the remote query instead of asking for every row in the remote query and then throwing away the rows that are not needed when they arrive. I have found an effective workaround but I am leaving the question open, hoping to find out why the optimizer is selecting a less than optimum plan when it knows better. My first attempt was to run the CTE version of the query, it two was electing to pass back the entire table. My next effort was to use two queries and a temporary table to hold the intermediate result. This time SQL server used the optimal plan to pull back the initial results and store them in the temporary table. I then repeated the join in the second query, but this time I was joining a tiny local table instead of a big remote table. The results were instantaneous, as I felt that they should be. This is the SQL for the split query technique that is providing reasonable performance:
select t1.pk,t2.name into #tmp from local_tbl as t1 inner join remote_tbl as t2 on t2.pk=t1.fk update t1 set t1.name=t2.name from local_tbl as t1 inner join #tmp as t2 on t2.pk=t1.pk drop table #tmp
The question remains, what have I done that makes the optimizer think that it needs to pass all of T2 across the wire? I will do some experimenting and see what I can figure out. For now the "bypass" is to break the query into two parts. I appreciate the direction and editorial assistance that I have received to this point.
Research update At the suggestion of @Scott Hodgin I tested the remote join hint. Even though it generated a warning that it was changing the join order due to the hint, i could discern no change in the execution plan and in particular the location of the join and which data elements were passed in which direction with or without the hint.
In each of the examples above, pk is the primary key; fk is a foreign key; t1 is the local table and t2 is on the linked (remote) server. (the servers are in the same room, just not on the same bus).