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 youmake one DELETE statementDELETE
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%' )|||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|||a trigger to delete something when you insert it?

that sure sounds silly

can you explain why you cannot avoid the insert?|||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|||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?|||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 I cant add a contraint on a table used by another application|||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|||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

?|||Brett I cant add a contraint on a table used by another application

But you can add a trigger?

I am so confused|||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.|||View, glorious view.....|||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 ;)|||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.|||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 this case, i believe it is you, the dba, who should be shot

i, as the user, should not have the ability to submit a query which can adversely affect a real-time system like that

therefore, the dba should prevent this

q.e.d.

just another in the long series of reasons why i are not a dba

:)|||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

?Yes.

The more you post, the more I wonder if you're fully aware of the ramification of these deletes you propose. If you don't have access to add constraints or add an UPDATE trigger, then why are you doing this at all?

As Brett suggests, why not just create a View, and perhaps index?|||This was posed as a question. I am unsure if rollback logs are actually handled in this fashion or if it poses a serious performance consideration.

in this case, i believe it is you, the dba, who should be shot

i, as the user, should not have the ability to submit a query which can adversely affect a real-time system like that
Aren't we demanding! :beer: DBA? I wish!|||thank you
but for the moment except keeping
DELETE
DELETE
DELETE
I dont know what to do , or/and how

I must do an intranet to display datas with filters, i can of course add as many tables as I want but the other application must insert normally the rows, and it can be in one report a few 10 000 rows ... up to 100 000

when that job is finish i can of course remove what i want|||So......

When are you going to read the sticky at the top of the board and post what it asks for so we can actually help you, instead of engaging in a virtual 20 questions game?

DBA? (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=46783&whichpage=1)|||I wonder which has more characters in it:

1) the sticky post that instructs one how get a question answered efficiently
2) all posts put together from Brett reminding noobs to read the sticky.

:)|||I wonder which has more characters in it:

1) the sticky post that instructs one how get a question answered efficiently
2) all posts put together from Brett reminding noobs to read the sticky.

:)

And can you imagine how many I have already stated, and been ignored, if you had more the 100 posts?

I wonder why it is so difficult...it is very frustrating|||Oracle and SQL Server treat Consistency differently. In Oracle, when a transaction runs, all pre-changed blocks are put into a Rollback Segment, or in the Undo Tablespace (depending on version and DBA preference/whim). Any select queries read from the table, and the Undo/Rollback area to get a consistent shot of the data BEFORE the transaction happened.

In SQL Server, the update transaction takes out exclusive locks against the rows/pages/tables that are being updated. These locks prevent anyone from accessing the data while the transaction has not been committed. Anyone who runs a select query during the transaction gets in line and waits for the transaction to complete. This gives users a consistent shot of the data AFTER the transaction has completed.

All of the above happens without the intervention (and usually without the knowledge) of any user or programmer. DBA's like me are too lazy to want to intervene, so we leave that all alone.

As a side note, this tends to be one of the hotbutton issues between Microsoft philosophy and Oracle philosophy, and you will see advocates of either deride the other.|||And can you imagine how many I have already stated, and been ignored, if you had more the 100 posts?

I wonder why it is so difficult...it is very frustrating

I can extrapolate. :)

The reason people don't do it is because they think it's more effort (and in the short run, it is - reading some words is more effort than reading zero words). But in the long run it certainly pays off. but humans aren't programmed to think long term. we are short term beasts.

If you use firefox, you should write an extension that does this:

1. prints "read the sticky on how to get your questions answered efficiently!"
2. hits submit
3. locks the thread until the poster has read the sticky

I know (3) is probably impossible, but if it was, it would quickly have a positive effect!|||i think one of the problems with brett's sticky is that you are asked to add yourself to frappr first|||i think one of the problems with brett's sticky is that you are asked to add yourself to frappr first

No longer...moved to the bottom

So, in your take, us wasting our time, when a little effort on the posters part would go a long way to solving their problem, is too much to ask?

I'll have to remember that|||I can extrapolate. :)
but humans aren't programmed to think long term. we are short term beasts.

And that is just plain nonsense

There are all kinds of people. We've fired most of the latter|||And that is just plain nonsense

There are all kinds of people. We've fired most of the latter

It's not nonsense. You may not *like* it, but it's reality. Certainly there are exceptions, but I think it's true in general that most people don't think long term.

That's why social security exists. :)sql