April 8, 2011 at 8:04 am
Hi all,
I know that cursors are evil, so I try not to use them. I've read that they are evil because they lock the table that you are iterating through, so no other processes can use that table while your cursor is running.
My question is - what if you use a cursor to iterate through the results returned to you via a table-valued function. Is it locking that function out somehow in the same way? Or is that not so bad?
Thanks!
-Patrick
April 8, 2011 at 8:18 am
I don't think that cursors are evil. I do think that cursors are being misused a lot and this is what gave them there reputation. You have to understand that SQL is a language that works on set of records. When you work with cursors you actually work on one record. If for example I have to delete 20 records. I can issue a single delete statement with the proper where clause or I can create a cursor that gets the records' primary key and then each time delete a single record. In both cases I'll have the same results – the 20 records will be deleted, but doing it with one delete statement is the better way (in term of coding, resource used by the server, logging, etc'). Of course I gave a very extreme example. I'm sure that the vast majority of us will never try to implement a simple delete with a cursor, but the point is that in the majority of the cases where cursors are being used, a better way can be found to solve the problem with statements that work on set of records. There are however cases that I do work with cursors. Most of the time it will be on administrative tasks such as reducing fragmentation by rebuilding/reorganize indexes.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
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/
April 8, 2011 at 8:22 am
Okay. Here's my situation. I have a function that accepts as a parameter a field that is a comma-seperated value. That function returns that data as seperate rows. So, if I pass it the field 'Hello, there, how, are, you', it returns to me this table:
********
* Hello *
* There *
* How *
* Are *
* You *
********
I've been using a cursor to loop through that result set to execute other actions (a select statement and then an update statement, specifically) using those returned rows.
There's no way this particular use of the function will ever return any more than 10 rows. So...in my example, is a cursor the appropriate way to go?
April 8, 2011 at 8:30 am
Can you post your "other" actions. There may be a way to solve your problem without a cursor.
Try thinking "SET BASED" and apply the select/update to the result set of the function all at once rather than one line at a time.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 8, 2011 at 8:34 am
Okay. Here's my actual code. What I'm doing is building an interface between two systems, and I'm setting patient disease indicators in one system based on data received in a CSV format from another.
BEGIN
--Get patient's flags from comma-seperated varchar value ptFlags
DECLARE flagCursor CURSOR FOR
SELECT * FROM splitCSV((SELECT ptFlags FROM Inserted), ',');
OPEN flagCursor
SET @rowCount = (SELECT COUNT(*) FROM splitCSV((SELECT ptFlags FROM Inserted), ','));
SET @lcv = 0;
WHILE @lcv < @rowCount
BEGIN
FETCH NEXT FROM flagCursor INTO @tempFlagIndicatorVar;
IF @tempFlagIndicatorVar IN (SELECT flagIndicator FROM FlagIndicators)
BEGIN
IF @ptFlagIndicator1Var IS NULL
BEGIN
SET @ptFlagIndicator1Var = (SELECT id FROM FlagIndicators WHERE FlagIndicator = @tempFlagIndicatorVar);
END
ELSE IF @ptFlagIndicator2Var IS NULL
BEGIN
SET @ptFlagIndicator2Var = (SELECT id FROM FlagIndicators WHERE FlagIndicator = @tempFlagIndicatorVar);
END
ELSE IF @ptFlagIndicator3Var IS NULL
BEGIN
SET @ptFlagIndicator3Var = (SELECT id FROM FlagIndicators WHERE FlagIndicator = @tempFlagIndicatorVar);
END
ELSE IF @ptFlagIndicator4Var IS NULL
BEGIN
SET @ptFlagIndicator4Var = (SELECT id FROM FlagIndicators WHERE FlagIndicator = @tempFlagIndicatorVar);
END
ELSE IF @ptFlagIndicator5Var IS NULL
BEGIN
SET @ptFlagIndicator5Var = (SELECT id FROM FlagIndicators WHERE FlagIndicator = @tempFlagIndicatorVar);
END
ELSE IF @ptFlagIndicator6Var IS NULL
BEGIN
SET @ptFlagIndicator6Var = (SELECT id FROM FlagIndicators WHERE FlagIndicator = @tempFlagIndicatorVar);
END
SET @lcv = @lcv + 1;
SET @tempFlagIndicatorVar = NULL;
END
END
CLOSE flagCursor
DEALLOCATE flagCursor
April 8, 2011 at 10:21 am
This isn't the whole trigger, is it? What are you doing with the variables @ptFlagIndicator1Var, @ptFlagIndicator2Var, etc., after you set them?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 8, 2011 at 10:24 am
No, that's not the whole trigger. Other parts of the trigger just figure out what other variables need to be set to - but they've been in place for a very long time and we never had any slowness issues with them. This cursor though, might be responsible for some slowness, I'm not sure - but it's the only thing that I've changed recently that I can think of as being a culprit.
At the end of the trigger, a single update statement to a table called [Current] is fired, which has these variables in them. Nothing special, just an ordinary UPDATE statement.
April 8, 2011 at 10:41 am
A few notes/comments/questions:
1. Is this for a trigger, it appears so?
2. Where do the other variables get set? @ptFlagIndicator1Var, @ptFlagIndicator2Var, etc...
3. The "simple update" at the end IS relevant to our suggestions, please include it.
4. At first run through, this does appear to be an ideal candidate for removing the cursor all together.
Post the COMPLETE code, and sample data with results and I'd be happy to help.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 8, 2011 at 10:56 am
The COMPLETE code is over 1500 lines long, and a whole lot of it isn't relevant to our conversation. Plus, I can't post it due to intellectual property laws. What I can tell you is that the update at the end of the trigger, after the variables are set, is simple. For our conversation, it looks like this:
UPDATE [Current] SET /*Other fields to other variables*/, ptFlagIndicator1 = @ptFlagIndicator1Var, ptFlagIndicator2 = @ptFlagIndicator2Var, etc.... WHERE nurseStation = @nurseStationVar AND roomNo = @roomNoVar AND bedNo = @bedNoVar;
That's all it is.
April 8, 2011 at 2:11 pm
Is this the only cursor in this trigger?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 8, 2011 at 2:12 pm
Yes sir, it is.
April 8, 2011 at 2:13 pm
Yes it is.
April 8, 2011 at 2:35 pm
Then how do these variables get set in the where clause of the update statement?
WHERE nurseStation = @nurseStationVar AND roomNo = @roomNoVar AND bedNo = @bedNoVar;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 8, 2011 at 2:37 pm
An earlier portion of the trigger. They actually come straight in from the INSERT statement that initially fires off this trigger.
Does any of that really matter? The only question I'm asking is if I'm using a cursor correctly, and, if not, what would be a better way to do what I'm doing? There is nothing else in the entire trigger that is malfunctioning or working slowly.
April 8, 2011 at 2:47 pm
If you're populating them by selecting from the inserted virtual table, then your trigger has a big problem - it is coded to handle just one record inserts, instead of sets. If you happen to pass in a set, you'll only be working with one random row from the set.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply