| Author |
Topic |
|
shayantani
Starting Member
4 Posts |
 Posted - 2009-11-04 : 12:32:13
|
| Hi guys,I was wondering if there is a way to avoid table writes(select into or insert into) inside cursors. I mean, if i can use the data that is being formed inside the cursor without storing in tables(temporary or permanent)?As u understand the cursor goes for 120 times, so I was trying to avoid writing into tables (not 120 separate tables because i create the table at the beginning , use it to perform the required task and then drop it ; so its not 120 separate tables but one table being written and overwritten). The logic works great, but it took about an hour for the execution. When i look into the execution plan, it shows that the table write part took maximum %age ; hence the need for shortening the time period.Would appreciate any kind of help.Thanks in advance |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-11-04 : 12:39:28
|
| I think the short answer is no. But, if you want to improve performance you'll probably want to get rid of the cursor and use a set-based method.If you want more help feel free to check out this link and get back to us with more details:http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
 |
|
|
shayantani
Starting Member
4 Posts |
Posted - 2009-11-04 : 13:40:12
|
| @lamprey,could you please let me know how to deal with this by getting ' rid of the cursor and use a set-based method'. I have to go through the data 120+ times, and that is why i use a cursor.Also if you could explain what you mean by a set based method?Thanks |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-11-04 : 13:54:08
|
| I'm not sure how to best explain a set based method, but basically if you want to update a table you don't loop through doing singleton updates you do it in a single statement that is set based. Relation Databases deal with sets and that is pretty fundamental to the job. If you are not up to speed I'd suggest you do some reading to help you better understand relational and set theory.Hard to say how to make it set based since you haven't given us any information about your particular issue/task. Or if the type of processing you are performing can even be translated to a set based methos. But, if you want to provide information we might be able to help. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
|
|
shayantani
Starting Member
4 Posts |
Posted - 2009-11-04 : 14:46:02
|
| @lamprey,was just wondering , if i could use table variables in this case. I know we cant do select into @TableVariable from ExistantTable, so i require to declare @TableVariable first and then insert into. So my question is if I could put the data into @TableVariable in the cursor(which i am thinking i can), but i dont know how to delete the data from @TableVariable during the second and other iterations henceforth.Thanks for all help@TG: i was not aware that both are different ; new to sqlteam.com |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-11-04 : 14:55:48
|
Sure you can. When you say delete the data do you mean all the data or specific rows? If all the data you just do:DELETE @TableVariable |
|
|
|
shayantani
Starting Member
4 Posts |
Posted - 2009-11-04 : 15:37:51
|
| to answer your question. I want to delete all the data and not just specific rows.Now coming to my point , cant I just clear the data from the @TableVariable instead of deleting it. Deleting it would mean that it would be declared again at the beginning of the cursor in all consequent iterations ; do you think this would take a lot of time?Thanks again! |
|
|
|
|
avoid table writes in cursor
Be One with the OptimizerTG