SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Improve performance of query involving linked server


Improve performance of query involving linked server

Author
Message
SQLCurious
SQLCurious
Mr or Mrs. 500
Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)

Group: General Forum Members
Points: 569 Visits: 452
I am trying to make improvements to a job that does the following.

1) Load a table with about 40,000 rows (Pretty straight forward, so there isn't much to change here)

2) Call a stored proc that joins this table's data with big set of data from a linked server. This proc reads data from the above table (where one of the columns is flagged '0'), and does a lot of gimmicks to process it and sets the flag to '1'

3) This stored proc is coded such that it takes a parameter for the number of rows to be processed. Currently the call is made with @rowsToProcess as 250. The code looks as below...

WHILE EXISTS (SELECT 1 FROM MyTable WHERE MyFlag = 0)
BEGIN
EXEC dbo.MyProc
@Server = 'MYREMOTESQLSERVER',
@DBName = 'MYRemoteDBN',
@rowsToProcess = 250
END

Can anyone suggest if there is a better idea to run this more efficiently? For now, I only have room to play around how to call this proc to process all rows in "MyTable". In other words, I can't influence how data is organized/arranged in the tables on the linked server.

Thanks in advance,
Rex
Chrissy321
Chrissy321
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4224 Visits: 4816
Can you put the procedure on the remote server and call it from your primary? That has worked fro me in some scenarios.

EXEC RemoteServer.RemoteDB.dbo.MyProc
SQLCurious
SQLCurious
Mr or Mrs. 500
Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)

Group: General Forum Members
Points: 569 Visits: 452
I can't do that, unfortunately.

-Rex
Chrissy321
Chrissy321
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4224 Visits: 4816
If I understand correctly the procedure is called multiple times until all rows are processed. Every time its called it needs to join with the large remote data set. I would think the large remote data set is being dragged over the network every time.

Can you pull the remote data set back to the local server into a temporary or permanent table? If the data is staged locally then your joins will be much faster.

My other thought is to call the procedure once and process all the data at one time. Is the @rowsToProcess parameter an attempt to address performance issues?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search