NOLOCK across linked server with join

Thanks all, I got some good ideas and new information from the various posts here.

Anyway, What I ended up doing was first run a script to pull the smaller reference data across to the sever with the very large tables, did my work locally, then saved the content down to a table there. Then another script comes through and pulls the pre-processed data across the linked server the other way.

There's a few more steps involved, but it eliminates the need to worry about nolocks across linked servers altogether.

To the issue of using nolock entirely, I should probably not have said it's used UNIVERSALLY, but the nature of this data is such that it's read upwards of 2000 times a second in some cases, so for certain operations, it is far more desirable to limit the potential of blocking anything than getting problems associated with nolock. There are also lots of system details that are immaterial to this conversation which I'm omitting. I am fully aware nolock is a sensitive issue among database developers (I've even been on the side warning against using them), so while I appreciate the concern, this is one of the situations where time and time again, it has proved to be the right call for the specifics of certain applications.

I'd stear clear of joining a local table with a remote table using 4 part naming convention. This type of cross-instance joining is not efficient for a number of reasons, and even with a predicate to filter the result, there is still a potential to pull every record across the network because of the limitations of how remote tables are joined.

What I'd reccomend for a first attempt is creating a local temp table #s, and then use a pass-through style remote query to select into this #s only the (2) columns you really need for the update. Adding a search predicate to the remote query to pair down the number of rows returned would also help a lot.

create table #s

(

id int not null primary key with (ignore_dup_key = on),

SomeValue varchar(30) not null

);

insert into #s ( id, SomeValue )

exec('select id, SomeValue from xref.dbo.Symbols') at lnk_sB;

update t

set someValue = #s.SomeValue

from #myTab t

inner join #s

on t.id = #s.id;

You can still add a NOLOCK hint to the Symbols query, but I doubt it's needed. The only reason for it would be if the table is constantly getting inserts, and it's very important not to temporarily block whatever process or application inserts this table. If this is simply a table containing one row for each industry stock symbol, then I don't think either blocking or dirty data is a big concern here. The primary key on #s with (ignore_dup_key = on) will prevent insertion of any duplicates even if there were some returned.

By default I'd just not use NOLOCK.

I would generally recommend at least a glance at the following page:

Isolation Levels in the Database Engine

due to the very simple table, which boils down to:

Level Data issues possible

READ UNCOMMITTED Dirty reads, Nonrepeatable reads, Phantom reads

READ COMMITTED Nonrepeatable reads, Phantom reads

REPEATABLE READ Phantom reads

SNAPSHOT (not listed, but tempdb becomes critical, and "If a snapshot transaction attempts to commit an update to a row that was changed after the transaction began, the transaction is rolled back, and an error is raised." - Snapshot Isolation in SQL Server)

SERIALIZABLE

It's not just NOLOCK or not :).

I'm guessing that Symbols is essentially a reference table containing one row for each company or index fund traded on a stock exchange. If that's the case, then this was probably bulk loaded in the past, gets heavily queried by multiple systems throughout the day, but only occasionally gets write transactions.

Another option is that you extract a copy of this remote table to your local server and then leverage and SSIS package to periodically perform incremental slowly changing dimension inserts and updates. Perhaps that's actually what you're currently doing but with T-SQL.

From the video Matt Miller posted, that would depend. A small remote table would not cause issues here. In this case since most of the data is on the remote end, the user did a smart thing to make what was the remote server now the local server... especially if the original local table is small.

How about options like this?

update t

set someValue = s.SomeValue

from openquery(lnk_sB,'

select *

xref.dbo.Symbols s') s

inner join #myTab t

on t.id = s.id

update t

set someValue = s.SomeValue

from #myTab t with (nolock)

where exists ( select id from lnk_sB.xref.dbo.Symbols

where t.id = s.id )

Hey JeeTee,

So which of these ponies runs fastest?

I'm placing my money on the INSERT #S EXEC() AT method I described earlier.

:satisfied: