Alternative methodes ... TO AVOID CURSORS....
-
I want avoid using 'cursors' in t-sql programming,give some idea to avoid cursors
-
using temp table is one way select into the temp table that has and ident column on it then use a while loop to loop through it. The best thing though is to find a set based way to remove the cursor using joins, subselects, or dirived tables.
If you have an example of what you are trying to do we can provide an example of how to do that without a cursor.
Wes
-
Try to use SELECT statement first with CASE in within to select the data that you want..
once you are happy with that.. you can either use SELECT INTO NEW_TABLE_NAME or you can use INSERT INTO(COLUMN NAME) followed by the SELECT statement.
-
try a while loop - works for me <grin >
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Cursors, while loops, temptables... They are all 'bad'
If you mean by your question "how do I think to avoid cursors", the answer is - you have to learn to think set based.
All three things above (the "bad" things) are ways to do things when either you can't figure out the "proper" set based solution, or when there actually is no set based solution.
In about 99 cases out of 100, there is a setbased way to do practically anything. Admin code tend to be more cursor or loop oriented, but that's mainly due to the charachteristics of administrative tasks. For online / user type of queries, there is rarely need for it.
The road to avoiding cursors is to understand SQL.
/Kenneth
-
Ken is right: you should use set-based commands to reduce your dataset to just those rows you want to see. Then let your display program handle the rest - it'll have to do all that anyway, so there'll be no extra loss there
Mike
-
Cursors are not necessarily bad!!! (Begin huge religious argument
)It has been shown that from a performance perspective for queries with low utilization and small result sets set-based processing has no performance advantage.
For very large result sets, or high-utilization transactions that could cause locking issues, set-based processing is significantly better than cursor-based processing.
You not only have to know SQL, you have to know your data and application. Good design is the first step, proper coding the second.
I've rewritten a number of "legacy" cursor-based functions with large working tables or result sets into set-based logic and achieved tremendous performance improvements (from minutes down to seconds). On the other hand I've rewritten TSQL code that only processes 5 rows and is only run once or twice a day by an application administrator and found I wasted my time. No improvement was achieved in application performance, and that's the bottom line, not the "purity" of the TSQL code.
-
I agree about not getting into cursor arguments and set arguments.
Not all data operations are set based, in these cases where row by row processing is required then the use of curors and while loops can be very useful. One big difference between a cursor and a while is that the whole while is seen as one transaction whereas cursor loops are seen as individual transactions ( normally - without explicit transaction commands ) these can factor your decision on how to approach a problem.
Dba's don't always get to choose the data or databases they work with, and I'm sure neither do developers.
It's also interesting to examine in fine detail the execution plans of your carefully crafted T SQL only to find that underneath sql server is effectively using cursors !! ( check out threads and articles on how using a function in your select forces a cursor operation )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Very true that we don't always get to pick the db structure!
I have a database that is published from another company. Even though I fought long and hard to have our front end use their database structure, I was overruled.
So now I have to use triggers, cursors, stored procedures to move their data to a database with our structure.
In my case, I do not see an alternative to cursors since my trigger fires when a row(s) is updated or inserted. I had originally thought it would only fire for each row but testing found there could be any number of rows at one time.
So I have to use a cursor to cycle through the updated set and then for each, call a stored procedure to move the data into our database.
-
At least you're calling a Stored Procedure
You wouldn't believe the code I've seen in some triggers where the trigger cursored through the updated rowset running hundreds of lines of TSQL code against each row! -
Triggers (as you have found) fire once for every update, not once for every row.
To deal with this, the trigger should be written in a way that handles more rows than one (ie set based).
There shouldn't be a need for a cursor in the trigger even though several rows may be updated/inserted/deleted...
(emphasis on shouldn't)
/Kenneth
-
Kenneth,
In keeping with the topic, I would love to see how I can get each [id] from the [updated] table and call a stored procedure using the [id] column. There is also another column that is sent as a part of the SP ([class]).
Right now, I cursor through and get the [id] and [class] and use them something like this:
exec qst_insert_ + [class] + id = [id]
or
exec qst_update_ + [class] + id = [id]
(oh my, cursors and dynamic SQL - am I going to hell?)
where I have 12 SPs just for each class and insert/update.
And that is just for one table!
-
From the perspective of someone who probably thinks more "cursor" than "set-based", can anyone out there recommend a book or two that deals with exactly what set-based programming and how to begin thinking that way?
In other words, do you have any recommendations on books that deal with set-based SQL?
Thanks...
-
I think that sometimes the exclusion of cursors is over emphasised, I've seen some wonderful ( and horribly inefficient ) code to avoid cursors that takes much longer and creates much more i/o.
It's really a case of being aware of the dangers of cursor overhead and using set based sql whenever possible. Any good sql book will do , my favourite, now a little out of date, is Transact SQL Programming isbn 1-56592-401-0
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Well, if the trigger doesn't do anything than call an insert or update procedure, then you're stuck. With that design you're forced to cursor through each insert or update - which also means that you nowhere in the system, from anywhere can send a batch like;
INSERT tableA VALUES (1,2,.....)
INSERT tableA VALUES (2,3,.....)
GO
or
UPDATE a
SET a.col1 = b.col1
FROM tableA a JOIN tableB b
ON a.pk = b.pk
GO
The triggers implemented in this way effectively turns off all setbased operations on the database. Its design doesn't allow setbased operations (or so it seems if I understand it correctly)
/Kenneth
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply