Deleteing rows

I have to delete 30K rows that were inserted into the orders table by
mistake.
The orderid field is used as a FK on many other tables so the delete takes
forever.
Is there any way to increase the performance of this delete?
In this case I know there will not be any associated FKs that reference
these orderids in other tables because the insert I am seeking to undo was
made only to the orders table. Therefore no orphans will be produced (i.e.,
no RI violation) upon delete.
Is it possible to perform the delete without constraint checking? If not,
is there anything I can do to speed up the process?
ThanksDo you have indexes on the FK columns in the referencing tables? That can
speed up such an operation significantly.
You can disable the FK constraint (see ALTER TABLE), but that disabling
applies for all connections, so make sure you are alone on the database
while doing so, if you want to take that route.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"David F" <davef@.nksj.ru> wrote in message
news:uS8KQd5AEHA.3348@.TK2MSFTNGP11.phx.gbl...
> I have to delete 30K rows that were inserted into the orders table by
> mistake.
> The orderid field is used as a FK on many other tables so the delete
takes
> forever.
> Is there any way to increase the performance of this delete?
> In this case I know there will not be any associated FKs that reference
> these orderids in other tables because the insert I am seeking to undo was
> made only to the orders table. Therefore no orphans will be produced
(i.e.,
> no RI violation) upon delete.
> Is it possible to perform the delete without constraint checking? If not,
> is there anything I can do to speed up the process?
> Thanks
>|||Thanks Tibor.
So it looks like I will not have to DROP the FK, just disable and then
reenable like:
--disable FK
ALTER TABLE child NOCHECK CONSTRAINT fk_id
--re-enable FK
ALTER TABLE child CHECK CONSTRAINT fk_id
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTRoxf5AEHA.1028@.TK2MSFTNGP11.phx.gbl...
> Do you have indexes on the FK columns in the referencing tables? That can
> speed up such an operation significantly.
> You can disable the FK constraint (see ALTER TABLE), but that disabling
> applies for all connections, so make sure you are alone on the database
> while doing so, if you want to take that route.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "David F" <davef@.nksj.ru> wrote in message
> news:uS8KQd5AEHA.3348@.TK2MSFTNGP11.phx.gbl...
> > I have to delete 30K rows that were inserted into the orders table by
> > mistake.
> >
> > The orderid field is used as a FK on many other tables so the delete
> takes
> > forever.
> >
> > Is there any way to increase the performance of this delete?
> >
> > In this case I know there will not be any associated FKs that reference
> > these orderids in other tables because the insert I am seeking to undo
was
> > made only to the orders table. Therefore no orphans will be produced
> (i.e.,
> > no RI violation) upon delete.
> >
> > Is it possible to perform the delete without constraint checking? If
not,
> > is there anything I can do to speed up the process?
> >
> > Thanks
> >
> >
>|||Yes, but did you check the indexes first? Having indexes on a FK column is
often crucial for reasonable performance when doing update and delete in the
referenced table. And not only that, these indexes can help your join
operations significantly (you often join over primary key - foreign key
relationships).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Dave" <dave@.nodomain.tv> wrote in message
news:eZjbGj6AEHA.3256@.TK2MSFTNGP09.phx.gbl...
> Thanks Tibor.
> So it looks like I will not have to DROP the FK, just disable and then
> reenable like:
> --disable FK
> ALTER TABLE child NOCHECK CONSTRAINT fk_id
> --re-enable FK
> ALTER TABLE child CHECK CONSTRAINT fk_id
> Thanks
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OTRoxf5AEHA.1028@.TK2MSFTNGP11.phx.gbl...
> > Do you have indexes on the FK columns in the referencing tables? That
can
> > speed up such an operation significantly.
> >
> > You can disable the FK constraint (see ALTER TABLE), but that disabling
> > applies for all connections, so make sure you are alone on the database
> > while doing so, if you want to take that route.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> >
> >
> > "David F" <davef@.nksj.ru> wrote in message
> > news:uS8KQd5AEHA.3348@.TK2MSFTNGP11.phx.gbl...
> > > I have to delete 30K rows that were inserted into the orders table by
> > > mistake.
> > >
> > > The orderid field is used as a FK on many other tables so the delete
> > takes
> > > forever.
> > >
> > > Is there any way to increase the performance of this delete?
> > >
> > > In this case I know there will not be any associated FKs that
reference
> > > these orderids in other tables because the insert I am seeking to undo
> was
> > > made only to the orders table. Therefore no orphans will be produced
> > (i.e.,
> > > no RI violation) upon delete.
> > >
> > > Is it possible to perform the delete without constraint checking? If
> not,
> > > is there anything I can do to speed up the process?
> > >
> > > Thanks
> > >
> > >
> >
> >
>