insert/update against VIEW on linked server

Question

insert/update against VIEW on linked server

cphite

SSCarpal Tunnel

Points: 4278
More actions
January 5, 2023 at 5:06 pm

#4134139
I can't find an answer on Google so I am hoping someone here knows...
We have a linked server that points to a cloud instance of SQL - actually to be more specific it's an instance of Dynamics CRM on the GCCH (Government secure Azure) and we're connecting to it via an ODBC connection.
I have a view called OpportunitiesGCCH that is a direct SELECT from the Opportunities table on the remote database.
If I run the following code, it completes in less than a second:
```
update SystemUserGCCH
set Title = 'DBA'
where SystemUserID = @id
```
For the process I am working on, I need to update multiple rows. So I create a temp table and update against that, as follows:
```
update a
set
a.Name = b.Name
from
SystemUserGCCH a 
inner join #people b on a.SystemUserId = b.SystemUserId
where
b.RowType = 'UPDATE'
```
However, even if the #people table contains only one row, which is the same row I am updating in the first query, it is taking forever. In fact, I've killed it every time - the longest being 45 minutes.
The only thing I can figure is that it has to do with how SQL is building the query plan because it's a linked server, that somehow with a join it does it differently. If anyone knows of a workaround, or even some suggestions, it would be appreciated!
thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1381 More actions · Answer 1

Performance issue when querying views across linked servers:
Linked server queries against views don't utilize statistics-based cardinality estimation. The cardinality estimation against a view is a constant value of 10,000.
If you execute a query with the WHERE clause against views on a linked server, the cardinality estimation against views is also a constant value. However, the value varies depending on the compatibility level of the database.
Note
The cardinality estimation value won't be impacted by the compatibility level of database where the view is defined.
For a database compatibility level of 120 or higher (new version of cardinality estimator), the cardinality estimation is 100.
For a database compatibility level of 110 or lower (legacy cardinality estimator), the cardinality estimation is 1,000.

Your best performance bet, if possible, might be to create a stored procedure w/ a TVP or delimited string that gets converted back to table-valued variable or temp table on the remote server and execute that.

kaj SSCommitted Points: 1607 More actions · Answer 2

Working with a combination of local and remote tables can be tricky.

You can try a few easy modifications and see if that helps:

Switch the joins (which by itselft shouldn't make a difference, but sometimes do), i.e. join from #people to SystemUserGCCH
(to make it stick you can even lock it that way by adding OPTION(FORCE ORDER) at the end of the query )
Force a loop join, i.e. write the join as INNER LOOP JOIN
(alternatively you can use OPTION(LOOP JOIN), but that would then apply to all joins, which may not always be desirable - but in this case where there are only one join will have an identical outcome)

If this fails to produce better results you can always rewrite the query to a loop over #people and doing an update of SystemUserGCCH for each row in #people. 🙂

This reply was modified 1 year, 11 months ago by kaj. Reason: elaboration
This reply was modified 1 year, 11 months ago by kaj. Reason: typo
This reply was modified 1 year, 11 months ago by kaj. Reason: Typo

cyrusbaratt SSC-Addicted Points: 417 More actions · Answer 3

Sometimes, good indexes can help speed things up. I think you said the cloud database access works ok with one table?

Please ensure the cloud server is configured properly. (Memory/CPU/database auto growth/ tempdb files matching number of cores)

Database maintenance plan is a must in any platform.

DBASupport

cphite SSCarpal Tunnel Points: 4278 More actions · Answer 4

I appreciate all the responses. Unfortunately, the performance seems to be terrible with a join OR with multiple columns being updated. The tables we need to update are all very wide - 200 columns or more - so obviously that's a problem.

Also, we cannot execute code remotely against the cloud server, and we cannot create new objects on the cloud server.

I'm starting to suspect that the issue is with the driver itself. We do this sort of thing against our own Azure databases without these same issues. The GCCH requires 2-factor authentication when you log in, and part of what the driver does is take care of this transparently - so it's not a "normal" ODBC connection.

I've reached out to the vendor to see if they have any suggestions. But again, I appreciate the replies - I tried a few of the JOIN ideas against our regular Azure box and saw some noticeable improvements 😀