October 11, 2004 at 7:31 am
Hi,
Scenario:
I have a database which is used solely by me but it sits on the same server as the other databases.
I created two delete stored procs which delete a customer. In the main delete stored proc(1) I started off with a transaction. Inside the transaction of sp(1) it contains two level of nested cursors.
Inside the cursor, it calls sp(2) that also start a new transaction and it has cursor in it. Within the cursor of sp(2), if there's any error, it will close and deallocate the cursor then jump to the ErrorExit routine whick rollback the trans inside (sp2).
Inside sp(1) if there is any error inside the lowest level of cursor, it will close and doallocate the cursor 1 and cursor 2 then jump to the ErrorExit routine that rolls back the trans. If there isn't any error, it commits the transaction.
I tested the sps by deleting 5, 10, ... 100 customers and it works OK.
I create a sql job which deletes 10 thousands customers. So I call the delete sp I created earlier within a cursor 10000 times.
I ran the job and it seemed to cause other user all sort of problem, time out error and the response time for every one on the server was very slow.
Questions:
1. If I cancel the job, will there be an orphan transaction(s) left hanging around? it yes, what can I do to make sure that the transactions have been rolled back?
2. what about the cursor(s), will they be close by sql server automaticall? if not what can I do to ensure that they have been closed and deallocated.
3. Is this a common pratice to have two level of transactions and three nested level of cursor?
Your help is very much appreciated.
October 12, 2004 at 4:02 am
I;m sure if you work out the number of iterations this process will be going through, you'll understand why it's taking so long! ... and for the entirity of this time period, you transactions will be locking the tables involved, which is why you're seeing the timeout errors as well.
The cardinal rule is to keep your transactions as short as possible!
I've certainly done similar ... ie Written a Sproc that processes a single row (and maybe does auditing!?) and then called it repeatedly from inside a cursor loops. It always seems like a good idea at the time, but when you suddenly get up to the number of iterations you're talking about, everything grinds to a halt ... and you have to look very seriously at changing the process!?
What you probably want to try and do is get out away from the cursor and try and find (work out!) the SQL statement that deals with ALL the rows you want in one go ... which is likely more complicated than what you have now!
A good general rule is that a single SQL statement that deals with 10000 rows will always be quicker than 10000 SQL statements dealing with a single row each!!
Cheers
Mark
October 12, 2004 at 6:31 am
Mark,
The database is solely used by me. so locking tables I presume is ok because no one else is using it. Other people use different database but they all sit on the same server.
I have tried to avoid using trigger but not sure which approach to use. Have you or any one had a better idea of doing this?
Thanks,
Varoeun
October 12, 2004 at 11:30 pm
Have you thought about deleting these customers using a DTS package. I have found that when things time out I can use a DTS package, which has the ability to delete built in..... I assume that the issue is the volume...
Michael
October 14, 2004 at 6:55 am
I think I found a solution to ensure that before I start a transaction, there is no orphan transaction which can cause all sort of problem. Before you start the transaction code:
if @trancount > 0 rollback.
That should do the trick. I get this trick from the book called "A Guru's guide to SQL Server Stored Procedure" by Ken Henderson.
It is an excellent book, I only read a few chapter so far and found a wealth of information in there already.
Another userful info is that why doesn't rollback tran work??? it is all in the book.
Varoeun.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply