Alternative to Using a Cursor - Execute one or both stored procs for each ID

Question

 Hello, I'm working on a new project. I'm trying to figure out how to fire off either one or both stored procedures based on values stored in two columns. And the stored procs need to execute per case's ID. 
 It has to be efficient and scalable so it can process upwards of 1000 files. So far I've coded a cursor to pass each case's ID into a variable to be used by either or both stored procs as well as the two columns where the two conditions come from. The cursor I programmed is really slow and I doubt I can use it. Its my rough draft of what I want the code to do.
 I wanted to know how could I accomplish this "without a cursor". I was thinking of using a while loop with some dynamic SQL to execute the stored procs for each ID. I could use some SQL advice thanks in advance. I've included what I've programmed so far.

DECLARE @CaseID varchar(4),
		@ChangesOnly int,
		@FullFile int

DECLARE FileFeed CURSOR FOR
SELECT [CaseID], [Run Changes Only],[Run Full Files] FROM CaseConfig WHERE [Run Changes Only] = '1' OR [Run Full Files] = '1'

OPEN FileFeed
FETCH NEXT FROM FileFeed INTO @CaseID, @FullFile, @ChangesOnly

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @ChangesOnly = '1' AND @CaseID = @CaseID
  BEGIN
    EXECUTE [ProcessChangesOnly]@CaseID
  END 
  IF @FullFile = '1' AND @CaseID = @CaseID
  BEGIN
	EXECUTE [ProcessFullFile] @CaseID
  END
	FETCH NEXT FROM FileFeed INTO @CaseID, @FullFile, @ChangesOnly
END

CLOSE AflacFileFeed;
DEALLOCATE AflacFileFeed;

Answer 1

There is one thing you can try to with the current solution and that is to change

DECLARE FileFeed CURSOR FOR

to

DECLARE FileFeed CURSOR STATIC LOCAL FOR

The default cursor type is a dynamic cursor, which means that the cursor is evaluated for every FETCH. With a STATIC cursor, the query is executed once and the result is stored in a hidden temp table and the cursor is served from table. It is actually not uncommon to see code with loops where the slowest moment is to find the next row.

But more likely it is as Bruce says, the issue is inside the stored procedure. So if making the cursor static does not save the show, you will need to roll up the sleeves and start tuning.

The first thing is to see if there is any particular statement in your procedures that are slow. When you run things in a loop like this a scan over an non-indexed temp table with a modest number of 30000 rows can be a killer, because it happens again and again.

On my web site, you find the tool sp_sqltrace, written Lee Tudor. It's a really great tool to find bottlenecks in loops. But in the end, the long-term solution is to re-write these procedures so that they can handle sets of data. Or maybe rather writing new procedures, and keeping the old ones as wrappers on the old one, if there is code that need to be able to call these procedures by ID. And, no, this is by no means a simple task. How difficult it is, I don't know, but I've made this task with some long stored procedures, and it was certainly not a walk in the park. Not the least because you need a complete different mindset for a set-based solution than a scalar solution.

Answer 2

the cursor is not the slow part. it is that you sequentially call the stored proc's to process one case id at a time. you have two options.

1. move the cursor to an application that can run multiple threads so more than one case is processed at a time
2. change the stored proc's to perform set options and process more than one case id at a time.