How to delete a big amount of data

[akej via SQLMonster.com]

Hi, suppose i have 3 tables

main table

CREATE TABLE table1 (
[t1ID] [int] primary key ,
[t1Name] nvatchar(50) NOT NULL,
............... .............

)

and connections tables (t1ID is a foreign key in all connection tables)

CREATE TABLE tbl2 (
[t1ID] [int] not null,
[gID] [int] not null
PRIMARY KEY (t1ID, gID)
)

CREATE TABLE tbl3 (
[t1ID] [int] not null,
[pID] [int] not null
PRIMARY KEY (t1ID, pID)
)

*************** *************** *************** **********

t1ID CLISTERED INDEX
in othre tables each primary key is clustered.

gID and pID are keys from other table: gtable, ptable



Now in case one row was deletes from table1 i need to delete all t1ID's
from the connection tables (each of connection may have e.g. 200000 rows).
If u use delete on cascade it's take a lot of time, i want to delete let's
say 1000 rows till all irrelevant rows will be deleted.

How To accomplish this??
Thanks.

--
Message posted via http://www.sqlmonster.com

[Erland Sommarskog]

Re: How to delete a big amount of data

akej via SQLMonster.com (forum@nospam.S QLMonster.com) writes:[color=blue]
> Now in case one row was deletes from table1 i need to delete all t1ID's
> from the connection tables (each of connection may have e.g. 200000 rows).
> If u use delete on cascade it's take a lot of time, i want to delete let's
> say 1000 rows till all irrelevant rows will be deleted.[/color]

Well, if you have FK constraints from the connections tables to the
main table, you cannot delete the connection from that table until all
references from the connection tables are gone. So while you could batch
things like:

SET ROWCOUNT 10000
WHILE EXISTS (SELECT * FROM tbl2 WHERE tl1D = @id_to_delete)
DELETE tbl2 WHERE tlID = @id_to_delete
SET ROWCOUNT 0

I can't really see that it will help you.

You could of course drop the foreign-key constraint, and the start some
background process that deletes the rows in the other table, but that's
a pretty wild thing to do.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[akej via SQLMonster.com]

Re: How to delete a big amount of data

Thanks Erland, your suggestion is helpful however how can implement it,
i guess by job that must define, so when this job will be start and who
will start it??

Also i can remove all these relationship, only the ID's will be stay, now
each time when t1ID will be deleted i will put this t1ID in some pemanent
table suppose [toDelete] and from now i need to start job or jobs that
let's say every 10 minut will remove 1000 rows from each connection table
or from first connection table after this from second and so on, in the end
i need to remove this t1ID. However i'm new in sql and i don't really know
how to implement it. Maybe u reject this and have more efficient suggestion.

Thanks

--
Message posted via http://www.sqlmonster.com

[akej via SQLMonster.com]

Re: How to delete a big amount of data

Any ideas ???????

--
Message posted via http://www.sqlmonster.com

[Erland Sommarskog]

Re: How to delete a big amount of data

akej via SQLMonster.com (forum@SQLMonst er.com) writes:[color=blue]
> Thanks Erland, your suggestion is helpful however how can implement it,
> i guess by job that must define, so when this job will be start and who
> will start it??
>
> Also i can remove all these relationship, only the ID's will be stay,
> now each time when t1ID will be deleted i will put this t1ID in some
> pemanent table suppose [toDelete] and from now i need to start job or
> jobs that let's say every 10 minut will remove 1000 rows from each
> connection table or from first connection table after this from second
> and so on, in the end i need to remove this t1ID. However i'm new in sql
> and i don't really know how to implement it. Maybe u reject this and
> have more efficient suggestion.[/color]

Well, since I don't like dropping foreign-key constraints, I would first
look into the situation a little closer. It seems that you have a
"connection " and you have 200000 rows added for that connection that
you want to drop. Maybe there is reason to consider why all those rows
were added in the first place?

This may be a stupid question, but pleaes keep in mind that since I know
nothing about your business, I have to try some stabs in the dark.

But if you truly want an asynchronous delete, I would set up a in SQL
Agent that runs with some frequency and which deletes orphaned rows.
There could be a trigger on the table, so that when a row is deleted,
the job is started, but frankly, I would actually skip that step. This
job could be devised so that it deletes all orphans it can find, possibly
batched with SET ROWCOUNT. Or it could be written so that it stops after
some time, and take remaining oprhans on the next time, depending on
how you want to spread the load.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp