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)
@Server = 'MYREMOTESQLSERVER',
@DBName = 'MYRemoteDBN',
@rowsToProcess = 250
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,