Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: many DELETE as fast as possible

    hello all !
    for MS SQL 2000
    i am having a table with > 100 000 rows
    I must clean it

    DELETE FROM myTable WHERE Name LIKE 'aser%' AND info IS NULL
    DELETE FROM myTable WHERE Name LIKE 'tuyi%' AND Info = 'ok'
    DELETE FROM myTable WHERE Name LIKE 'hop%' AND info LIKE 'retro%'
    .....

    about 20 DELETE commands

    what is the best way to do it ?


    thank you
    Last edited by anselme; 10-23-06 at 14:15.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    make one DELETE statement
    Code:
    DELETE 
      FROM myTable 
     WHERE ( Name LIKE 'aser%' AND info IS NULL )
        OR ( Name LIKE 'tuyi%' AND Info = 'ok' )
        OR ( Name LIKE 'hop%' AND info LIKE 'retro%' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2005
    Posts
    266
    even with 20 OR OR OR ?

    and a TRIGGER to DELETE it when it is inserted is maybe a solution ? (I cant avoid the INSERT)

    thank you

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a trigger to delete something when you insert it?

    that sure sounds silly

    can you explain why you cannot avoid the insert?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2005
    Posts
    266
    a software is using that database and insert without any filter
    the software itself after inserting dont use a big part of rows

    i must make an intranet with this database , my own tables and a part of that software tables
    but to clean up is a hard job ...

    if the MS SQL server could delete this row my web application will be 4 or 5 time faster

  6. #6
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Is this correct?

    You have misbehaving software that inserts unnecessary rows.
    You would rather prevent the rows from being inserted.
    If you cannot prevent it, then you would like to delete them.
    The purpose of deleting it is to improve performance.

    Just a few notes:

    1. You can set a trigger to avoid the INSERT, or you can set one to ROLLBACK the insert based upon conditions.

    2. A single day of INSERTs probably won't have much impact. You could schedule an overnight job to do the deletes. DELETE is one of the more expensive (performance wise) activities, so if you're worried about performance (and you still cannot prevent the INSERT), then you're better off scheduling this cleanup after hours.

    Deleting some rows from a table of 100,000 rows won't take more than 5 or 10 minutes, very acceptable overnight.


    Rudy (r937): I was wondering about consolidating the delete into a single statement. I've experienced that if huge data-altering statements are run within a single transaction, and if it's done during a lot of other access to the database, it can drag the other users down (I presume because they have to read through the rollback logs to get their data). What's your thoughts on that?
    Last edited by vich; 10-23-06 at 15:25.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by anselme
    if the MS SQL server could delete this row my web application will be 4 or 5 time faster
    Not for nuthin (as my Bronx born boss would say), BUT 100,000 rows is nothing.

    In any case, pust constraints on your database

    Create a new table with only the data you want

    Rename the original table _old

    rename the new table to the original tables name
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Dec 2005
    Posts
    266
    Brett I cant add a contraint on a table used by another application

  9. #9
    Join Date
    Dec 2005
    Posts
    266
    vich your method seems to be the best for my needs

    but how can i Delete some rows from a table of 100,000 rows won't take more than 5 or 10 minutes, very acceptable overnight. ?

    a trigger ? and how ?

    thanks a lot for helping

  10. #10
    Join Date
    Dec 2005
    Posts
    266
    Vich you said I was wondering about consolidating the delete into a single statement.

    you mean

    DELETE
    DELETE
    DELETE

    in sted of

    DELETE
    WHERE
    OR
    OR

    ?

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by anselme
    Brett I cant add a contraint on a table used by another application

    But you can add a trigger?


    I am so confused
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You need to go into some more detail about what this app is and why you can't touch it. Is it sharing data with something else that's critical? Why can't it run along on its merry way and you export the data you need elsewhere? If you don't have access to the app itself, perhaps it's best not to rely on the data it produces as a direct source of data for other internal applications.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    View, glorious view......
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vich
    Rudy (r937): I was wondering about consolidating the delete into a single statement. I've experienced that if huge data-altering statements are run within a single transaction, and if it's done during a lot of other access to the database, it can drag the other users down (I presume because they have to read through the rollback logs to get their data). What's your thoughts on that?
    my thoughts: NULL

    i have no thoughts on that, because i have no idea how a user would read through a rollback log

    as far as i know, you can only read tables

    but this is because i are not a dba
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Yeah, I'm refering to internals and associated performance impact.

    (Warning: straying off topic a bit here)
    Consider this: If you have an accounting database with a ton of debits and credits. You issue a huge query during the day, while 20 other users are actively making payments, creating new invoices, making posts, etc.

    In the end, the debits and credits balance to the red penny. How?

    My understanding is via internal cursors and rollback logs. A cursor here, is an internal timer pointer that's attached to each and every data-altering update. Everything within a "transaction" (ie: Begin/End block, whether implicit or explicit) has the same cursor number. Anyone issuing a query with an earlier cursor number to that update must read through the transaction logs to find the "backdated state" of that row.

    It follows that a huge update, like a 2 hour monster that changes most of the rows, will cause a lot of subsequent queries to go searching the transaction logs. Once the query completes, new queries can just go straight to the tables again.

    So; if you break it into little 5 minute changes (or schedule it for an off-hours time), this can all be avoided.

    If however; someone had some huge query issued prior to your first massive UPDATE, then there's no avoiding culling the transaction logs for the proper data state. Ergo; a performance concept to avoid an excessive number of sumultaneous and massive queries and updates. Or better; schedule massive updates (UPDATE and DELETE commands) for after hours and to be run serially.

    Granted; a (properly indexed) table of 100,000 is hardly cause for concern in any case.

    Now; maybe this is a display of some misinformation or misunderstanding I got some years back. I was reading this Oracle book that gave "executive summaries" on the internals of Oracle that was directed at the system's designer level, not the deeper DBA level. I am also no DBA and would love to be corrected on this.

    In short; I would have thought that separate DELETE statements, while it may execute faster, may have less impact on other users. I'd love to hear a DBA's take on that since I'm standing on shaky ground here.
    Last edited by vich; 10-23-06 at 18:10.

Posting Permissions

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