Uri Dimant
3/28/2007 12:45:00 PM
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
DELETE FROM tbl WHERE [Date] < DATEADD(dd, -30, CURRENT_TIMESTAMP)
IF @@ROWCOUNT = 0
BEGIN
BREAK
END
END
SET ROWCOUNT 0
For more details please read BOL
"Stephane" <Stephane@discussions.microsoft.com> wrote in message
news:AD0B9B58-9E11-434A-B01E-44A2C06D56F8@microsoft.com...
> Thanks for your answer.
>
> I already use a "where". In fact, I only delete a single user at the time
> out of approx 300.
>
> And with a row count, I would have to use this in a loop? Any example?
>
> Thanks,
>
> Stephane
>
> "Uri Dimant" wrote:
>
>> Hi
>>
>> Do not delete 5M rows as one batch. It is expensive operation. Instead
>> divide your DELETE into small batches like
>>
>> DELETE FROM table WHERE ......
>> Also take a look at ROWCOUNT command
>>
>>
>>
>>
>> "Stephane" <Stephane@discussions.microsoft.com> wrote in message
>> news:23B3BB6C-22B5-49D5-8791-14B8DF80468B@microsoft.com...
>> > Hi,
>> >
>> > I have a two tables with ~5M rows. They have 5 to 10 foreing keys.
>> > Almost
>> > all of them are indexed. It's a data warehouse so there are only few
>> > updates. There's batch insert sometimes. Select statements are really
>> > fast,
>> > but when I want to delete, it's a real pain in the neck.
>> >
>> > In the query execution plan, it seems that the indexes deletion are
>> > what
>> > causing the query to be slow. There's a lot of index delete, sort and
>> > table
>> > spool.
>> >
>> > It looks like this for each index:
>> >
>> > index delete 3% <- sort 5% <- table spool 0%
>> >
>> > How can I speed up a delete statement on a table with several indexes?
>> >
>> > Thanks for any tip
>> >
>> > Stephane
>>
>>
>>