deleting rows

I have a table with approx 5 million rows and 36 columns. It takes approx
4 minutes to delete 1 row. The table has 3 indexes in addition to it's primary key and has twelve foreign key constraints. We are still using sequel 7.
There is a backup run every night as part of the nightly maintenence that
reorg/reindexes and checks the database integrity. Any thoughts?

Thankswell I depends what your deleting on. Make sure that you're using an indexed column, and check the execution plan to make sure that it's doing a table seek and not a table scan.

What are you stats for this table set to ?|||I am using a simple delete such as delete from tablename where transsk = 1002
with transsk being the primary key. This has only become a problem once the table grew over a mil rows.|||what kind of index, clustered or non-clustered ?

and did you set a fill factor on the table ?

setting your index properly should bring down your delete to a few seconds.
I've got tables that are 6mil+ rows and a delete takes < 15 secs.|||The primary key is non-clustered with a fill factor of 90.
There are also three indexes. Two non-clustered and one clustered, all three with a fill factor of 90. It's also odd to me that inserting rows is not a problem.|||Inserting a row shouldn't be much of a problem as you don't have to seek to insert a row. If you've got a clustered index, there is a little bit of overhead as the data needs to be arranged logically. IE, it may have to shuffle other rows around to properly fit in the one you are inserting. With a non-clustered index, it can just append the row to the logical group and add an entry into the tree.

What you may want to try for benchmarking purposes is to remove the clustered index and see if you get a performance increase when inserting or deleting. I don't think you'll get much, but it's worth a shot...

have you taken a look at the execution plan for a simple delete like the one you posted ?|||Thanks, I will give that a try by removing the clustered index.
Do you have tables with as many foreign key constraints? I didn't know if 12 was a unusually large amount.

Also, I guess I'm an idiot, what do you mean by execution plan?|||If you open query analyzer, there is a button at the top that will show you the proposed execution plan that SQL server will use when you run that SQL. The execution plan is created based on statistics.

Also, I think 12 FK constraints on one table is *a lot*. You should really only have 1 to 3. That's likely the reason it's taking so long to delete anything, it's got many constraints to check before deleting a row.

Cheers,
-Kilka|||Use this sample and apply your own code and cut and paste what it returns

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col2)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'
GO

SET SHOWPLAN_TEXT ON
GO

DELETE FROM myTable99 WHERE Col1 = 2
GO

SET SHOWPLAN_TEXT OFF
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Deleting records from a table takes a long time

I have a table A that has about 35000 rows. Table B has about 2 million rows
.
Three columns colX,colY and colZ in table B have referential integrity
constraints with the primary key of table A. i.e. fk_1 for colX referencing
pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ
referencing pk of table C ( There are other fks also on tableB and indexes)
When I delete rows from table A the delete takes a very long time sometimes
about 40 minutes for about 10 rows( if I allow the delete sql to run) Is
there a way I can speed up the delete from table A? Or any other things I
should look into?Are your statistics up to date? See UPDATE STATISTICS in BOL.
"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:6EDF8E2A-8AEE-439B-9ACB-A36CE3F7956B@.microsoft.com...
>I have a table A that has about 35000 rows. Table B has about 2 million
>rows.
> Three columns colX,colY and colZ in table B have referential integrity
> constraints with the primary key of table A. i.e. fk_1 for colX
> referencing
> pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ
> referencing pk of table C ( There are other fks also on tableB and
> indexes)
> When I delete rows from table A the delete takes a very long time
> sometimes
> about 40 minutes for about 10 rows( if I allow the delete sql to run) Is
> there a way I can speed up the delete from table A? Or any other things I
> should look into?|||Frank1213 wrote:
> I have a table A that has about 35000 rows. Table B has about 2
> million rows. Three columns colX,colY and colZ in table B have
> referential integrity constraints with the primary key of table A.
> i.e. fk_1 for colX referencing pk of table A, fk_2 for colY
> referencing pk of table B,fk_3 for colZ referencing pk of table C (
> There are other fks also on tableB and indexes) When I delete rows
> from table A the delete takes a very long time sometimes about 40
> minutes for about 10 rows( if I allow the delete sql to run) Is there
> a way I can speed up the delete from table A? Or any other things I
> should look into?
If table A has FK values in table B, then you can't delete from table A
unless you have set up cascading deletes. Have you? The other way to
delete is to manually remove the table B rows that match the PK in table
A, then delete from table A.
It's impossible to really guess where the holdup is in your testing. I
assume you have an index on the FK col1X in tableB, right?
David Gugick
Imceda Software
www.imceda.com|||Do you have an index on the foreign key in table B that references table A?
If not then when you delete from A it will probably be doing table scans of
table B to perform the referential integrity action (validate, cascade
delete/update).
"Frank1213" wrote:

> I have a table A that has about 35000 rows. Table B has about 2 million ro
ws.
> Three columns colX,colY and colZ in table B have referential integrity
> constraints with the primary key of table A. i.e. fk_1 for colX referenci
ng
> pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ
> referencing pk of table C ( There are other fks also on tableB and indexes
)
> When I delete rows from table A the delete takes a very long time sometime
s
> about 40 minutes for about 10 rows( if I allow the delete sql to run) Is
> there a way I can speed up the delete from table A? Or any other things I
> should look into?|||Run the delete with showplan and statistics io to see where your slowdown is
at. Have you done this yet? Post the results, the original query, and the
ddl. Maybe we can help you out with a more educated guess. :)
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O10aVwQKFHA.3552@.TK2MSFTNGP12.phx.gbl...
> Frank1213 wrote:
> If table A has FK values in table B, then you can't delete from table A
> unless you have set up cascading deletes. Have you? The other way to
> delete is to manually remove the table B rows that match the PK in table
> A, then delete from table A.
> It's impossible to really guess where the holdup is in your testing. I
> assume you have an index on the FK col1X in tableB, right?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Like Derrick, I would guess that putting an index on the foreign key in the
child table would help. Can you define "sometimes"? Does it sometimes run
in 40 ms? How busy is the system at the time? Do you have adequate
hardware?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:6EDF8E2A-8AEE-439B-9ACB-A36CE3F7956B@.microsoft.com...
>I have a table A that has about 35000 rows. Table B has about 2 million
>rows.
> Three columns colX,colY and colZ in table B have referential integrity
> constraints with the primary key of table A. i.e. fk_1 for colX
> referencing
> pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ
> referencing pk of table C ( There are other fks also on tableB and
> indexes)
> When I delete rows from table A the delete takes a very long time
> sometimes
> about 40 minutes for about 10 rows( if I allow the delete sql to run) Is
> there a way I can speed up the delete from table A? Or any other things I
> should look into?

Deleting million rows after checking duplication

I have a table(9 fields) which contains around 10 million records. Within these 10 million records, don't know how many are duplicated rows. I wrote a cursor which checks duplication on all of the 9 fields within each row, and then returns back with the count of rows that are duplicated. I subtact 1 row and delete all the other rows. Problem is that this thing takes a lot of time to execute. At the current rate(approx. 90 records/hour) this cursor is going to take months to clean the table.

There is no PK or no indexing what so ever on the table, and I HAVE to check each and every field for duplication(all except 1 fields are nvarchar).

Please help me. I need to sort this out

What about doing a grouping on the table and pulling hte data out to another table.

Something like:

Select cola,ColB,Colc (all columns here)
INTO SomeNewTable
FROM SomeTable
Group by cola,ColB,Colc (all columns here)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Deleting duplicate entries in nightmare-table

Hello
I was given the task to "filter away" duplicate rows in a 10 million row
table with about 30 columns :S And the table has no primary key, no
constrains, nothing, and I need to find a way to clear that mess up, in a
way... sigh
You guys must think, alright, this is easy, just group by, well well, that
would be too easy, since each row isnt really "unique", lets continue the
mess....
Lets say the table contains 5 columns, A B C D E
In the new table there shall be a CHECK(A,B,C), that combination is unique.
But in the old table there are duplicates of that, and the values of D and E
may be any value on each row. (Headache yet?) Ppl who create these kinds of
heaptables should be lined up and shot :(
Lemme post some test DLL:
CREATE TABLE #Test (
A int,
B int,
C int,
D int,
E int
)
INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,9,8)
INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,-1,6)
INSERT INTO #Test(A,B,C,D,E)VALUES(1,2,1,1,4)
INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,1,4)
INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,3,4)
INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,3,4)
SELECT * FROM #Test
/*
DESIRED RESULT
A B C D E
1,1,1,9,8
1,2,1,1,4
3,2,1,1,4
*/
DROP TABLE #Test
And everything is so screwed up it doesnt "matter" which values D and E has
(or the other 20 columns) as long as they had the value it had before. And
all columns are like varchar(x) in the table, so keep that in mind :S> And everything is so screwed up it doesnt "matter" which values D and E
> has
> (or the other 20 columns) as long as they had the value it had before.
This doesn't make sense to me, which value did it have before?
Anyway, I'll try, can you do this:
SELECT A,B,C,MAX(D),MAX(E)
FROM table
GROUP BY A,B,C;
?
There is no ANY() function, so you either need to choose an aggregate, or
maybe you could use a correlated subquery with ORDER BY CHECKSUM(NEWID())
but I'm not clear that would work, nor without better requirements am I
inclined to try.
A|||Lasse Edsvik wrote:
> Hello
> I was given the task to "filter away" duplicate rows in a 10 million row
> table with about 30 columns :S And the table has no primary key, no
> constrains, nothing, and I need to find a way to clear that mess up, in a
> way... sigh
> You guys must think, alright, this is easy, just group by, well well, that
> would be too easy, since each row isnt really "unique", lets continue the
> mess....
> Lets say the table contains 5 columns, A B C D E
> In the new table there shall be a CHECK(A,B,C), that combination is unique
.
> But in the old table there are duplicates of that, and the values of D and
E
> may be any value on each row. (Headache yet?) Ppl who create these kinds o
f
> heaptables should be lined up and shot :(
> Lemme post some test DLL:
>
> CREATE TABLE #Test (
> A int,
> B int,
> C int,
> D int,
> E int
> )
> INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,9,8)
> INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,-1,6)
> INSERT INTO #Test(A,B,C,D,E)VALUES(1,2,1,1,4)
> INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,1,4)
> INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,3,4)
> INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,3,4)
>
> SELECT * FROM #Test
> /*
> DESIRED RESULT
>
> A B C D E
> 1,1,1,9,8
> 1,2,1,1,4
> 3,2,1,1,4
> */
> DROP TABLE #Test
>
> And everything is so screwed up it doesnt "matter" which values D and E ha
s
> (or the other 20 columns) as long as they had the value it had before. And
> all columns are like varchar(x) in the table, so keep that in mind :S
Always tell us what version of SQL Server you are using.
In SQL Server 2005:
WITH T (row_num) AS
(SELECT ROW_NUMBER() OVER
(PARTITION BY a,b,c ORDER BY a,b,c,d,e)
FROM #Test)
DELETE FROM T
WHERE row_num > 1;
Google for "delete duplicates" and you'll find lots of other solutions
in the archives of this group.
Test it out and make sure you have a current backup first :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
sry, forgot that :) I'm using SQL 2000
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1147360308.771189.249980@.i40g2000cwc.googlegroups.com...
> Lasse Edsvik wrote:
a
that
the
unique.
and E
of
has
And
> Always tell us what version of SQL Server you are using.
> In SQL Server 2005:
> WITH T (row_num) AS
> (SELECT ROW_NUMBER() OVER
> (PARTITION BY a,b,c ORDER BY a,b,c,d,e)
> FROM #Test)
> DELETE FROM T
> WHERE row_num > 1;
> Google for "delete duplicates" and you'll find lots of other solutions
> in the archives of this group.
> Test it out and make sure you have a current backup first :-)
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Hi Aaron,
consider raw data like this:
INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,9,6)
INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,-1,8)
then the query
SELECT A,B,C,MAX(D),MAX(E)
FROM table
GROUP BY A,B,C;
will produce a row
1,1,1,9,8
which is not present in the original data. Does it make sence?|||alter table #test add f timestamp
go
select * from #test
go
select A,B,C,D,E from #test where not exists(select 1 from #test t1
where t1.a=#test.a
and t1.b=#test.b
and t1.c=#test.c
and t1.f>#test.f)
A B C D E
-- -- -- -- --
1 1 1 -1 6
1 2 1 1 4
3 2 1 3 4
(3 row(s) affected)|||> which is not present in the original data. Does it make sence?
I don't know, I don't think the requirements were specific enough to make
you right or to make me wrong. I was just offering one possible solution.

Deleting Duplicate Data

Hi to All!
I have a table with 60 columns and more than one million rows. i have to
implement composite primary key but it gives me error of duplicate data.
i have used following query to detect the duplicate rows
select NID,output_No from tbl_Data
group by NID,output_No
having count(*) > 1
it gives me 2526 duplicate dows. Now i wants to delete the duplicate
rows what will be the query for deleting the duplicate records.
Thanx
*** Sent via Developersdex http://www.examnotes.net ***This script has written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
news:umVO5durFHA.3444@.TK2MSFTNGP12.phx.gbl...
>
> Hi to All!
> I have a table with 60 columns and more than one million rows. i have to
> implement composite primary key but it gives me error of duplicate data.
> i have used following query to detect the duplicate rows
> select NID,output_No from tbl_Data
> group by NID,output_No
> having count(*) > 1
> it gives me 2526 duplicate dows. Now i wants to delete the duplicate
> rows what will be the query for deleting the duplicate records.
>
> Thanx
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Try this:
(1) SELECT columnList INTO workTable FROM tableName GROUP BY keyColumns
HAVING COUNT(*) > 1
(2) DELETE tableName FROM workTable WHERE tableName.keyColumns =
workTable.keyColumns
(3) INSERT tableName (columnList) SELECT columnList FROM workTable
(4) DROP workTable
You might want to wrap this in a transaction, but if you don't then a temp
table for the work table is contraindicated because if power goes out
between steps 2 and 3, you will lose the duplicate rows altogether.
If the table were tiny, you could use something like:
SET ROWCOUNT 1
AGAIN:
DELETE tableName FROM (SELECT keyColumns FROM tableName GROUP BY keyColumns
HAVING COUNT(*) > 1) a WHERE tableName.keyColumns = a.keyColumns
IF @.@.ROWCOUNT > 0 GOTO AGAIN
SET ROWCOUNT 0
"Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
news:umVO5durFHA.3444@.TK2MSFTNGP12.phx.gbl...
>
> Hi to All!
> I have a table with 60 columns and more than one million rows. i have to
> implement composite primary key but it gives me error of duplicate data.
> i have used following query to detect the duplicate rows
> select NID,output_No from tbl_Data
> group by NID,output_No
> having count(*) > 1
> it gives me 2526 duplicate dows. Now i wants to delete the duplicate
> rows what will be the query for deleting the duplicate records.
>
> Thanx
>
> *** Sent via Developersdex http://www.examnotes.net ***

Deleting data with least impact

I have a large database - 33 million rows - VERY WIDE table and am
running out of disk space - lovely ...
I have to move 7 million records off the table - no biggie - I'll just
dts the records to another server.
My problem is deleting the remaining data to make room on the server
without impacting/logging too much.
Any help would be appreciated.
Thanks,
CraigOn Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
> I have a large database - 33 million rows - VERY WIDE table and am
> running out of disk space - lovely ...
> I have to move 7 million records off the table - no biggie - I'll just
> dts the records to another server.
> My problem is deleting the remaining data to make room on the server
> without impacting/logging too much.
> Any help would be appreciated.
> Thanks,
> Craig
truncate|||On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
> I have a large database - 33 million rows - VERY WIDE table and am
> running out of disk space - lovely ...
> I have to move 7 million records off the table - no biggie - I'll just
> dts the records to another server.
> My problem is deleting the remaining data to make room on the server
> without impacting/logging too much.
> Any help would be appreciated.
> Thanks,
> Craig
you can truncate the table one the data is moved out.|||On Sep 11, 9:17 pm, SB <othell...@.yahoo.com> wrote:
> On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
> > I have a large database - 33 million rows - VERY WIDE table and am
> > running out of disk space - lovely ...
> > I have to move 7 million records off the table - no biggie - I'll just
> > dts the records to another server.
> > My problem is deleting the remaining data to make room on the server
> > without impacting/logging too much.
> > Any help would be appreciated.
> > Thanks,
> > Craig
> you can truncate the table one the data is moved out.
I will only need to delete 7 m rows of the 33 m rows ... so I need a
good way to do that.
Craig|||On Sep 12, 11:27 am, Craig <csomb...@.gmail.com> wrote:
> On Sep 11, 9:17 pm, SB <othell...@.yahoo.com> wrote:
>
>
> > On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
> > > I have a large database - 33 million rows - VERY WIDE table and am
> > > running out of disk space - lovely ...
> > > I have to move 7 million records off the table - no biggie - I'll just
> > > dts the records to another server.
> > > My problem is deleting the remaining data to make room on the server
> > > without impacting/logging too much.
> > > Any help would be appreciated.
> > > Thanks,
> > > Craig
> > you can truncate the table one the data is moved out.
> I will only need to delete 7 m rows of the 33 m rows ... so I need a
> good way to do that.
> Craig- Hide quoted text -
> - Show quoted text -
I need to look at the delete statement. How long it takes now?|||Do not forget changing your Recovery Model temporarily to "Simple Recovery
Model". And before doing this, backup your Transaction Log, otherwise log
chain will be broken and you will not be able to restore your database to
the point where you changed your recovery model if you encounter a problem.
After your deleting operation, change your Recovery Model back to FULL.
(After changing your recovery model to FULL, back up your transaction log
again to prevent breaking the log chain)
The aim of changing your recovery model is not to log all this 7million
deletion to the transaction log file and blow it up. You change your
recovery model to prevent this. Because in this operation (as you already
lack of free space on your disks) if you keep FULL recovery model, then it
would log all this deletion operation and it probably raise an "out of
space" error and halt the process of deletion or whatever.
Here's a link that you can obtain more info abour Recovery Models from:
http://msdn2.microsoft.com/en-us/library/ms366344.aspx
--
Ekrem Önsoy
"Craig" <csomberg@.gmail.com> wrote in message
news:1189574876.579528.58280@.r29g2000hsg.googlegroups.com...
> On Sep 11, 9:17 pm, SB <othell...@.yahoo.com> wrote:
>> On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
>> > I have a large database - 33 million rows - VERY WIDE table and am
>> > running out of disk space - lovely ...
>> > I have to move 7 million records off the table - no biggie - I'll just
>> > dts the records to another server.
>> > My problem is deleting the remaining data to make room on the server
>> > without impacting/logging too much.
>> > Any help would be appreciated.
>> > Thanks,
>> > Craig
>> you can truncate the table one the data is moved out.
>
> I will only need to delete 7 m rows of the 33 m rows ... so I need a
> good way to do that.
> Craig
>|||On Tue, 11 Sep 2007 22:27:56 -0700, Craig wrote:
>On Sep 11, 9:17 pm, SB <othell...@.yahoo.com> wrote:
>> On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
>> > I have a large database - 33 million rows - VERY WIDE table and am
>> > running out of disk space - lovely ...
>> > I have to move 7 million records off the table - no biggie - I'll just
>> > dts the records to another server.
>> > My problem is deleting the remaining data to make room on the server
>> > without impacting/logging too much.
>> > Any help would be appreciated.
>> > Thanks,
>> > Craig
>> you can truncate the table one the data is moved out.
>
>I will only need to delete 7 m rows of the 33 m rows ... so I need a
>good way to do that.
>Craig
Hi Craig,
A common technique is to split the delete in batches, like this:
DECLARE @.rc int;
SET @.rc = 1;
WHILE @.rc <> 0
BEGIN;
DELETE TOP(100000)
FROM YourTable
WHERE whatever has to be deleted;
SET @.rc = @.@.ROWCOUNT;
END;
In SQL Server 2000, DELETE TOP(..) is not supported - instead, use SET
ROWCOUNT 100000 in the beginning and SET ROWCOUNT 0 at the end of the
script. Also, for all versions, you might need to experiment to find the
ideal batch size.
IIf your recovery model is full, either switch temporarily to simple (as
sugggested by Ekrem), or add a BACKUP LOG command inside the WHILE loop.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||> IIf your recovery model is full, either switch temporarily to simple (as
> sugggested by Ekrem), or add a BACKUP LOG command inside the WHILE loop.
Now, above doesn't jive in a SQL Server forum. I think you meant:
CASE WHEN recovery model is full THEN switch temporarily to simple ...
(Sorry, I couldn't resist. Just playing a bad joke with Hugo, doesn't have anything to do with your
problem, Craig...)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:60tge3pj8h8jk3d3kohjahp2gh6s345595@.4ax.com...
> On Tue, 11 Sep 2007 22:27:56 -0700, Craig wrote:
>>On Sep 11, 9:17 pm, SB <othell...@.yahoo.com> wrote:
>> On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
>> > I have a large database - 33 million rows - VERY WIDE table and am
>> > running out of disk space - lovely ...
>> > I have to move 7 million records off the table - no biggie - I'll just
>> > dts the records to another server.
>> > My problem is deleting the remaining data to make room on the server
>> > without impacting/logging too much.
>> > Any help would be appreciated.
>> > Thanks,
>> > Craig
>> you can truncate the table one the data is moved out.
>>
>>I will only need to delete 7 m rows of the 33 m rows ... so I need a
>>good way to do that.
>>Craig
> Hi Craig,
> A common technique is to split the delete in batches, like this:
> DECLARE @.rc int;
> SET @.rc = 1;
> WHILE @.rc <> 0
> BEGIN;
> DELETE TOP(100000)
> FROM YourTable
> WHERE whatever has to be deleted;
> SET @.rc = @.@.ROWCOUNT;
> END;
> In SQL Server 2000, DELETE TOP(..) is not supported - instead, use SET
> ROWCOUNT 100000 in the beginning and SET ROWCOUNT 0 at the end of the
> script. Also, for all versions, you might need to experiment to find the
> ideal batch size.
> IIf your recovery model is full, either switch temporarily to simple (as
> sugggested by Ekrem), or add a BACKUP LOG command inside the WHILE loop.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Thu, 13 Sep 2007 09:52:56 +0200, Tibor Karaszi wrote:
>> IIf your recovery model is full, either switch temporarily to simple (as
>> sugggested by Ekrem), or add a BACKUP LOG command inside the WHILE loop.
>Now, above doesn't jive in a SQL Server forum. I think you meant:
>CASE WHEN recovery model is full THEN switch temporarily to simple ...
Hey, Tibor,
You are not actually using CASE as a *statement*, now are you? Off you
go to the nearest C++ newsgroup!!
>(Sorry, I couldn't resist. Just playing a bad joke with Hugo, doesn't have anything to do with your
>problem, Craig...)
And neither could I ... :-)
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||> You are not actually using CASE as a *statement*, now are you? Off you
> go to the nearest C++ newsgroup!!
I guess my wishes for a closer integration with ANSI SQL PSM syntax influences me... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:rhije3lgrsc2476uhtfig6ue17iiv3b1s8@.4ax.com...
> On Thu, 13 Sep 2007 09:52:56 +0200, Tibor Karaszi wrote:
>> IIf your recovery model is full, either switch temporarily to simple (as
>> sugggested by Ekrem), or add a BACKUP LOG command inside the WHILE loop.
>>Now, above doesn't jive in a SQL Server forum. I think you meant:
>>CASE WHEN recovery model is full THEN switch temporarily to simple ...
> Hey, Tibor,
> You are not actually using CASE as a *statement*, now are you? Off you
> go to the nearest C++ newsgroup!!
>>(Sorry, I couldn't resist. Just playing a bad joke with Hugo, doesn't have anything to do with
>>your
>>problem, Craig...)
> And neither could I ... :-)
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

DELETING 100 million from a table weekly SQl SERVER 2000

DELETING 100 million from a table wly SQl SERVER 2000
Hi All
We have a table in SQL SERVER 2000 which has about 250 million records
and this will be growing by 100 million every w. At a time the table
should contain just 13 ws of data. when the 14th w data needs to
be loaded the first w's data has to be deleted.
And this deletes 100 million every w, since the delete is taking lot
of transaction log space the job is not successful.
Can you please help with what are the approaches we can take to fix
this problem?
Performance and transaction log are the issues we are facing. We tried
deletion in steps too but that also is taking time. What are the
different ways we can address this quickly.
Please reply at the earliest.
Thanks
HarishHi Harish,
You should look at partitioning, keep a cycle the partitions and simply
CREATE TABLE and DROP TABLE the new partitions, that way you won't have to
do any logging.
Tony
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"harish" <harish.prabhala@.gmail.com> wrote in message
news:1131162393.816615.122850@.f14g2000cwb.googlegroups.com...
> DELETING 100 million from a table wly SQl SERVER 2000
> Hi All
> We have a table in SQL SERVER 2000 which has about 250 million records
> and this will be growing by 100 million every w. At a time the table
> should contain just 13 ws of data. when the 14th w data needs to
> be loaded the first w's data has to be deleted.
> And this deletes 100 million every w, since the delete is taking lot
> of transaction log space the job is not successful.
> Can you please help with what are the approaches we can take to fix
> this problem?
> Performance and transaction log are the issues we are facing. We tried
> deletion in steps too but that also is taking time. What are the
> different ways we can address this quickly.
> Please reply at the earliest.
> Thanks
> Harish
>|||I agree with Tony in that SQL2005 gives you Partitioning which is great for
these type activities. But if you do the deletes in smaller batches you can
backup the log during the operation periodically to keep the tran log from
growing.
SET ROWCOUNT 10000
WHILE 1 = 1
BEGIN
DELETE FROM TABLE WHERE Col = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"harish" <harish.prabhala@.gmail.com> wrote in message
news:1131162393.816615.122850@.f14g2000cwb.googlegroups.com...
> DELETING 100 million from a table wly SQl SERVER 2000
> Hi All
> We have a table in SQL SERVER 2000 which has about 250 million records
> and this will be growing by 100 million every w. At a time the table
> should contain just 13 ws of data. when the 14th w data needs to
> be loaded the first w's data has to be deleted.
> And this deletes 100 million every w, since the delete is taking lot
> of transaction log space the job is not successful.
> Can you please help with what are the approaches we can take to fix
> this problem?
> Performance and transaction log are the issues we are facing. We tried
> deletion in steps too but that also is taking time. What are the
> different ways we can address this quickly.
> Please reply at the earliest.
> Thanks
> Harish
>|||Hey
Thanks. We have an index on four columns in this table. For Ex A, B, C
and D
The delete statement's where clause has the conditions for A, B and C
The delete statement's where clause has the conditions for just A
Which of the two's performance will be faster?
We tried something like this:
SET ROWCOUNT 5000;
WHILE 1 = 1
BEGIN
DELETE FROM T1 WHERE dt < '20030101' -- original delete
IF @.@.rowcount < 5000 BREAK;
END
SET ROWCOUNT 0;
1) Does this setting ROWCOUNT first sort the table and then delete?
2) The above query is executed to delete all records satisfying the
condition in steps of 5000 until the delete is comple.
How can I stop it after one 5000?|||On 5 Nov 2005 11:04:29 -0800, harish wrote:
(snip)
Hi Harish,
I just replied to the same question in another thread.
Could you please ask your questions in JUST ONE place, and in JUST ONE
group? I've seen your messages scattered over several groups, and
several different messages in just this group. Many of them have
attracted replies. It's very hard to keep track of what is going on in
all thesse threads, and it's a waste of other people's time if someone
posts a reply to you that you already had received in another group.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||harish (harish.prabhala@.gmail.com) writes:
> Thanks. We have an index on four columns in this table. For Ex A, B, C
> and D
> The delete statement's where clause has the conditions for A, B and C
> The delete statement's where clause has the conditions for just A
>
> Which of the two's performance will be faster?
The one that uses the clustered index on the table. In fact, given
the number of rows you are to delete, it is essential that you use
the clustered index in your WHERE condition. Else you will lose on
all the time it takes to locate the rows.

> We tried something like this:
>
> SET ROWCOUNT 5000;
> WHILE 1 = 1
> BEGIN
> DELETE FROM T1 WHERE dt < '20030101' -- original delete
> IF @.@.rowcount < 5000 BREAK;
> END
> SET ROWCOUNT 0;
>
> 1) Does this setting ROWCOUNT first sort the table and then delete?
It will locate the rows by some means. If there is no good index,
this may lead to a scan of the table. But there should not be any
sorting, as there is no reason to sort the table.
By the way, 5000 rows at a time, is a far too low batch-size; 50000
is a minimum. Since you need to delete 100 million, I would even
try a million at a time.
Keep in mind that if you are running with full recovery, you still
need to backup the transaction log. Simple recovery may be a good
thing here.

> 2) The above query is executed to delete all records satisfying the
> condition in steps of 5000 until the delete is comple.
> How can I stop it after one 5000?
The red button in Query Analyzer?
I still think you should consider partitioned views with one view per
month, or one per ten days in a month. In this case deleting the work
for ten days is as easy:
1) Create a new table for the next period.
2) Alter the view to include the new table, and not include the
table with the data to go.
3) Drop the table and lose 130 millions rows instantly.
Check out partitioned views in Books Online.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

DELETING 100 million from a table weekly SQl SERVER 2000

DELETING 100 million from a table weekly SQl SERVER 2000
Hi All
We have a table in SQL SERVER 2000 which has about 250 million records
and this will be growing by 100 million every week. At a time the table
should contain just 13 weeks of data. when the 14th week data needs to
be loaded the first week's data has to be deleted.
And this deletes 100 million every week, since the delete is taking lot
of transaction log space the job is not successful.
Can you please help with what are the approaches we can take to fix
this problem?
Performance and transaction log are the issues we are facing. We tried
deletion in steps too but that also is taking time. What are the
different ways we can address this quickly.
Please reply at the earliest.
Thanks
Harish
You could try inserting the clean records i.e. Current 13 weeks data into a
a temporary table.
Then truncate the table with all data in (bear in mind any identity
columns).
Reinsert the clean records into the table.
Again, bear in mind any idientity columns if you need to keep the ids in
sync as truncate will reset the seed count, also, you can't truncate a table
with a FK so you'd need to work around that too.
Not the best method but may be quicker than your current process.
Immy
"harish" <harish.prabhala@.gmail.com> wrote in message
news:1131163435.343059.179520@.f14g2000cwb.googlegr oups.com...
> DELETING 100 million from a table weekly SQl SERVER 2000
> Hi All
> We have a table in SQL SERVER 2000 which has about 250 million records
> and this will be growing by 100 million every week. At a time the table
> should contain just 13 weeks of data. when the 14th week data needs to
> be loaded the first week's data has to be deleted.
> And this deletes 100 million every week, since the delete is taking lot
> of transaction log space the job is not successful.
> Can you please help with what are the approaches we can take to fix
> this problem?
> Performance and transaction log are the issues we are facing. We tried
> deletion in steps too but that also is taking time. What are the
> different ways we can address this quickly.
> Please reply at the earliest.
> Thanks
> Harish
>
|||> Performance and transaction log are the issues we are facing. We tried
> deletion in steps too but that also is taking time. What are the
> different ways we can address this quickly.
Deleting in smaller separate transaction batches will reduce transaction log
space requirements and improve performance You'll need to experiment to
determine the best batch size for your environment. In my experience, the
optimum size is somewhere around the amount of SQL Server memory. For
example, with 2GB RAM and a 100 byte row size, delete no more than 20M rows
at a time. If you are using the FULL are BULK_LOGGED recovery model, you'll
also need to backup the log between batches to keep the log size manageable.
Alternatively, you can use change to the SIMPLE model for the duration of
the delete script and change back to your normal recovery model afterward.
Don't forget to perform a full database backup following the change from
SIMPLE recovery.
Use the TABLOCKX hint if possible. Ideally, the table's clustered index
should be the column(s) used for your delete criteria.
Another option is to partition data based on your delete criteria (separate
table for each week). This will allow you to simply drop the table
containing the oldest data. The partitioning implementation can be made
transparent to applications by using a UNION ALL view. After dropping the
oldest table, you create a new table for the latest data and change the view
accordingly. If you can adhere to the rules for local partitioned views as
described in the Books Online, there are performance advantages with the
partitioned view and the view is updatable as well. See the example below.
CREATE TABLE OrderDetails_20051030
(
OrderID int NOT NULL,
ProductID int NOT NULL,
Quantity int NOT NULL,
UnitPrice decimal(9, 2) NOT NULL,
OrderDate smalldatetime NOT NULL
CONSTRAINT CK_OrderDetails_20051030
CHECK (OrderDate > '20051023' AND OrderDate <= '20051030')
)
ALTER TABLE OrderDetails_20051030
ADD CONSTRAINT PK_OrderDetails_20051030
PRIMARY KEY(OrderDate, OrderID, ProductID)
GO
CREATE TABLE OrderDetails_20051106
(
OrderID int NOT NULL,
ProductID int NOT NULL,
Quantity int NOT NULL,
UnitPrice decimal(9, 2) NOT NULL,
OrderDate smalldatetime NOT NULL
CONSTRAINT CK_OrderDetails_20051106
CHECK (OrderDate > '20051030' AND OrderDate <= '20051106')
)
ALTER TABLE OrderDetails_20051106
ADD CONSTRAINT PK_OrderDetailsOrderDetails_20051106
PRIMARY KEY(OrderDate, OrderID, ProductID)
GO
CREATE TABLE OrderDetails_20051113
(
OrderID int NOT NULL,
ProductID int NOT NULL,
Quantity int NOT NULL,
UnitPrice decimal(9, 2) NOT NULL,
OrderDate smalldatetime NOT NULL
CONSTRAINT CK_OrderDetails_20051113_OrderDate
CHECK (OrderDate > '20051106' AND OrderDate <= '20051113')
)
ALTER TABLE OrderDetails_20051113
ADD CONSTRAINT PK_OrderDetails_20051113
PRIMARY KEY(OrderDate, OrderID, ProductID)
GO
CREATE VIEW OrderDetails AS
SELECT
OrderID,
ProductID,
Quantity,
UnitPrice,
OrderDate
FROM OrderDetails_20051030
UNION ALL
SELECT
OrderID,
ProductID,
Quantity,
UnitPrice,
OrderDate
FROM OrderDetails_20051106
UNION ALL
SELECT
OrderID,
ProductID,
Quantity,
UnitPrice,
OrderDate
FROM OrderDetails_20051113
GO
INSERT INTO OrderDetails VALUES(1, 1, 1, 1, '20051030')
INSERT INTO OrderDetails VALUES(2, 1, 1, 1, '20051106')
INSERT INTO OrderDetails VALUES(3, 1, 1, 1, '20051113')
GO
--execution plan shows only OrderDetails_20051106 is accessed because
--all PK columns are referenced in this query
SELECT
a.OrderID,
a.ProductID,
a.Quantity,
a.UnitPrice,
a.OrderDate
FROM OrderDetails a
WHERE a.OrderDate = '20051106' AND OrderID = 2 AND ProductID = 1
GO
--to remove oldest week:
DROP VIEW OrderDetails
GO
--drop oldest table
DROP TABLE OrderDetails_20051030
GO
--create table for new data
CREATE TABLE OrderDetails_20051120
(
OrderID int NOT NULL,
ProductID int NOT NULL,
Quantity int NOT NULL,
UnitPrice decimal(9, 2) NOT NULL,
OrderDate smalldatetime NOT NULL
CONSTRAINT CK_OrderDetails_20051120_OrderDate
CHECK (OrderDate > '20051113' AND OrderDate <= '20051120')
)
ALTER TABLE OrderDetails_20051120
ADD CONSTRAINT PK_OrderDetails_20051120
PRIMARY KEY(OrderDate, OrderID, ProductID)
GO
--create view with current tables
CREATE VIEW OrderDetails AS
SELECT
OrderID,
ProductID,
Quantity,
UnitPrice,
OrderDate
FROM OrderDetails_20051106
UNION ALL
SELECT
OrderID,
ProductID,
Quantity,
UnitPrice,
OrderDate
FROM OrderDetails_20051113
UNION ALL
SELECT
OrderID,
ProductID,
Quantity,
UnitPrice,
OrderDate
FROM OrderDetails_20051120
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"harish" <harish.prabhala@.gmail.com> wrote in message
news:1131163435.343059.179520@.f14g2000cwb.googlegr oups.com...
> DELETING 100 million from a table weekly SQl SERVER 2000
> Hi All
> We have a table in SQL SERVER 2000 which has about 250 million records
> and this will be growing by 100 million every week. At a time the table
> should contain just 13 weeks of data. when the 14th week data needs to
> be loaded the first week's data has to be deleted.
> And this deletes 100 million every week, since the delete is taking lot
> of transaction log space the job is not successful.
> Can you please help with what are the approaches we can take to fix
> this problem?
> Performance and transaction log are the issues we are facing. We tried
> deletion in steps too but that also is taking time. What are the
> different ways we can address this quickly.
> Please reply at the earliest.
> Thanks
> Harish
>
|||I forgot to mention that SQL 2005 Enterprise introduces new table and index
partitioning features that make it easier to partition large tables.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23$vpN7h4FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Deleting in smaller separate transaction batches will reduce transaction
> log space requirements and improve performance You'll need to experiment
> to determine the best batch size for your environment. In my experience,
> the optimum size is somewhere around the amount of SQL Server memory. For
> example, with 2GB RAM and a 100 byte row size, delete no more than 20M
> rows at a time. If you are using the FULL are BULK_LOGGED recovery model,
> you'll also need to backup the log between batches to keep the log size
> manageable. Alternatively, you can use change to the SIMPLE model for the
> duration of the delete script and change back to your normal recovery
> model afterward. Don't forget to perform a full database backup following
> the change from SIMPLE recovery.
> Use the TABLOCKX hint if possible. Ideally, the table's clustered index
> should be the column(s) used for your delete criteria.
> Another option is to partition data based on your delete criteria
> (separate table for each week). This will allow you to simply drop the
> table containing the oldest data. The partitioning implementation can be
> made transparent to applications by using a UNION ALL view. After
> dropping the oldest table, you create a new table for the latest data and
> change the view accordingly. If you can adhere to the rules for local
> partitioned views as described in the Books Online, there are performance
> advantages with the partitioned view and the view is updatable as well.
> See the example below.
> CREATE TABLE OrderDetails_20051030
> (
> OrderID int NOT NULL,
> ProductID int NOT NULL,
> Quantity int NOT NULL,
> UnitPrice decimal(9, 2) NOT NULL,
> OrderDate smalldatetime NOT NULL
> CONSTRAINT CK_OrderDetails_20051030
> CHECK (OrderDate > '20051023' AND OrderDate <= '20051030')
> )
> ALTER TABLE OrderDetails_20051030
> ADD CONSTRAINT PK_OrderDetails_20051030
> PRIMARY KEY(OrderDate, OrderID, ProductID)
> GO
> CREATE TABLE OrderDetails_20051106
> (
> OrderID int NOT NULL,
> ProductID int NOT NULL,
> Quantity int NOT NULL,
> UnitPrice decimal(9, 2) NOT NULL,
> OrderDate smalldatetime NOT NULL
> CONSTRAINT CK_OrderDetails_20051106
> CHECK (OrderDate > '20051030' AND OrderDate <= '20051106')
> )
> ALTER TABLE OrderDetails_20051106
> ADD CONSTRAINT PK_OrderDetailsOrderDetails_20051106
> PRIMARY KEY(OrderDate, OrderID, ProductID)
> GO
> CREATE TABLE OrderDetails_20051113
> (
> OrderID int NOT NULL,
> ProductID int NOT NULL,
> Quantity int NOT NULL,
> UnitPrice decimal(9, 2) NOT NULL,
> OrderDate smalldatetime NOT NULL
> CONSTRAINT CK_OrderDetails_20051113_OrderDate
> CHECK (OrderDate > '20051106' AND OrderDate <= '20051113')
> )
> ALTER TABLE OrderDetails_20051113
> ADD CONSTRAINT PK_OrderDetails_20051113
> PRIMARY KEY(OrderDate, OrderID, ProductID)
> GO
> CREATE VIEW OrderDetails AS
> SELECT
> OrderID,
> ProductID,
> Quantity,
> UnitPrice,
> OrderDate
> FROM OrderDetails_20051030
> UNION ALL
> SELECT
> OrderID,
> ProductID,
> Quantity,
> UnitPrice,
> OrderDate
> FROM OrderDetails_20051106
> UNION ALL
> SELECT
> OrderID,
> ProductID,
> Quantity,
> UnitPrice,
> OrderDate
> FROM OrderDetails_20051113
> GO
> INSERT INTO OrderDetails VALUES(1, 1, 1, 1, '20051030')
> INSERT INTO OrderDetails VALUES(2, 1, 1, 1, '20051106')
> INSERT INTO OrderDetails VALUES(3, 1, 1, 1, '20051113')
> GO
> --execution plan shows only OrderDetails_20051106 is accessed because
> --all PK columns are referenced in this query
> SELECT
> a.OrderID,
> a.ProductID,
> a.Quantity,
> a.UnitPrice,
> a.OrderDate
> FROM OrderDetails a
> WHERE a.OrderDate = '20051106' AND OrderID = 2 AND ProductID = 1
> GO
> --to remove oldest week:
> DROP VIEW OrderDetails
> GO
> --drop oldest table
> DROP TABLE OrderDetails_20051030
> GO
> --create table for new data
> CREATE TABLE OrderDetails_20051120
> (
> OrderID int NOT NULL,
> ProductID int NOT NULL,
> Quantity int NOT NULL,
> UnitPrice decimal(9, 2) NOT NULL,
> OrderDate smalldatetime NOT NULL
> CONSTRAINT CK_OrderDetails_20051120_OrderDate
> CHECK (OrderDate > '20051113' AND OrderDate <= '20051120')
> )
> ALTER TABLE OrderDetails_20051120
> ADD CONSTRAINT PK_OrderDetails_20051120
> PRIMARY KEY(OrderDate, OrderID, ProductID)
> GO
> --create view with current tables
> CREATE VIEW OrderDetails AS
> SELECT
> OrderID,
> ProductID,
> Quantity,
> UnitPrice,
> OrderDate
> FROM OrderDetails_20051106
> UNION ALL
> SELECT
> OrderID,
> ProductID,
> Quantity,
> UnitPrice,
> OrderDate
> FROM OrderDetails_20051113
> UNION ALL
> SELECT
> OrderID,
> ProductID,
> Quantity,
> UnitPrice,
> OrderDate
> FROM OrderDetails_20051120
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "harish" <harish.prabhala@.gmail.com> wrote in message
> news:1131163435.343059.179520@.f14g2000cwb.googlegr oups.com...
>
|||Hey
Thanks. We have an index on four columns in this table. For Ex A, B, C
and D
The delete statement's where clause has the conditions for A, B and C
The delete statement's where clause has the conditions for just A
Which of the two's performance will be faster?
We tried something like this:
SET ROWCOUNT 5000;
WHILE 1 = 1
BEGIN
DELETE FROM T1 WHERE dt < '20030101' -- original delete
IF @.@.rowcount < 5000 BREAK;
END
SET ROWCOUNT 0;
1) Does this setting ROWCOUNT first sort the table and then delete?
2) The above query is executed to delete all records satisfying the
condition in steps of 5000 until the delete is comple.
How can I stop it after one 5000?
|||On 5 Nov 2005 11:01:30 -0800, harish wrote:
(snip)
Hi Harish,
I just replied to the same question in another thread.
Could you please ask your questions in JUST ONE place, and in JUST ONE
group? I've seen your messages scattered over several groups, and
several different messages in just this group. Many of them have
attracted replies. It's very hard to keep track of what is going on in
all thesse threads, and it's a waste of other people's time if someone
posts a reply to you that you already had received in another group.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi
Sorry for that. I was new to the group and not sure which group is
active.
Thanks a lot for the information
Regards
Harish

DELETING 100 million from a table weekly SQl SERVER 2000

DELETING 100 million from a table weekly SQl SERVER 2000

Hi All

We have a table in SQL SERVER 2000 which has about 250 million records
and this will be growing by 100 million every week. At a time the table
should contain just 13 weeks of data. when the 14th week data needs to
be loaded the first week's data has to be deleted.

And this deletes 100 million every week, since the delete is taking lot
of transaction log space the job is not successful.

Can you please help with what are the approaches we can take to fix
this problem?

Performance and transaction log are the issues we are facing. We tried
deletion in steps too but that also is taking time. What are the
different ways we can address this quickly.

Please reply at the earliest.

Thanks
HarishHi Harish,

You should look at partitioning, keep a cycle the partitions and simply
CREATE TABLE and DROP TABLE the new partitions, that way you won't have to
do any logging.

Tony

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"harish" <harish.prabhala@.gmail.com> wrote in message
news:1131162982.103343.268120@.o13g2000cwo.googlegr oups.com...
> DELETING 100 million from a table weekly SQl SERVER 2000
> Hi All
> We have a table in SQL SERVER 2000 which has about 250 million records
> and this will be growing by 100 million every week. At a time the table
> should contain just 13 weeks of data. when the 14th week data needs to
> be loaded the first week's data has to be deleted.
> And this deletes 100 million every week, since the delete is taking lot
> of transaction log space the job is not successful.
> Can you please help with what are the approaches we can take to fix
> this problem?
> Performance and transaction log are the issues we are facing. We tried
> deletion in steps too but that also is taking time. What are the
> different ways we can address this quickly.
> Please reply at the earliest.
> Thanks
> Harish|||Am 4 Nov 2005 19:56:22 -0800 schrieb harish:

> DELETING 100 million from a table weekly SQl SERVER 2000
> Hi All
> We have a table in SQL SERVER 2000 which has about 250 million records
> and this will be growing by 100 million every week. At a time the table
> should contain just 13 weeks of data. when the 14th week data needs to
> be loaded the first week's data has to be deleted.
> And this deletes 100 million every week, since the delete is taking lot
> of transaction log space the job is not successful.
> Can you please help with what are the approaches we can take to fix
> this problem?
> Performance and transaction log are the issues we are facing. We tried
> deletion in steps too but that also is taking time. What are the
> different ways we can address this quickly.
> Please reply at the earliest.
> Thanks
> Harish

In this special case i would think about using a table per week. There is
no faster way then DROP/CREATE or maybe TRUNCATE. You have to change a lot
in the way you work with this data, but you have UNION and maybe you can
use VIEWS.
Or you use a big Solid State Disk for your database :-))

bye,
Helmut|||helmut woess (hw@.iis.at) writes:
> In this special case i would think about using a table per week. There is
> no faster way then DROP/CREATE or maybe TRUNCATE. You have to change a lot
> in the way you work with this data, but you have UNION and maybe you can
> use VIEWS.
> Or you use a big Solid State Disk for your database :-))

Since one table per week becomes quite a job to manage, I would go for
one table per month, and then truncate once per month.

If this would be too much data, I would then try every tenth day. This
makes it a lot easier to set up the check constraints for the partitions.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97059EAF78709Yazorman@.127.0.0.1...
> helmut woess (hw@.iis.at) writes:
> > In this special case i would think about using a table per week. There
is
> > no faster way then DROP/CREATE or maybe TRUNCATE. You have to change a
lot
> > in the way you work with this data, but you have UNION and maybe you can
> > use VIEWS.
> > Or you use a big Solid State Disk for your database :-))
> Since one table per week becomes quite a job to manage, I would go for
> one table per month, and then truncate once per month.
> If this would be too much data, I would then try every tenth day. This
> makes it a lot easier to set up the check constraints for the partitions.

Another way to handle this which is SQL Server specific is to set a rowcount
of say 10,000 and loop through deleting 10,000 rows at a time.

And either back up the log frequently enough or use a simple recovery
method.

>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

DELETING 100 million from a table weekly SQl SERVER 2000

DELETING 100 million from a table weekly SQl SERVER 2000
Hi All
We have a table in SQL SERVER 2000 which has about 250 million records
and this will be growing by 100 million every week. At a time the table
should contain just 13 weeks of data. when the 14th week data needs to
be loaded the first week's data has to be deleted.
And this deletes 100 million every week, since the delete is taking lot
of transaction log space the job is not successful.
Can you please help with what are the approaches we can take to fix
this problem?
Performance and transaction log are the issues we are facing. We tried
deletion in steps too but that also is taking time. What are the
different ways we can address this quickly.
Please reply at the earliest.
Thanks
HarishYou could try inserting the clean records i.e. Current 13 weeks data into a
a temporary table.
Then truncate the table with all data in (bear in mind any identity
columns).
Reinsert the clean records into the table.
Again, bear in mind any idientity columns if you need to keep the ids in
sync as truncate will reset the seed count, also, you can't truncate a table
with a FK so you'd need to work around that too.
Not the best method but may be quicker than your current process.
Immy
"harish" <harish.prabhala@.gmail.com> wrote in message
news:1131163435.343059.179520@.f14g2000cwb.googlegroups.com...
> DELETING 100 million from a table weekly SQl SERVER 2000
> Hi All
> We have a table in SQL SERVER 2000 which has about 250 million records
> and this will be growing by 100 million every week. At a time the table
> should contain just 13 weeks of data. when the 14th week data needs to
> be loaded the first week's data has to be deleted.
> And this deletes 100 million every week, since the delete is taking lot
> of transaction log space the job is not successful.
> Can you please help with what are the approaches we can take to fix
> this problem?
> Performance and transaction log are the issues we are facing. We tried
> deletion in steps too but that also is taking time. What are the
> different ways we can address this quickly.
> Please reply at the earliest.
> Thanks
> Harish
>|||> Performance and transaction log are the issues we are facing. We tried
> deletion in steps too but that also is taking time. What are the
> different ways we can address this quickly.
Deleting in smaller separate transaction batches will reduce transaction log
space requirements and improve performance You'll need to experiment to
determine the best batch size for your environment. In my experience, the
optimum size is somewhere around the amount of SQL Server memory. For
example, with 2GB RAM and a 100 byte row size, delete no more than 20M rows
at a time. If you are using the FULL are BULK_LOGGED recovery model, you'll
also need to backup the log between batches to keep the log size manageable.
Alternatively, you can use change to the SIMPLE model for the duration of
the delete script and change back to your normal recovery model afterward.
Don't forget to perform a full database backup following the change from
SIMPLE recovery.
Use the TABLOCKX hint if possible. Ideally, the table's clustered index
should be the column(s) used for your delete criteria.
Another option is to partition data based on your delete criteria (separate
table for each week). This will allow you to simply drop the table
containing the oldest data. The partitioning implementation can be made
transparent to applications by using a UNION ALL view. After dropping the
oldest table, you create a new table for the latest data and change the view
accordingly. If you can adhere to the rules for local partitioned views as
described in the Books Online, there are performance advantages with the
partitioned view and the view is updatable as well. See the example below.
CREATE TABLE OrderDetails_20051030
(
OrderID int NOT NULL,
ProductID int NOT NULL,
Quantity int NOT NULL,
UnitPrice decimal(9, 2) NOT NULL,
OrderDate smalldatetime NOT NULL
CONSTRAINT CK_OrderDetails_20051030
CHECK (OrderDate > '20051023' AND OrderDate <= '20051030')
)
ALTER TABLE OrderDetails_20051030
ADD CONSTRAINT PK_OrderDetails_20051030
PRIMARY KEY(OrderDate, OrderID, ProductID)
GO
CREATE TABLE OrderDetails_20051106
(
OrderID int NOT NULL,
ProductID int NOT NULL,
Quantity int NOT NULL,
UnitPrice decimal(9, 2) NOT NULL,
OrderDate smalldatetime NOT NULL
CONSTRAINT CK_OrderDetails_20051106
CHECK (OrderDate > '20051030' AND OrderDate <= '20051106')
)
ALTER TABLE OrderDetails_20051106
ADD CONSTRAINT PK_OrderDetailsOrderDetails_20051106
PRIMARY KEY(OrderDate, OrderID, ProductID)
GO
CREATE TABLE OrderDetails_20051113
(
OrderID int NOT NULL,
ProductID int NOT NULL,
Quantity int NOT NULL,
UnitPrice decimal(9, 2) NOT NULL,
OrderDate smalldatetime NOT NULL
CONSTRAINT CK_OrderDetails_20051113_OrderDate
CHECK (OrderDate > '20051106' AND OrderDate <= '20051113')
)
ALTER TABLE OrderDetails_20051113
ADD CONSTRAINT PK_OrderDetails_20051113
PRIMARY KEY(OrderDate, OrderID, ProductID)
GO
CREATE VIEW OrderDetails AS
SELECT
OrderID,
ProductID,
Quantity,
UnitPrice,
OrderDate
FROM OrderDetails_20051030
UNION ALL
SELECT
OrderID,
ProductID,
Quantity,
UnitPrice,
OrderDate
FROM OrderDetails_20051106
UNION ALL
SELECT
OrderID,
ProductID,
Quantity,
UnitPrice,
OrderDate
FROM OrderDetails_20051113
GO
INSERT INTO OrderDetails VALUES(1, 1, 1, 1, '20051030')
INSERT INTO OrderDetails VALUES(2, 1, 1, 1, '20051106')
INSERT INTO OrderDetails VALUES(3, 1, 1, 1, '20051113')
GO
--execution plan shows only OrderDetails_20051106 is accessed because
--all PK columns are referenced in this query
SELECT
a.OrderID,
a.ProductID,
a.Quantity,
a.UnitPrice,
a.OrderDate
FROM OrderDetails a
WHERE a.OrderDate = '20051106' AND OrderID = 2 AND ProductID = 1
GO
--to remove oldest week:
DROP VIEW OrderDetails
GO
--drop oldest table
DROP TABLE OrderDetails_20051030
GO
--create table for new data
CREATE TABLE OrderDetails_20051120
(
OrderID int NOT NULL,
ProductID int NOT NULL,
Quantity int NOT NULL,
UnitPrice decimal(9, 2) NOT NULL,
OrderDate smalldatetime NOT NULL
CONSTRAINT CK_OrderDetails_20051120_OrderDate
CHECK (OrderDate > '20051113' AND OrderDate <= '20051120')
)
ALTER TABLE OrderDetails_20051120
ADD CONSTRAINT PK_OrderDetails_20051120
PRIMARY KEY(OrderDate, OrderID, ProductID)
GO
--create view with current tables
CREATE VIEW OrderDetails AS
SELECT
OrderID,
ProductID,
Quantity,
UnitPrice,
OrderDate
FROM OrderDetails_20051106
UNION ALL
SELECT
OrderID,
ProductID,
Quantity,
UnitPrice,
OrderDate
FROM OrderDetails_20051113
UNION ALL
SELECT
OrderID,
ProductID,
Quantity,
UnitPrice,
OrderDate
FROM OrderDetails_20051120
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"harish" <harish.prabhala@.gmail.com> wrote in message
news:1131163435.343059.179520@.f14g2000cwb.googlegroups.com...
> DELETING 100 million from a table weekly SQl SERVER 2000
> Hi All
> We have a table in SQL SERVER 2000 which has about 250 million records
> and this will be growing by 100 million every week. At a time the table
> should contain just 13 weeks of data. when the 14th week data needs to
> be loaded the first week's data has to be deleted.
> And this deletes 100 million every week, since the delete is taking lot
> of transaction log space the job is not successful.
> Can you please help with what are the approaches we can take to fix
> this problem?
> Performance and transaction log are the issues we are facing. We tried
> deletion in steps too but that also is taking time. What are the
> different ways we can address this quickly.
> Please reply at the earliest.
> Thanks
> Harish
>|||I forgot to mention that SQL 2005 Enterprise introduces new table and index
partitioning features that make it easier to partition large tables.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23$vpN7h4FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Deleting in smaller separate transaction batches will reduce transaction
> log space requirements and improve performance You'll need to experiment
> to determine the best batch size for your environment. In my experience,
> the optimum size is somewhere around the amount of SQL Server memory. For
> example, with 2GB RAM and a 100 byte row size, delete no more than 20M
> rows at a time. If you are using the FULL are BULK_LOGGED recovery model,
> you'll also need to backup the log between batches to keep the log size
> manageable. Alternatively, you can use change to the SIMPLE model for the
> duration of the delete script and change back to your normal recovery
> model afterward. Don't forget to perform a full database backup following
> the change from SIMPLE recovery.
> Use the TABLOCKX hint if possible. Ideally, the table's clustered index
> should be the column(s) used for your delete criteria.
> Another option is to partition data based on your delete criteria
> (separate table for each week). This will allow you to simply drop the
> table containing the oldest data. The partitioning implementation can be
> made transparent to applications by using a UNION ALL view. After
> dropping the oldest table, you create a new table for the latest data and
> change the view accordingly. If you can adhere to the rules for local
> partitioned views as described in the Books Online, there are performance
> advantages with the partitioned view and the view is updatable as well.
> See the example below.
> CREATE TABLE OrderDetails_20051030
> (
> OrderID int NOT NULL,
> ProductID int NOT NULL,
> Quantity int NOT NULL,
> UnitPrice decimal(9, 2) NOT NULL,
> OrderDate smalldatetime NOT NULL
> CONSTRAINT CK_OrderDetails_20051030
> CHECK (OrderDate > '20051023' AND OrderDate <= '20051030')
> )
> ALTER TABLE OrderDetails_20051030
> ADD CONSTRAINT PK_OrderDetails_20051030
> PRIMARY KEY(OrderDate, OrderID, ProductID)
> GO
> CREATE TABLE OrderDetails_20051106
> (
> OrderID int NOT NULL,
> ProductID int NOT NULL,
> Quantity int NOT NULL,
> UnitPrice decimal(9, 2) NOT NULL,
> OrderDate smalldatetime NOT NULL
> CONSTRAINT CK_OrderDetails_20051106
> CHECK (OrderDate > '20051030' AND OrderDate <= '20051106')
> )
> ALTER TABLE OrderDetails_20051106
> ADD CONSTRAINT PK_OrderDetailsOrderDetails_20051106
> PRIMARY KEY(OrderDate, OrderID, ProductID)
> GO
> CREATE TABLE OrderDetails_20051113
> (
> OrderID int NOT NULL,
> ProductID int NOT NULL,
> Quantity int NOT NULL,
> UnitPrice decimal(9, 2) NOT NULL,
> OrderDate smalldatetime NOT NULL
> CONSTRAINT CK_OrderDetails_20051113_OrderDate
> CHECK (OrderDate > '20051106' AND OrderDate <= '20051113')
> )
> ALTER TABLE OrderDetails_20051113
> ADD CONSTRAINT PK_OrderDetails_20051113
> PRIMARY KEY(OrderDate, OrderID, ProductID)
> GO
> CREATE VIEW OrderDetails AS
> SELECT
> OrderID,
> ProductID,
> Quantity,
> UnitPrice,
> OrderDate
> FROM OrderDetails_20051030
> UNION ALL
> SELECT
> OrderID,
> ProductID,
> Quantity,
> UnitPrice,
> OrderDate
> FROM OrderDetails_20051106
> UNION ALL
> SELECT
> OrderID,
> ProductID,
> Quantity,
> UnitPrice,
> OrderDate
> FROM OrderDetails_20051113
> GO
> INSERT INTO OrderDetails VALUES(1, 1, 1, 1, '20051030')
> INSERT INTO OrderDetails VALUES(2, 1, 1, 1, '20051106')
> INSERT INTO OrderDetails VALUES(3, 1, 1, 1, '20051113')
> GO
> --execution plan shows only OrderDetails_20051106 is accessed because
> --all PK columns are referenced in this query
> SELECT
> a.OrderID,
> a.ProductID,
> a.Quantity,
> a.UnitPrice,
> a.OrderDate
> FROM OrderDetails a
> WHERE a.OrderDate = '20051106' AND OrderID = 2 AND ProductID = 1
> GO
> --to remove oldest week:
> DROP VIEW OrderDetails
> GO
> --drop oldest table
> DROP TABLE OrderDetails_20051030
> GO
> --create table for new data
> CREATE TABLE OrderDetails_20051120
> (
> OrderID int NOT NULL,
> ProductID int NOT NULL,
> Quantity int NOT NULL,
> UnitPrice decimal(9, 2) NOT NULL,
> OrderDate smalldatetime NOT NULL
> CONSTRAINT CK_OrderDetails_20051120_OrderDate
> CHECK (OrderDate > '20051113' AND OrderDate <= '20051120')
> )
> ALTER TABLE OrderDetails_20051120
> ADD CONSTRAINT PK_OrderDetails_20051120
> PRIMARY KEY(OrderDate, OrderID, ProductID)
> GO
> --create view with current tables
> CREATE VIEW OrderDetails AS
> SELECT
> OrderID,
> ProductID,
> Quantity,
> UnitPrice,
> OrderDate
> FROM OrderDetails_20051106
> UNION ALL
> SELECT
> OrderID,
> ProductID,
> Quantity,
> UnitPrice,
> OrderDate
> FROM OrderDetails_20051113
> UNION ALL
> SELECT
> OrderID,
> ProductID,
> Quantity,
> UnitPrice,
> OrderDate
> FROM OrderDetails_20051120
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "harish" <harish.prabhala@.gmail.com> wrote in message
> news:1131163435.343059.179520@.f14g2000cwb.googlegroups.com...
>|||Hey
Thanks. We have an index on four columns in this table. For Ex A, B, C
and D
The delete statement's where clause has the conditions for A, B and C
The delete statement's where clause has the conditions for just A
Which of the two's performance will be faster?
We tried something like this:
SET ROWCOUNT 5000;
WHILE 1 = 1
BEGIN
DELETE FROM T1 WHERE dt < '20030101' -- original delete
IF @.@.rowcount < 5000 BREAK;
END
SET ROWCOUNT 0;
1) Does this setting ROWCOUNT first sort the table and then delete?
2) The above query is executed to delete all records satisfying the
condition in steps of 5000 until the delete is comple.
How can I stop it after one 5000?|||On 5 Nov 2005 11:01:30 -0800, harish wrote:
(snip)
Hi Harish,
I just replied to the same question in another thread.
Could you please ask your questions in JUST ONE place, and in JUST ONE
group? I've seen your messages scattered over several groups, and
several different messages in just this group. Many of them have
attracted replies. It's very hard to keep track of what is going on in
all thesse threads, and it's a waste of other people's time if someone
posts a reply to you that you already had received in another group.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi
Sorry for that. I was new to the group and not sure which group is
active.
Thanks a lot for the information
Regards
Harish

DELETING 100 million from a table weekly SQl SERVER 2000

DELETING 100 million from a table weekly SQl SERVER 2000
Hi All
We have a table in SQL SERVER 2000 which has about 250 million records
and this will be growing by 100 million every week. At a time the table
should contain just 13 weeks of data. when the 14th week data needs to
be loaded the first week's data has to be deleted.
And this deletes 100 million every week, since the delete is taking lot
of transaction log space the job is not successful.
Can you please help with what are the approaches we can take to fix
this problem?
Performance and transaction log are the issues we are facing. We tried
deletion in steps too but that also is taking time. What are the
different ways we can address this quickly.
Please reply at the earliest.
Thanks
Harish
If you can move up to SQL Server 2005, then you should look at partitioning the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"harish" <harish.prabhala@.gmail.com> wrote in message
news:1131162784.737373.142270@.g43g2000cwa.googlegr oups.com...
> DELETING 100 million from a table weekly SQl SERVER 2000
> Hi All
> We have a table in SQL SERVER 2000 which has about 250 million records
> and this will be growing by 100 million every week. At a time the table
> should contain just 13 weeks of data. when the 14th week data needs to
> be loaded the first week's data has to be deleted.
> And this deletes 100 million every week, since the delete is taking lot
> of transaction log space the job is not successful.
> Can you please help with what are the approaches we can take to fix
> this problem?
> Performance and transaction log are the issues we are facing. We tried
> deletion in steps too but that also is taking time. What are the
> different ways we can address this quickly.
> Please reply at the earliest.
> Thanks
> Harish
>
|||Hey
Thanks. We have an index on four columns in this table. For Ex A, B, C
and D
The delete statement's where clause has the conditions for A, B and C
The delete statement's where clause has the conditions for just A
Which of the two's performance will be faster?
We tried something like this:
SET ROWCOUNT 5000;
WHILE 1 = 1
BEGIN
DELETE FROM T1 WHERE dt < '20030101' -- original delete
IF @.@.rowcount < 5000 BREAK;
END
SET ROWCOUNT 0;
1) Does this setting ROWCOUNT first sort the table and then delete?
2) The above query is executed to delete all records satisfying the
condition in steps of 5000 until the delete is comple.
How can I stop it after one 5000?
|||"harish" <harish.prabhala@.gmail.com> wrote in message
news:1131217180.922372.211460@.g44g2000cwa.googlegr oups.com...
> Thanks. We have an index on four columns in this table. For Ex A, B, C
> and D
> The delete statement's where clause has the conditions for A, B and C
> The delete statement's where clause has the conditions for just A
>
> Which of the two's performance will be faster?
There should be virtually no difference. What might be faster would be
using a predicate that can be satisfied by the clustered index, to avoid
doing two lookups.

> 1) Does this setting ROWCOUNT first sort the table and then delete?
No; it just deletes the first N rows it finds that satisfy the
predicate.

> 2) The above query is executed to delete all records satisfying the
> condition in steps of 5000 until the delete is comple.
> How can I stop it after one 5000?
Remove the loop.
Adam Machanic
Pro SQL Server 2005, available now
www.apress.com/book/bookDisplay.html?bID=457
|||On 5 Nov 2005 10:59:40 -0800, harish wrote:
(snip)
Hi Harish,
I just replied to the same question in another thread.
Could you please ask your questions in JUST ONE place, and in JUST ONE
group? I've seen your messages scattered over several groups, and
several different messages in just this group. Many of them have
attracted replies. It's very hard to keep track of what is going on in
all thesse threads, and it's a waste of other people's time if someone
posts a reply to you that you already had received in another group.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Where's the other thread?
Adam Machanic
Pro SQL Server 2005, available now
www.apress.com/book/bookDisplay.html?bID=457
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:uq5qm1drmjlp5kt0hp0jl3krefsm9qgff8@.4ax.com...
> On 5 Nov 2005 10:59:40 -0800, harish wrote:
> (snip)
> Hi Harish,
> I just replied to the same question in another thread.
> Could you please ask your questions in JUST ONE place, and in JUST ONE
> group? I've seen your messages scattered over several groups, and
> several different messages in just this group. Many of them have
> attracted replies. It's very hard to keep track of what is going on in
> all thesse threads, and it's a waste of other people's time if someone
> posts a reply to you that you already had received in another group.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Sat, 5 Nov 2005 18:08:27 -0500, Adam Machanic wrote:

>Where's the other thread?
Hi Adam,
One in microsoft.public.sqlserver.newusers (that's where I replied to
his questions - but don't bother to look it up, my answers are virtually
identical; to yours).
One in microsoft.public.sqlserver.server.
And no less than THREE in microsoft.public.sqlserver.programming.
If there were more, I didn't see them.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo
This is Harish again
set rowcount 100000
declare @.a int
while 1=1
begin
begin tran del1
delete from dbo.extt_vpm_ldr_stg_2
select @.a = @.@.rowcount
print @.a
commit tran del1
if @.a < 100000 break;
end
TOTAL TABLE SIZE - 650 000 records
I am using the above statement to delete in chunks on 100000.
After every 100000 I have put a COMMIT TRAN. Hence as per the logic it
should delete 100000 commit and delete the next 100000.
And the transaction log is suppose 100MB before the statement is
executed. It is increasing in steps of some 30MB for each delete to
upto some 250MB and then goes back to some 50MB.
What we need is the transaction log must increase for every delete and
drop and again increase and so on.
How can the above statement be modified to take care of this behaviour?
Please reply asap.
Thanks
Harish
|||On 9 Nov 2005 08:10:52 -0800, harish wrote:

>Hi Hugo
>This is Harish again
Hi Harish,
This is my previous reply again
Could you please ask your questions in JUST ONE place, and in JUST ONE
group? I've seen your messages scattered over several groups, and
several different messages in just this group. Many of them have
attracted replies. It's very hard to keep track of what is going on in
all thesse threads, and it's a waste of other people's time if someone
posts a reply to you that you already had received in another group.
BTW, I already saw your question in one of the other groups, and I have
already seen at least one answer (can't recall off the top of my head
who wrote it, though).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

DELETING 100 million from a table weekly SQl SERVER 2000

DELETING 100 million from a table weekly SQl SERVER 2000
Hi All
We have a table in SQL SERVER 2000 which has about 250 million records
and this will be growing by 100 million every week. At a time the table
should contain just 13 weeks of data. when the 14th week data needs to
be loaded the first week's data has to be deleted.
And this deletes 100 million every week, since the delete is taking lot
of transaction log space the job is not successful.
Can you please help with what are the approaches we can take to fix
this problem?
Performance and transaction log are the issues we are facing. We tried
deletion in steps too but that also is taking time. What are the
different ways we can address this quickly.
Please reply at the earliest.
Thanks
HarishYou could try inserting the clean records i.e. Current 13 weeks data into a
a temporary table.
Then truncate the table with all data in (bear in mind any identity
columns).
Reinsert the clean records into the table.
Again, bear in mind any idientity columns if you need to keep the ids in
sync as truncate will reset the seed count, also, you can't truncate a table
with a FK so you'd need to work around that too.
Not the best method but may be quicker than your current process.
Immy
"harish" <harish.prabhala@.gmail.com> wrote in message
news:1131163435.343059.179520@.f14g2000cwb.googlegroups.com...
> DELETING 100 million from a table weekly SQl SERVER 2000
> Hi All
> We have a table in SQL SERVER 2000 which has about 250 million records
> and this will be growing by 100 million every week. At a time the table
> should contain just 13 weeks of data. when the 14th week data needs to
> be loaded the first week's data has to be deleted.
> And this deletes 100 million every week, since the delete is taking lot
> of transaction log space the job is not successful.
> Can you please help with what are the approaches we can take to fix
> this problem?
> Performance and transaction log are the issues we are facing. We tried
> deletion in steps too but that also is taking time. What are the
> different ways we can address this quickly.
> Please reply at the earliest.
> Thanks
> Harish
>|||> Performance and transaction log are the issues we are facing. We tried
> deletion in steps too but that also is taking time. What are the
> different ways we can address this quickly.
Deleting in smaller separate transaction batches will reduce transaction log
space requirements and improve performance You'll need to experiment to
determine the best batch size for your environment. In my experience, the
optimum size is somewhere around the amount of SQL Server memory. For
example, with 2GB RAM and a 100 byte row size, delete no more than 20M rows
at a time. If you are using the FULL are BULK_LOGGED recovery model, you'll
also need to backup the log between batches to keep the log size manageable.
Alternatively, you can use change to the SIMPLE model for the duration of
the delete script and change back to your normal recovery model afterward.
Don't forget to perform a full database backup following the change from
SIMPLE recovery.
Use the TABLOCKX hint if possible. Ideally, the table's clustered index
should be the column(s) used for your delete criteria.
Another option is to partition data based on your delete criteria (separate
table for each week). This will allow you to simply drop the table
containing the oldest data. The partitioning implementation can be made
transparent to applications by using a UNION ALL view. After dropping the
oldest table, you create a new table for the latest data and change the view
accordingly. If you can adhere to the rules for local partitioned views as
described in the Books Online, there are performance advantages with the
partitioned view and the view is updatable as well. See the example below.
CREATE TABLE OrderDetails_20051030
(
OrderID int NOT NULL,
ProductID int NOT NULL,
Quantity int NOT NULL,
UnitPrice decimal(9, 2) NOT NULL,
OrderDate smalldatetime NOT NULL
CONSTRAINT CK_OrderDetails_20051030
CHECK (OrderDate > '20051023' AND OrderDate <= '20051030')
)
ALTER TABLE OrderDetails_20051030
ADD CONSTRAINT PK_OrderDetails_20051030
PRIMARY KEY(OrderDate, OrderID, ProductID)
GO
CREATE TABLE OrderDetails_20051106
(
OrderID int NOT NULL,
ProductID int NOT NULL,
Quantity int NOT NULL,
UnitPrice decimal(9, 2) NOT NULL,
OrderDate smalldatetime NOT NULL
CONSTRAINT CK_OrderDetails_20051106
CHECK (OrderDate > '20051030' AND OrderDate <= '20051106')
)
ALTER TABLE OrderDetails_20051106
ADD CONSTRAINT PK_OrderDetailsOrderDetails_20051106
PRIMARY KEY(OrderDate, OrderID, ProductID)
GO
CREATE TABLE OrderDetails_20051113
(
OrderID int NOT NULL,
ProductID int NOT NULL,
Quantity int NOT NULL,
UnitPrice decimal(9, 2) NOT NULL,
OrderDate smalldatetime NOT NULL
CONSTRAINT CK_OrderDetails_20051113_OrderDate
CHECK (OrderDate > '20051106' AND OrderDate <= '20051113')
)
ALTER TABLE OrderDetails_20051113
ADD CONSTRAINT PK_OrderDetails_20051113
PRIMARY KEY(OrderDate, OrderID, ProductID)
GO
CREATE VIEW OrderDetails AS
SELECT
OrderID,
ProductID,
Quantity,
UnitPrice,
OrderDate
FROM OrderDetails_20051030
UNION ALL
SELECT
OrderID,
ProductID,
Quantity,
UnitPrice,
OrderDate
FROM OrderDetails_20051106
UNION ALL
SELECT
OrderID,
ProductID,
Quantity,
UnitPrice,
OrderDate
FROM OrderDetails_20051113
GO
INSERT INTO OrderDetails VALUES(1, 1, 1, 1, '20051030')
INSERT INTO OrderDetails VALUES(2, 1, 1, 1, '20051106')
INSERT INTO OrderDetails VALUES(3, 1, 1, 1, '20051113')
GO
--execution plan shows only OrderDetails_20051106 is accessed because
--all PK columns are referenced in this query
SELECT
a.OrderID,
a.ProductID,
a.Quantity,
a.UnitPrice,
a.OrderDate
FROM OrderDetails a
WHERE a.OrderDate = '20051106' AND OrderID = 2 AND ProductID = 1
GO
--to remove oldest week:
DROP VIEW OrderDetails
GO
--drop oldest table
DROP TABLE OrderDetails_20051030
GO
--create table for new data
CREATE TABLE OrderDetails_20051120
(
OrderID int NOT NULL,
ProductID int NOT NULL,
Quantity int NOT NULL,
UnitPrice decimal(9, 2) NOT NULL,
OrderDate smalldatetime NOT NULL
CONSTRAINT CK_OrderDetails_20051120_OrderDate
CHECK (OrderDate > '20051113' AND OrderDate <= '20051120')
)
ALTER TABLE OrderDetails_20051120
ADD CONSTRAINT PK_OrderDetails_20051120
PRIMARY KEY(OrderDate, OrderID, ProductID)
GO
--create view with current tables
CREATE VIEW OrderDetails AS
SELECT
OrderID,
ProductID,
Quantity,
UnitPrice,
OrderDate
FROM OrderDetails_20051106
UNION ALL
SELECT
OrderID,
ProductID,
Quantity,
UnitPrice,
OrderDate
FROM OrderDetails_20051113
UNION ALL
SELECT
OrderID,
ProductID,
Quantity,
UnitPrice,
OrderDate
FROM OrderDetails_20051120
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"harish" <harish.prabhala@.gmail.com> wrote in message
news:1131163435.343059.179520@.f14g2000cwb.googlegroups.com...
> DELETING 100 million from a table weekly SQl SERVER 2000
> Hi All
> We have a table in SQL SERVER 2000 which has about 250 million records
> and this will be growing by 100 million every week. At a time the table
> should contain just 13 weeks of data. when the 14th week data needs to
> be loaded the first week's data has to be deleted.
> And this deletes 100 million every week, since the delete is taking lot
> of transaction log space the job is not successful.
> Can you please help with what are the approaches we can take to fix
> this problem?
> Performance and transaction log are the issues we are facing. We tried
> deletion in steps too but that also is taking time. What are the
> different ways we can address this quickly.
> Please reply at the earliest.
> Thanks
> Harish
>|||I forgot to mention that SQL 2005 Enterprise introduces new table and index
partitioning features that make it easier to partition large tables.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23$vpN7h4FHA.3000@.TK2MSFTNGP12.phx.gbl...
>> Performance and transaction log are the issues we are facing. We tried
>> deletion in steps too but that also is taking time. What are the
>> different ways we can address this quickly.
> Deleting in smaller separate transaction batches will reduce transaction
> log space requirements and improve performance You'll need to experiment
> to determine the best batch size for your environment. In my experience,
> the optimum size is somewhere around the amount of SQL Server memory. For
> example, with 2GB RAM and a 100 byte row size, delete no more than 20M
> rows at a time. If you are using the FULL are BULK_LOGGED recovery model,
> you'll also need to backup the log between batches to keep the log size
> manageable. Alternatively, you can use change to the SIMPLE model for the
> duration of the delete script and change back to your normal recovery
> model afterward. Don't forget to perform a full database backup following
> the change from SIMPLE recovery.
> Use the TABLOCKX hint if possible. Ideally, the table's clustered index
> should be the column(s) used for your delete criteria.
> Another option is to partition data based on your delete criteria
> (separate table for each week). This will allow you to simply drop the
> table containing the oldest data. The partitioning implementation can be
> made transparent to applications by using a UNION ALL view. After
> dropping the oldest table, you create a new table for the latest data and
> change the view accordingly. If you can adhere to the rules for local
> partitioned views as described in the Books Online, there are performance
> advantages with the partitioned view and the view is updatable as well.
> See the example below.
> CREATE TABLE OrderDetails_20051030
> (
> OrderID int NOT NULL,
> ProductID int NOT NULL,
> Quantity int NOT NULL,
> UnitPrice decimal(9, 2) NOT NULL,
> OrderDate smalldatetime NOT NULL
> CONSTRAINT CK_OrderDetails_20051030
> CHECK (OrderDate > '20051023' AND OrderDate <= '20051030')
> )
> ALTER TABLE OrderDetails_20051030
> ADD CONSTRAINT PK_OrderDetails_20051030
> PRIMARY KEY(OrderDate, OrderID, ProductID)
> GO
> CREATE TABLE OrderDetails_20051106
> (
> OrderID int NOT NULL,
> ProductID int NOT NULL,
> Quantity int NOT NULL,
> UnitPrice decimal(9, 2) NOT NULL,
> OrderDate smalldatetime NOT NULL
> CONSTRAINT CK_OrderDetails_20051106
> CHECK (OrderDate > '20051030' AND OrderDate <= '20051106')
> )
> ALTER TABLE OrderDetails_20051106
> ADD CONSTRAINT PK_OrderDetailsOrderDetails_20051106
> PRIMARY KEY(OrderDate, OrderID, ProductID)
> GO
> CREATE TABLE OrderDetails_20051113
> (
> OrderID int NOT NULL,
> ProductID int NOT NULL,
> Quantity int NOT NULL,
> UnitPrice decimal(9, 2) NOT NULL,
> OrderDate smalldatetime NOT NULL
> CONSTRAINT CK_OrderDetails_20051113_OrderDate
> CHECK (OrderDate > '20051106' AND OrderDate <= '20051113')
> )
> ALTER TABLE OrderDetails_20051113
> ADD CONSTRAINT PK_OrderDetails_20051113
> PRIMARY KEY(OrderDate, OrderID, ProductID)
> GO
> CREATE VIEW OrderDetails AS
> SELECT
> OrderID,
> ProductID,
> Quantity,
> UnitPrice,
> OrderDate
> FROM OrderDetails_20051030
> UNION ALL
> SELECT
> OrderID,
> ProductID,
> Quantity,
> UnitPrice,
> OrderDate
> FROM OrderDetails_20051106
> UNION ALL
> SELECT
> OrderID,
> ProductID,
> Quantity,
> UnitPrice,
> OrderDate
> FROM OrderDetails_20051113
> GO
> INSERT INTO OrderDetails VALUES(1, 1, 1, 1, '20051030')
> INSERT INTO OrderDetails VALUES(2, 1, 1, 1, '20051106')
> INSERT INTO OrderDetails VALUES(3, 1, 1, 1, '20051113')
> GO
> --execution plan shows only OrderDetails_20051106 is accessed because
> --all PK columns are referenced in this query
> SELECT
> a.OrderID,
> a.ProductID,
> a.Quantity,
> a.UnitPrice,
> a.OrderDate
> FROM OrderDetails a
> WHERE a.OrderDate = '20051106' AND OrderID = 2 AND ProductID = 1
> GO
> --to remove oldest week:
> DROP VIEW OrderDetails
> GO
> --drop oldest table
> DROP TABLE OrderDetails_20051030
> GO
> --create table for new data
> CREATE TABLE OrderDetails_20051120
> (
> OrderID int NOT NULL,
> ProductID int NOT NULL,
> Quantity int NOT NULL,
> UnitPrice decimal(9, 2) NOT NULL,
> OrderDate smalldatetime NOT NULL
> CONSTRAINT CK_OrderDetails_20051120_OrderDate
> CHECK (OrderDate > '20051113' AND OrderDate <= '20051120')
> )
> ALTER TABLE OrderDetails_20051120
> ADD CONSTRAINT PK_OrderDetails_20051120
> PRIMARY KEY(OrderDate, OrderID, ProductID)
> GO
> --create view with current tables
> CREATE VIEW OrderDetails AS
> SELECT
> OrderID,
> ProductID,
> Quantity,
> UnitPrice,
> OrderDate
> FROM OrderDetails_20051106
> UNION ALL
> SELECT
> OrderID,
> ProductID,
> Quantity,
> UnitPrice,
> OrderDate
> FROM OrderDetails_20051113
> UNION ALL
> SELECT
> OrderID,
> ProductID,
> Quantity,
> UnitPrice,
> OrderDate
> FROM OrderDetails_20051120
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "harish" <harish.prabhala@.gmail.com> wrote in message
> news:1131163435.343059.179520@.f14g2000cwb.googlegroups.com...
>> DELETING 100 million from a table weekly SQl SERVER 2000
>> Hi All
>> We have a table in SQL SERVER 2000 which has about 250 million records
>> and this will be growing by 100 million every week. At a time the table
>> should contain just 13 weeks of data. when the 14th week data needs to
>> be loaded the first week's data has to be deleted.
>> And this deletes 100 million every week, since the delete is taking lot
>> of transaction log space the job is not successful.
>> Can you please help with what are the approaches we can take to fix
>> this problem?
>> Performance and transaction log are the issues we are facing. We tried
>> deletion in steps too but that also is taking time. What are the
>> different ways we can address this quickly.
>> Please reply at the earliest.
>> Thanks
>> Harish
>|||Hey
Thanks. We have an index on four columns in this table. For Ex A, B, C
and D
The delete statement's where clause has the conditions for A, B and C
The delete statement's where clause has the conditions for just A
Which of the two's performance will be faster?
We tried something like this:
SET ROWCOUNT 5000;
WHILE 1 = 1
BEGIN
DELETE FROM T1 WHERE dt < '20030101' -- original delete
IF @.@.rowcount < 5000 BREAK;
END
SET ROWCOUNT 0;
1) Does this setting ROWCOUNT first sort the table and then delete?
2) The above query is executed to delete all records satisfying the
condition in steps of 5000 until the delete is comple.
How can I stop it after one 5000?|||On 5 Nov 2005 11:01:30 -0800, harish wrote:
(snip)
Hi Harish,
I just replied to the same question in another thread.
Could you please ask your questions in JUST ONE place, and in JUST ONE
group? I've seen your messages scattered over several groups, and
several different messages in just this group. Many of them have
attracted replies. It's very hard to keep track of what is going on in
all thesse threads, and it's a waste of other people's time if someone
posts a reply to you that you already had received in another group.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi
Sorry for that. I was new to the group and not sure which group is
active.
Thanks a lot for the information
Regards
Harish