Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Improve performance of query involving linked server Expand / Collapse
Author
Message
Posted Tuesday, April 2, 2013 11:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:30 PM
Points: 76, Visits: 290
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
Post #1438019
Posted Wednesday, April 3, 2013 8:49 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:08 PM
Points: 653, Visits: 3,841
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
Post #1438402
Posted Wednesday, April 3, 2013 10:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:30 PM
Points: 76, Visits: 290
I can't do that, unfortunately.

-Rex
Post #1438456
Posted Wednesday, April 3, 2013 10:26 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:08 PM
Points: 653, Visits: 3,841
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?

Post #1438467
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse