Re: Delete query taking long time to execute

Thank you Hugo for the information.

FYI, I have asked him to do the NOCHECK when the system is offline (No users
are connected) and after the delete activity he can
make ALTER statement WITH CHECK. For one of my manual delete ( 5 million
record deletion) I did this and identified a huge
performance difference.

Thanks
Hari




"Hugo Kornelis" <hugo@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:55ccm2dgsfvcdco0iogo0oge8j9bvs953c@xxxxxxxxxx
On Thu, 23 Nov 2006 02:08:26 -0600, Hari Prasad wrote:

Hi,

This is because of the multiple relationship. While deleting it checks all
the relationship. Where as if you execute a SELECT statement for the same
query
it will be faster. If you can do this during offline you could disable the
constraints [See ALTER TABLE WITH NOCHECK] to make the DELETE faster.
Since the number of records is very less it is not required to disable the
constratins

Hi Hari,

There are of course two downsides to disabling the constraints:

1. If there is some error in the manual checking process, or some
concurrent opdate after the manual checking, the database may be left in
an unconsistent state.

2. Even if all checks were made aith 100% accuracy, SQL Server doesn't
know this, so the constraints are marked as "not trusted" once they are
re-enabled. That means that the optimizer can no longer rely on those
constraints for considering shortcuts for the query plan, which may
result in longer running queries. (Unless you use the WITH CHECK option
when the constraints are re-enabled, but in that case you still get the
same performance hit, only at a later time).

--
Hugo Kornelis, SQL Server MVP


.

Relevant Pages

  • Re: Creating Not Null contraints in SE
    ... dbschema is failing with -100 errors. ... NOT NULL constraints cannot be 'ADDED' they have to be MODIFIED: ... ALTER TABLE xredit_cards MODIFY (somecolumn some type NOT NULL ... When you dbexport, ...
    (comp.databases.informix)
  • drop column fails
    ... alter table TE20_AREA ... checked for indexes, constraints, relations ... help because db modification needs to be in a batch job :- ...
    (microsoft.public.sqlserver.server)
  • How to check if triggers and constraints are enabled/disabled?
    ... I can disable and enable triggers and constraints with sql-statements shown ... ALTER TABLE mytable NOCHECK CONSTRAINT ALL ... How can I check if triggers and constraints are enabled or disabled? ...
    (microsoft.public.sqlserver.programming)
  • Re: TSQL - Alter table to add a clustered prime key index
    ... You can not have two constraints in a single definition (Check and Primary ... Wayne Snyder, MCDBA, SQL Server MVP ... > ALTER TABLE USERS WITH NOCHECK ADD CONSTRAINT PK_USERS CHECK USERCODE ...
    (microsoft.public.sqlserver.programming)
  • Re: add FK constraint with nocheck
    ... Nocheck will ignore what is existing in your tables, ... "Norman" wrote in message ... > And then when deletions are done I have to set back. ... I want to add FK constraints with nocheck so that SQL ...
    (microsoft.public.sqlserver.mseq)