Deleting huge tables with a cursor, failing on huge tables

JamesAvery22 · #1 (**permalink**) 04-21-06, 11:42

I wrote a bunch of SP with code based on this thread:

While loop in Procedure for a batch delete

The stored procs work fine for the majority of tables. One stored procedure just times out and never deletes any rows. It declares a few cursors, then opens one, deletes all the data, closes it, then moves onto the next cursor. I'm guessing its just timing out when it tries to create the cursors.

The tables have relationships with one another so they are deleted in the proper order.

Doing a bunch of these:

Code:

delete
	from myschema.mytable
		where id_col in
		(select id_col
			from myschema.mytable
			where var = invar
			fetch first 250000 rows only
		);
commit;

works though. Is there any way I should be declaring the cursor to improve performance?

sathyaram_s · #2 (**permalink**) 04-21-06, 12:09

I think you should get be monitoring the various applications that are running when you run this SP, lock snapshot in particular ... That way, you'll know what's really causing the error ..

DELETE CURRENT of CURSOR is a very slow process ... The advantage may be concurrency ... ie , your delete statement you have shown here is very likely to escalate locks to table level and therefore other concurrent applications may not be able to access the table ... So, you'll have to decide based on the circumstances ... BTW, if you are talking about concurrent applications, make sure you do not have a large commitcount.. this may cause applications to fail with log full errors ...

Sathyaram

JamesAvery22 · #3 (**permalink**) 04-21-06, 12:25

"DELETE CURRENT of CURSOR is a very slow process" lol great. In that other thread I asked that question and was told it wasn't much different in terms of performance from that other delete...

Should I have never used cursors in the first place? Nothing else will be trying to access the tables when the deletes happen. Well at least for the batch of deletes that Im talking about here that are failing. The other ones I wrote might have people accessing them.
These tables are only output tables for a specific huge process thats run only at very specific times.

sathyaram_s · #4 (**permalink**) 04-21-06, 12:50

When I read the other post, I can't make out where it is said this way ... Probably I have not been with the flow ..

Well, such difference of opinion is not surprising with performance issues ... Each one's experience is different ..

Always do benchmark testing in your environment to ascertain peformance facts ...

In my experience, DELETE is much faster than the cursored delete .. But the difference in performance is considerably reduced if the table is either a parent or a child table in a ref integirty relationship ..

Quote:

Originally Posted by JamesAvery22

"DELETE CURRENT of CURSOR is a very slow process" lol great. In that other thread I asked that question and was told it wasn't much different in terms of performance from that other delete...

JamesAvery22 · #5 (**permalink**) 04-21-06, 13:06

Quote:

Originally Posted by sathyaram_s

When I read the other post, I can't make out where it is said this way ... Probably I have not been with the flow ..

Well, such difference of opinion is not surprising with performance issues ... Each one's experience is different ..

Always do benchmark testing in your environment to ascertain peformance facts ...

In my experience, DELETE is much faster than the cursored delete .. But the difference in performance is considerably reduced if the table is either a parent or a child table in a ref integirty relationship ..

At the end of the thread I asked

"One of the tables this query will be running on has referential integrity turned on and deleting from it is very slow. It has around 8mil rows and it takes longer to delete it than another table with 30mil. Will this slow it down any more?

As apposed to running this a bunch of times"

So just like you said its much slower

It is being performance tested. Thats where it failed. The rest were fine but done have RI so it makes sense.

Internet Services

Web Development

Digital Marketing

Consumer Tech