question

Matt Whitfield avatar image
Matt Whitfield asked

I'm using a cursor in production code - does that make me a bad person?

Just wanted to get your points of view.

I need to disable foreign keys that point to a particular set of tables while those tables are modified (dropped & recreated) and then have their data re-inserted (it is stored in a separate table first).

I am using a cursor to iterate through the foreign keys which point to any of the tables in question, and disable them. I then use another cursor to re-enable them after the operation is complete.

I need to do this at run-time, because the code can run on anyone's database, so I can't avoid having to deal with foreign keys which could potentially be system-named constraints.

In this instance, i chose to use a cursor because I think the performance differences will be very minimal (if noticeable at all), and it actually makes the code more readable for generating and executing the DDL that I need.

What do you think?

t-sqlperformancecursorddl
4 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Bob Hovious avatar image Bob Hovious commented ·
Yes, you are evil and must be destroyed !! (J/K)
2 Likes 2 ·
David Wimbush avatar image David Wimbush commented ·
+1 for the title. Beautiful!
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
I am tempted to say yes you are, but i don't. :) Sometimes a cursor i faster or doesn't have any negative effect on performance and is easier to implement.
0 Likes 0 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
That is the funniest title I have seen on this site yet.
0 Likes 0 ·

4 Answers

·
Jack Corbett avatar image
Jack Corbett answered

This is one of those instances where I would think a cursor would be appropriate. Another option is to create the script(s) to drop the FK's and reapply them after and use sqlcmd to call the scripts that you have saved off in a file.

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
I'm not sure the file one would fly for me - basically this is in a schema comparison tool - and one of the output modes is in script - so it has to be a 100% T-SQL approach...
0 Likes 0 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
I tend to find while loops with table variables to be more readable, but that is largely a matter of taste. I think using something like a cursor in this case is certainly understandable and would be very hard to avoid.
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered

I second Jack's answer. There are a few things where there just doesn't seem to be a set-based way. More on the DDL side than DML. I just checked and there's definitely no WHERE clause in the ALTER TABLE ... DROP CONSTRAINT syntax.

Hmmm. It would be nice if you could do DDL on objects by INNER JOINing to a SELECT that returns the names or IDs of the objects to act on. It would make my nightly data warehouse rebuild job a lot less brittle.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
I certainly could have done it that way, by selecting into a variable and building up the statement as needed - but I didn't for two reasons - 1) The limits for strings in SQL Server 2000 and 2) The readability of the cursor...
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered

I think a lot of the 'bad' things about cursors is the way that some people use them incorrectly, when a more set-based solution is both 'better' and faster. Often procedural thinking programmers fall into the trap that they must deal with data one row at a time, rather than taking the whole data set as one. It's no-ones fault, just a different mindset.

So seconding and thirding (is that a word?) what has already been said here, there's nothing wrong with a cursor based solution if it's right for the job, and you go into it with eyes wide open. Have you ever looked at some of the system procs that come with SQL Server - riddled with cursors!

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

pxt_ce avatar image
pxt_ce answered

cursor is most expensive as we know already. in most case we use cursor for looping only...

we can do the same looping using table variable.

let me describe.

-use table -insert those records to table ( SELECT statement of Cusrsor) -count the number or row. -take while loop till count -same process logic in while loop.

i am using this as an alternative solution to cursor.

hope this will help....

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
You might want to re-look at that, because a fast forward read only cursor isn't any good for large scale data manipulation, but it will be faster than a while loop
1 Like 1 ·
pxt_ce avatar image pxt_ce commented ·
this is new thing to me.. need to verify on screen.. thanks for info. in that case what should be prefered? FastFwd Cur OR While loop?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.