June 25, 2010 at 8:12 am
I am running hte following query to delete since 7 days its not yet finished, any thoughts ? and the number of records involved are 56734591
set nocount on
SET ROWCOUNT 200
WHILE 1 = 1
BEGIN
delete from dbo.RevenueT where Cdate>='2004-01-01'
IF @@ROWCOUNT = 0
BREAK
END
set rowcount 0
June 25, 2010 at 8:33 am
Tara-1044200 (6/25/2010)
I am running hte following query to delete since 7 days its not yet finished, any thoughts ? and the number of records involved are 56734591set nocount on
SET ROWCOUNT 200
WHILE 1 = 1
BEGIN
delete from dbo.RevenueT where Cdate>='2004-01-01'
IF @@ROWCOUNT = 0
BREAK
END
set rowcount 0
Looks like you are trying to delete 56+ million rows 200 at a time, this may take awhile. May I suggest that you increase the size of your batch? Also, you may want to read this article: Deleting Large Number of Records[/url].
The way you are using ROWCOUNT has been depreciated and may not be supported in future versions of SQL Server.
June 25, 2010 at 8:36 am
Do read Lynn's article - and make sure there's a useful index (leading on cdate).
If you are deleting a substantial proportion of the rows in the table, you might find it easier to bulk copy out the rows to keep, truncate the table, and bulk load the saved rows back in. If you are careful to follow the rules for minimally-logged bulk operations, this could be a very fast alternative.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 25, 2010 at 8:45 am
Tara-1044200 (6/25/2010)
I am running hte following query to delete since 7 days its not yet finished, any thoughts ? and the number of records involved are 56734591set nocount on
SET ROWCOUNT 200
WHILE 1 = 1
BEGIN
delete from dbo.RevenueT where Cdate>='2004-01-01'
IF @@ROWCOUNT = 0
BREAK
END
set rowcount 0
How long it takes to find 200 records in your table based on the given condition (Cdate>='2004-01-01')? Does it takes more than 2 seconds?
If so, why you surprised? To delete 56734591 records in 200 records per batch, it needs to be done 283,672 times!
Check the current count in your table (try it as "select rowcnt from sysindexes where [id]=object_id('tally') and indid in (0,1)", I hope your index/table are not partitioned), is it reduces over time (you can mesure how long it takes)? If it does go down then your query is still working and doing its best 😀
June 25, 2010 at 8:58 am
As asid above how do i do a BULK INSERT from table to table very fast, that sounds like a good approach.
June 25, 2010 at 9:05 am
Paul does bring up an interesting point, how many records are you keeping in the table?
June 25, 2010 at 9:19 am
Tara-1044200 (6/25/2010)
As asid above how do i do a BULK INSERT from table to table very fast, that sounds like a good approach.
SELECT * INTO NewTable FROM OldTable [WHERE ....]
Depending on your server recovery model ("Simple" is the best for this, but not all DBAs will like it enough to allow it in production 😀 ), it can be very fast.
June 25, 2010 at 9:20 am
Lynn Pettis (6/25/2010)
Paul does bring up an interesting point...
It has been known 😀
I'm off to bed (3:20am!), so I'll leave Tara in your capable hands for the bulk load stuff. Have fun 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 25, 2010 at 9:37 am
thank you guys i am going to try bulk insert now.
June 25, 2010 at 12:38 pm
Ok if i am doing a "select into " from a partition table and i think it will not create the same paritioned table and os i have to "insert into select "
June 26, 2010 at 7:07 am
Tara,
How many records will be left in the table after the delete?
Lynn asked that before but I think you missed it.
Run the following query to give the answer:
select count(*) from dbo.RevenueT where Cdate < '20040101';
There is also another very efficient way to delete rows from a partitioned table - if the partitioning is based on Cdate. It would help a lot if you could provide the CREATE TABLE definition for the table, and the definition of the partitioning scheme and function too. It would also be nice to know if the table is referenced by any foreign keys.
Thanks
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 28, 2010 at 1:55 pm
Paul
HEre is the DDL you have asked and there are no reference keys. Please let me know if there is any fastest way to delete records.
CREATE DATABASE [RevBud] ON PRIMARY
( NAME = N'RevBud', FILENAME = N'D:\DATA\RevBud.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
FILEGROUP [2004_01_Bud]
( NAME = N'2004_01_$$', FILENAME = N'D:\DATA\2001_01_Bud.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
FILEGROUP [2004_02_Bud]
( NAME = N'2004_02_$$', FILENAME = N'D:\DATA\2001_02_bud.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
................................................................
FILEGROUP [2018_12_Bud]
( NAME = N'2018_12_Bud', FILENAME = N'D:\DATA\2018_12_Bud.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
LOG ON
( NAME = N'RevBud_log', FILENAME = N'D:\LOG\RevBud_log.ldf' , SIZE = 61115584KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
CREATE PARTITION FUNCTION Part_Fn_Month_Bud (smalldatetime)
ASRANGE RIGHT FOR VALUES (
'2001-01-01','2001-02-01','2001-03-01','2001-04-01', '2001-05-01','2001-06-01',
'2001-07-01', '2001-08-01', '2001-09-01','2001-10-01','2001-11-01', '2001-12-01',
..................................................................
'2018-01-01', '2018-02-01','2018-03-01', '2018-04-01', '2018-05-01', '2018-06-01',
'2018-07-01', '2018-08-01', '2018-09-01','2018-10-01','2018-11-01', '2018-12-01' )
CREATE PARTITION SCHEME Part_Schm_Month_Bud
AS PARTITION Part_Fn_Month_Bud TO (
'2001_01_Bud','2001_02_Bud','2001_03_Bud','2001_04_Bud','2001_05_Bud','2001_06_Bud',
'2001_07_Bud', '2001_08_Bud','2001_09_Bud','2001_10_Bud', '2001_11_Bud', '2001_12_Bud',
......................................................
'2018_01_Bud','2018_02_Bud','2018_03_Bud','2018_04_Bud', '2018_05_Bud','2018_06_Bud',
'2018_07_Bud', '2018_08_Bud','2018_09_Bud','2018_10_Bud', '2018_11_Bud', '2018_12_Bud', 'PRIMARY')
June 29, 2010 at 7:02 am
June 29, 2010 at 10:44 am
Paul
The definition very simple which consists of 53 columns with varchar(16) and 4 datetime fields and the row count is 345039256
June 30, 2010 at 11:39 am
Lynn Pettis (6/25/2010)
The way you are using ROWCOUNT has been depreciated and may not be supported in future versions of SQL Server.
Lynn,
Did you mean to say depreciated? Or did you maybe mean deprecated?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply