Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    70

    Unanswered: Deleting huge tables with a cursor, failing on huge tables

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

    http://www.dbforums.com/showthread.php?t=1212381

    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?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2004
    Posts
    70
    "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.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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...
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jan 2004
    Posts
    70
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •