Problem with slowly delete many filtered rows in table.

I use cursor for delete 20 milions rows from one table, but it's very slowly. I want use delete rows in a non-resource consuming manner. I don't need from transaction log, but don't know how turn off it.

Please help me!

10x

Why are you using a cursor for this? Can't you form the DELETE statement into a SET-oriented operation? Cursors are going to be much less efficient than a straight-DELETE DML operation. (FYI, TRUNCATE is the only logging-optimized DELETE operation available in the server.)

Thanks,
--R

|||

The basic process that most people do is something like the following:

declare @.row int

set @.row = 1

while @.row < somevalue

begin

delete from table where primary key between @.row and @.row + 1000

set @.row = @.row + 1000

end

This basically walks the table and allows you to delete rows in chunks which minimizes the impact of the delete operation. If this is SQL Server 2005, I would use partitioning to switch the rows you want to delete out of the table and then truncate the staging table you moved the data to.

|||

I use the cursore because i want remove filtered data from this table. I have relation with another table and result to deleted rows is round 21 milions records from 41 milions. The big table have 9 indexes. Is it a problem to slowly execute? I probe to not use the cursor. Write where clause with operator IN, but this not solve the problem. Now delete indexes, but problem stay. For cursor i use option FORWARD_ONLY, and delete records with

DECLARE abc CURSOR FORWARD_ONLY FOR
SELECT * FROM MyTable INNER JOIN

OtherTable ON(OtherTable .MyTable_ID=MyTable .ID)

OPEN abc

FETCH NEXT FROM abc

DELETE MyTable WHERE CURRENT OF abc

CLOSE abc

DEALLOCATE abc

Can you offer some idea?

|||

Hi, maybe I am wrong but do you this this could work ?

Delete MyTable where exists (select * from OtherTable where OtherTable.MyTable_ID = MyTable.ID)

|||

Thanks knuti,

I solve this problem with part delete of N records with ROWCOUNT and WAITFOR. This is slowly but tables stay unlocked.

Problem with slowly delete many filtered rows in table.

I use cursor for delete 20 milions rows from one table, but it's very slowly. I want use delete rows in a non-resource consuming manner. I don't need from transaction log, but don't know how turn off it.

Please help me!

10x

Why are you using a cursor for this? Can't you form the DELETE statement into a SET-oriented operation? Cursors are going to be much less efficient than a straight-DELETE DML operation. (FYI, TRUNCATE is the only logging-optimized DELETE operation available in the server.)

Thanks,
--R

|||

The basic process that most people do is something like the following:

declare @.row int

set @.row = 1

while @.row < somevalue

begin

delete from table where primary key between @.row and @.row + 1000

set @.row = @.row + 1000

end

This basically walks the table and allows you to delete rows in chunks which minimizes the impact of the delete operation. If this is SQL Server 2005, I would use partitioning to switch the rows you want to delete out of the table and then truncate the staging table you moved the data to.

|||

I use the cursore because i want remove filtered data from this table. I have relation with another table and result to deleted rows is round 21 milions records from 41 milions. The big table have 9 indexes. Is it a problem to slowly execute? I probe to not use the cursor. Write where clause with operator IN, but this not solve the problem. Now delete indexes, but problem stay. For cursor i use option FORWARD_ONLY, and delete records with

DECLARE abc CURSOR FORWARD_ONLY FOR
SELECT * FROM MyTable INNER JOIN

OtherTable ON(OtherTable .MyTable_ID=MyTable .ID)

OPEN abc

FETCH NEXT FROM abc

DELETE MyTable WHERE CURRENT OF abc

CLOSE abc

DEALLOCATE abc

Can you offer some idea?

|||

Hi, maybe I am wrong but do you this this could work ?

Delete MyTable where exists (select * from OtherTable where OtherTable.MyTable_ID = MyTable.ID)

|||

Thanks knuti,

I solve this problem with part delete of N records with ROWCOUNT and WAITFOR. This is slowly but tables stay unlocked.

Problem with simple cursor

I have a cursor here that appears to never stop running even though the
record set that populates it is only 22 records

heres the code:

declare cursorfinal cursor for
select appointmenteffdate, appointmentDuration, provideroid from @.main

open cursorfinal

FETCH NEXT FROM cursorfinal
INTO @.aff, @.duration, @.poid

WHILE @.@.FETCH_STATUS = 0
BEGIN

delete from @.main_temp where (appointmenteffdate between @.aff and
dateadd(minute, @.duration , @.aff) or
dateadd(minute,appointmentduration,appointmenteffd ate ) between @.aff
and dateadd(minute, @.duration , @.aff))
and provideroid = @.poid
select @.poid

END

CLOSE cursorfinal
DEALLOCATE cursorfinalJimbo (jim.ferris@.motorola.com) writes:
> I have a cursor here that appears to never stop running even though the
> record set that populates it is only 22 records
> heres the code:
> declare cursorfinal cursor for
> select appointmenteffdate, appointmentDuration, provideroid from @.main
> open cursorfinal
> FETCH NEXT FROM cursorfinal
> INTO @.aff, @.duration, @.poid
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> delete from @.main_temp where (appointmenteffdate between @.aff and
> dateadd(minute, @.duration , @.aff) or
> dateadd(minute,appointmentduration,appointmenteffd ate ) between @.aff
> and dateadd(minute, @.duration , @.aff))
> and provideroid = @.poid
> select @.poid
> END
> CLOSE cursorfinal
> DEALLOCATE cursorfinal

You need to move the FETCH into the loop:

WHILE 1 = 1
BEGIN
FETCH ...
IF @.@.fetch_status <> 0
BREAK

-- Do stuff
END

However, there is no reason to write a cursor at all here:

delete @.main_temp
from @.main_temp t
where EXISTS
(SELECT *
FROM @.main m
WHERE m.provideroid = t.provideroid
AND (t.appointmenteffdate between
m.appointmenteffdate and
dateadd(minute, m.appointmentDuration,
m.appointmenteffdate)
or dateadd(minute, t.appointmentduration,
t.appointmenteffdate)
between m.appointmenteffdate AND
dateadd(minute, m.appointmentDuration,
m.appointmenteffdate)))

It's essential that you lear to operations like this in a set-based
fashion. Maybe your cursor over 22 rows runs quickly, but what if
real production data has 20000 rows? The difference beween the
cursor and a set-based statement like the above, can easily be a
factor of 1000 in such case.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here's a guess. I'm assuming that (provideroid, appointmenteffdate) is
unique in @.main_temp. If you need more help then please post DDL /
DECLARE statements so that we don't have to guess at keys and
constraints - that's pretty important information for solving data
manipulation problems.

DELETE T
FROM @.main_temp AS T
WHERE EXISTS
(SELECT *
FROM @.main AS M
WHERE M.provideroid = T.provideroid
AND M.appointmenteffdate
< DATEADD(MINUTE,T.appointmentduration,T.appointment effdate)
AND DATEADD(MINUTE,M.appointmentduration,M.appointment effdate)
> T.appointmenteffdate
AND M.appointmenteffdate < T.appointmenteffdate) ;

(untested)

Alternatively, why bother deleting the rows at all from the table
variable? Just ignore the unwanted rows until you discard the variable.

--
David Portas
SQL Server MVP
--|||The reason why I did the cursor is really complicated...I tried it with
the delete before and it wasnt working on certain records...but the
cursor solved it..thanks for all your help and suggestions..got it
working

Problem with Script

Hey all,
Can anyone explain me why this doesnt work:
declare db_defrag cursor for
select [name] from master..sysdatabases where dbid
NOT IN (1,2,3,4,5,6) for read only
declare @.dbname varchar(50)
open db_defrag
fetch next from db_defrag into @.dbname
while @.@.fetch_status = 0
*****declare show_defrag cursor for
exec ('select [name] from ' + @.dbname
+ '..sysobjects where type =''U''') for read only *** how
come i cannot use the variable @.dbname in the second
cursor without getting an error? Any workarround?
Please help,
Thanxs> *****declare show_defrag cursor for
> exec ('select [name] from ' + @.dbname
> + '..sysobjects where type =''U''') for read only *** how
> come i cannot use the variable @.dbname in the second
> cursor without getting an error? Any workarround?
> Please help,
Do the whole lot inside dynamic SQL:
SET @.sql = 'DECLARE show_defrag cursor for SELECT "name" FROM ' + @.dbname +
'..sysobjects WHERE ...'
EXEC(@.sql)
However, you don't have to loop inside the database for DBCC SHOWCONTIG.
Just use DBCC SHOWCONTIG at the database level using the WITH TABLERESULTS
option.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:5b3a01c3ad27$de17fe30$a601280a@.phx.gbl...
> Hey all,
> Can anyone explain me why this doesnt work:
> declare db_defrag cursor for
> select [name] from master..sysdatabases where dbid
> NOT IN (1,2,3,4,5,6) for read only
> declare @.dbname varchar(50)
> open db_defrag
> fetch next from db_defrag into @.dbname
> while @.@.fetch_status = 0
> *****declare show_defrag cursor for
> exec ('select [name] from ' + @.dbname
> + '..sysobjects where type =''U''') for read only *** how
> come i cannot use the variable @.dbname in the second
> cursor without getting an error? Any workarround?
> Please help,
> Thanxs|||It dont seem to work... the process just
stays "pending"... Here is the whole script.. any help
would be greatful:
set nocount on
CREATE TABLE #aux (
[ObjectName] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ObjectId] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IndexName] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IndexId] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Level] [varchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Pages] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Rows] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MinimumRecordSize] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MaximumRecordSize] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AverageRecordSize] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[FowardedRecords] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Extents] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ExtentSwitches] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AverageFreeBytes] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AveragePageDensity] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ScanDensity] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[BestCount] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ActualCount] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LogicalFragmentation] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ExtentFragmentation] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
declare db_defrag cursor for
select [name] from master..sysdatabases where dbid
NOT IN (1,2,3,4,5,6) for read only
declare @.dbname varchar(50)
open db_defrag
fetch next from db_defrag into @.dbname
while @.@.fetch_status = 0
declare @.sql varchar (300)
SET @.sql = 'DECLARE show_defrag cursor for
SELECT [name] FROM ' + @.dbname
+ '..sysobjects WHERE type = ''U'' for read only'
declare @.objectname varchar(200)
declare @.msg varchar(200)
EXEC(@.sql)
--declare show_defrag cursor for
--select [name] from ..sysobjects where type = 'U'
for read only
open show_defrag
fetch next from show_defrag into @.objectname
while @.@.fetch_status = 0
Begin
INSERT INTO #aux
EXEC ('DBCC SHOWCONTIG (' + @.objectname
+ ') WITH TABLERESULTS')
if exists (select Objectname from #aux where
Objectname = @.objectname and
ExtentSwitches > Extents or
ScanDensity < cast (95.0 as Decimal) or
LogicalFragmentation > cast (10.0 as Decimal) or
ExtentFragmentation > cast (10.0 as Decimal))
Begin
set @.msg = 'The table ' + @.objectname + ' in
database' + @.dbname + ' is fragmented'
Print @.msg
--exec master..xp_logevent 51515, @.msg,
informational
End
fetch next from db_defrag into @.dbname
fetch next from show_defrag into @.objectname
End
deallocate db_defrag
deallocate show_defrag
drop table #aux
set nocount off
>--Original Message--
>> *****declare show_defrag cursor for
>> exec ('select [name] from ' + @.dbname
>> + '..sysobjects where type =''U''') for read only ***
how
>> come i cannot use the variable @.dbname in the second
>> cursor without getting an error? Any workarround?
>> Please help,
>Do the whole lot inside dynamic SQL:
>SET @.sql = 'DECLARE show_defrag cursor for SELECT "name"
FROM ' + @.dbname +
>'..sysobjects WHERE ...'
>EXEC(@.sql)
>However, you don't have to loop inside the database for
DBCC SHOWCONTIG.
>Just use DBCC SHOWCONTIG at the database level using the
WITH TABLERESULTS
>option.
>
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>":)" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5b3a01c3ad27$de17fe30$a601280a@.phx.gbl...
>> Hey all,
>> Can anyone explain me why this doesnt work:
>> declare db_defrag cursor for
>> select [name] from master..sysdatabases where dbid
>> NOT IN (1,2,3,4,5,6) for read only
>> declare @.dbname varchar(50)
>> open db_defrag
>> fetch next from db_defrag into @.dbname
>> while @.@.fetch_status = 0
>> *****declare show_defrag cursor for
>> exec ('select [name] from ' + @.dbname
>> + '..sysobjects where type =''U''') for read only ***
how
>> come i cannot use the variable @.dbname in the second
>> cursor without getting an error? Any workarround?
>> Please help,
>> Thanxs
>
>.
>|||One thing I see that that inside your WHILE, you need BEGIN and END:
WHILE ...
BEGIN
...
...
FETCH NEXT FROM ...
END
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:0b9501c3adb6$987ae5b0$a101280a@.phx.gbl...
> It dont seem to work... the process just
> stays "pending"... Here is the whole script.. any help
> would be greatful:
>
>
> set nocount on
> CREATE TABLE #aux (
> [ObjectName] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ObjectId] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [IndexName] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [IndexId] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Level] [varchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Pages] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Rows] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MinimumRecordSize] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MaximumRecordSize] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AverageRecordSize] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [FowardedRecords] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Extents] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ExtentSwitches] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AverageFreeBytes] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AveragePageDensity] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ScanDensity] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [BestCount] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ActualCount] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [LogicalFragmentation] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ExtentFragmentation] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> declare db_defrag cursor for
> select [name] from master..sysdatabases where dbid
> NOT IN (1,2,3,4,5,6) for read only
> declare @.dbname varchar(50)
> open db_defrag
> fetch next from db_defrag into @.dbname
> while @.@.fetch_status = 0
> declare @.sql varchar (300)
> SET @.sql = 'DECLARE show_defrag cursor for
> SELECT [name] FROM ' + @.dbname
> + '..sysobjects WHERE type = ''U'' for read only'
> declare @.objectname varchar(200)
> declare @.msg varchar(200)
> EXEC(@.sql)
>
> --declare show_defrag cursor for
> --select [name] from ..sysobjects where type = 'U'
> for read only
>
> open show_defrag
> fetch next from show_defrag into @.objectname
> while @.@.fetch_status = 0
> Begin
> INSERT INTO #aux
> EXEC ('DBCC SHOWCONTIG (' + @.objectname
> + ') WITH TABLERESULTS')
>
> if exists (select Objectname from #aux where
> Objectname = @.objectname and
> ExtentSwitches > Extents or
> ScanDensity < cast (95.0 as Decimal) or
> LogicalFragmentation > cast (10.0 as Decimal) or
> ExtentFragmentation > cast (10.0 as Decimal))
> Begin
> set @.msg = 'The table ' + @.objectname + ' in
> database' + @.dbname + ' is fragmented'
> Print @.msg
> --exec master..xp_logevent 51515, @.msg,
> informational
> End
> fetch next from db_defrag into @.dbname
> fetch next from show_defrag into @.objectname
> End
> deallocate db_defrag
> deallocate show_defrag
> drop table #aux
>
> set nocount off
>
>
>
> >--Original Message--
> >> *****declare show_defrag cursor for
> >> exec ('select [name] from ' + @.dbname
> >> + '..sysobjects where type =''U''') for read only ***
> how
> >> come i cannot use the variable @.dbname in the second
> >> cursor without getting an error? Any workarround?
> >> Please help,
> >
> >Do the whole lot inside dynamic SQL:
> >
> >SET @.sql = 'DECLARE show_defrag cursor for SELECT "name"
> FROM ' + @.dbname +
> >'..sysobjects WHERE ...'
> >EXEC(@.sql)
> >
> >However, you don't have to loop inside the database for
> DBCC SHOWCONTIG.
> >Just use DBCC SHOWCONTIG at the database level using the
> WITH TABLERESULTS
> >option.
> >
> >
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >":)" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:5b3a01c3ad27$de17fe30$a601280a@.phx.gbl...
> >> Hey all,
> >>
> >> Can anyone explain me why this doesnt work:
> >>
> >> declare db_defrag cursor for
> >> select [name] from master..sysdatabases where dbid
> >> NOT IN (1,2,3,4,5,6) for read only
> >>
> >> declare @.dbname varchar(50)
> >>
> >> open db_defrag
> >> fetch next from db_defrag into @.dbname
> >>
> >> while @.@.fetch_status = 0
> >>
> >> *****declare show_defrag cursor for
> >> exec ('select [name] from ' + @.dbname
> >> + '..sysobjects where type =''U''') for read only ***
> how
> >> come i cannot use the variable @.dbname in the second
> >> cursor without getting an error? Any workarround?
> >> Please help,
> >>
> >> Thanxs
> >
> >
> >.
> >