Remote Scan on Linked Server - Fast SELECT/Slow UPDATE

[Tammy!]

In an ASP, I have a dynamically created SQL statement that amounts to "SELECT * FROM Server1.myDB.dbo.myTable WHERE Col1 = 1" (Col1 is the table's primary key). It returns the data immediately when executed.

However, when the same record is updated with "UPDATE Server1.myDB.dbo.myTable SET Comments = 'blah blah blah' WHERE Col1 = 1", the page times out before the query can complete.

I watched the program in Profiler, and I saw on the update that sp_cursorfetch was being executed as an RPC once per each row in the table. In a table of 78000 records, the timeout occurs well before the last record is fetched, and the update bombs.

I can run the same statements in Query Analyzer from a linked server and have the same results. The execution plan shows that a Remote Query is occurring on the select that returns 1 row, and a Remote Scan is taking place on the update scanning 78000 rows (I guess this is where all the sp_cursorfetch calls are happening...?).

How can I prevent the Remote Scan? How can I prevent the execution of the RPC sp_cursorfetch for each row in the remote table?

Thank you!

[Jer]

Tammy

One thing that works for me is the OPENQUERY command (see bol for more details). that works for isql. for the asp page, i would try to just create the connection directly to the db server your updating.. or do you have to go through the linked server? i can't see why you would have to... I've never had to do that. I usually just create another connection in the global.asa and then call it from the .asp page directly... thus avoiding the middle database server entirely.

Jer


------------
Tammy! at 8/14/01 11:47:57 AM

In an ASP, I have a dynamically created SQL statement that amounts to "SELECT * FROM Server1.myDB.dbo.myTable WHERE Col1 = 1" (Col1 is the table's primary key). It returns the data immediately when executed.

However, when the same record is updated with "UPDATE Server1.myDB.dbo.myTable SET Comments = 'blah blah blah' WHERE Col1 = 1", the page times out before the query can complete.

I watched the program in Profiler, and I saw on the update that sp_cursorfetch was being executed as an RPC once per each row in the table. In a table of 78000 records, the timeout occurs well before the last record is fetched, and the update bombs.

I can run the same statements in Query Analyzer from a linked server and have the same results. The execution plan shows that a Remote Query is occurring on the select that returns 1 row, and a Remote Scan is taking place on the update scanning 78000 rows (I guess this is where all the sp_cursorfetch calls are happening...?).

How can I prevent the Remote Scan? How can I prevent the execution of the RPC sp_cursorfetch for each row in the remote table?

Thank you!

[Jeff Putnam]

I've got a couple of suggestions:

1) Put the update in a stored proc that lives on the remote server. You'll avoid the RPC call that way.

2) Index the field that you reference in the where clause.


------------
Tammy! at 8/14/01 11:47:57 AM

In an ASP, I have a dynamically created SQL statement that amounts to "SELECT * FROM Server1.myDB.dbo.myTable WHERE Col1 = 1" (Col1 is the table's primary key). It returns the data immediately when executed.

However, when the same record is updated with "UPDATE Server1.myDB.dbo.myTable SET Comments = 'blah blah blah' WHERE Col1 = 1", the page times out before the query can complete.

I watched the program in Profiler, and I saw on the update that sp_cursorfetch was being executed as an RPC once per each row in the table. In a table of 78000 records, the timeout occurs well before the last record is fetched, and the update bombs.

I can run the same statements in Query Analyzer from a linked server and have the same results. The execution plan shows that a Remote Query is occurring on the select that returns 1 row, and a Remote Scan is taking place on the update scanning 78000 rows (I guess this is where all the sp_cursorfetch calls are happening...?).

How can I prevent the Remote Scan? How can I prevent the execution of the RPC sp_cursorfetch for each row in the remote table?

Thank you!