| Author |
Topic |
|
schmidtg
Starting Member
36 Posts |
 Posted - 2007-04-13 : 16:20:39
|
| I have a SQL procedure that can take several minutes to complete. I allow users to initiate the process through a web site and view a progress bar. The process is spun off onto a separate thread so that the UI doesn't wait. When the process is running, though, page loads on the site that interact with the db slow to a crawl or time out completely. That long running process seems to block all other queries on the database. Is there a way to give this process a low priority or somehow throttle its resource use so that the other web processes can get a chance to run in a timely manner? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-13 : 16:22:55
|
quote:
Originally posted by schmidtg
Is there a way to give this process a low priority or somehow throttle its resource use so that the other web processes can get a chance to run in a timely manner?
No. Could you post the code so that we can help fine tune it?Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
schmidtg
Starting Member
36 Posts |
Posted - 2007-04-13 : 16:33:01
|
quote:
Originally posted by tkizerNo. Could you post the code so that we can help fine tune it?
I don't think there's much to be tuned unfortunately. It's just deleting many, many records from several very large, heavily indexed tables. I've thought about having it repeatedly delete in small chunks so other processes might have a better chance to run but I was hoping there might be a better solution. Any suggestions? |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-13 : 16:39:29
|
| Yes delete in smaller chunks or you can do a 'soft delete'. Have a status column in those tables and mark it as flagged. Then have a nightly job delete all those records when the load isnt that high. This might require application changes too to include a condition in the WHERE clause.************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-13 : 16:58:33
|
| Yes that it exactly what you should do. This is how we handle all mass deletes.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
schmidtg
Starting Member
36 Posts |
Posted - 2007-04-13 : 17:12:58
|
quote:
Originally posted by tkizer
Yes that it exactly what you should do. This is how we handle all mass deletes.
Which? The chunked deletion or the nightly job? I don't think the latter is an option for us. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-13 : 17:18:45
|
| I think Tara is talking about the batch delete.************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
schmidtg
Starting Member
36 Posts |
Posted - 2007-04-13 : 17:37:57
|
| Great, thanks for the help guys.While I have your ear, does having this all wrapped in a single transaction have any bearing on the locking? We probably need to be able to roll back all changes if an error occurs, regardless of how we end up structuring the deletion. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-13 : 17:42:18
|
| if you need to rollback then you are better off running it in a transaction and check for @@ERROR after each DELETE.************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-13 : 17:42:59
|
| Yes it has a huge bearing. The controlled/batch delete approach is done with multiple transactions. So you can not rollback the entire delete if an error occurs.Perhaps you could do something like this:Create a table that matches the structure of the table to be deleted fromInsert rows into this new table that are to be deleted from the actual table (batch inserts)Delete rows from your actual table (batch deletes)On error of anything, insert data from new table into actual table (batch inserts)You will need to write a bunch of code to do the "rollback".Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
schmidtg
Starting Member
36 Posts |
Posted - 2007-04-13 : 17:48:09
|
| Ugh. That's what I was afraid of. I think we may be able to do without the rollback ability fortunately. |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-04-14 : 03:04:47
|
| Just for my own benefit, what could ever cause a delete to need to be rolled back do to a error if the procedure was written correctly?I can see a update or insert, but how would a delete ever cause a error? |
|
|
|
schmidtg
Starting Member
36 Posts |
Posted - 2007-04-14 : 09:34:09
|
quote:
Originally posted by Vinnie881
Just for my own benefit, what could ever cause a delete to need to be rolled back do to a error if the procedure was written correctly?I can see a update or insert, but how would a delete ever cause a error?
Mostly non-cascading foreign key violations. |
|
|
|
|
Throttling a long-running procedure