Help with re-writing a cursor to a set based solution

Question

Help with re-writing a cursor to a set based solution

Jim.A

SSC Journeyman

Points: 94
More actions
May 12, 2010 at 11:23 am

#386917

Hi all,
I inherited a lot of code that was written back in the early 2000's and recently it's started to cause some errors in different environments.
This particular code is part of a udf that returns a table. What's interesting is that there will be a discrepancy of 5 - 10k records in different environments. While one solution is to change it into a sp and use a permanent table with locking, I would really like to figure out a set based solution if possible that will work in all environments.
Essentially the cursor is created to read from the table variable, but it also writes to the same table during it's loop. The intention is to discover a dependency chain.
--Seed
INSERT INTO @ModelJob(ModelName, ModelID, MType, PModelID, TreeLevel, GPModelID, PIndex, GPIndex, PModelType)
SELECT fnTable.ModelName, fnTable.ModelID, fnTable.MType, @ModelID, 1, 0, 1, 0, 0
FROM [dbo].[fn_GetModels](@ModelID, @ModelType, @Dir) fnTable
SET @i = 2;
DECLARE ModelUsageCursor
CURSOR FOR
SELECT ModelName, ModelID, MType, PModelID, TreeLevel, PIndex, PModelType
FROM @ModelJob
OPEN ModelUsageCursor
FETCH NEXT FROM ModelUsageCursor INTO @ColName, @ColID, @ColType, @ColParentID, @ColLevel, @ParentIndex, @PMType
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @ModelJob(ModelName, ModelID, MType, PModelID, TreeLevel, GPModelID, PIndex, GPIndex, PModelType)
SELECT fnTable.ModelName, fnTable.ModelID, fnTable.MType, @ColID, @ColLevel + 1, @ColParentID, @i, @ParentIndex, @PMType
FROM [dbo].[fn_GetModels](@ColID, @ColType, @Dir) fnTable
SET @i = @i + 1
FETCH NEXT FROM ModelUsageCursor INTO @ColName, @ColID, @ColType, @ColParentID, @ColLevel, @ParentIndex, @PMType
END
CLOSE ModelUsageCursor
DEALLOCATE ModelUsageCursor
If there's any input anyone can give, I would be greatly appreciative.
Thank you,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

do you have any sample data with expected results? It's hard to determine what you need without seeing the function, tables, data, etc.. Also, what have you tries so far?

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jim.A SSC Journeyman Points: 94 More actions · Answer 2

I apologize. I was trying to show 'real' code instead of a mock up so you could get an idea of the environment. Also the full function itself is over 300 lines of code loaded with goto's, lookup functions for mutliple tables etc. It was something that I felt wouldn't translate well to a forum environment.

I'll see if I can present a version that can accurately portray the issue.

Thank you for the response,

Jim

WayneS SSC Guru Points: 95460 More actions · Answer 3

Jim,

In order to help you replace this with a set-based routine, we will need the following information from you:

1. DECLARE @ModelJob TABLE statement (so we know the structure of what we're dealing with.

2. Table structures (in the form of CREATE TABLE statements) for all other involved tables.

3. Code for the fn_GetModels function.

4. Some sample data to work with, in the form of INSERT INTO statements.

5. Expected output, based on the provided sample data.

Since you're using an incrementing number (@i), I suspect that this can be made into a set-based solution with the use of a tally table.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

Jim.A (5/12/2010)
I apologize. I was trying to show 'real' code instead of a mock up so you could get an idea of the environment. Also the full function itself is over 300 lines of code loaded with goto's, lookup functions for mutliple tables etc. It was something that I felt wouldn't translate well to a forum environment.
I'll see if I can present a version that can accurately portray the issue.
Thank you for the response,
Jim

You can always attach the function code as a text file. But, if it's as bad as you say, it may be better just to describe what the function is meant to do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)