Re: Delete query taking long time to execute

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: How to disable foreign key constraints in a stored procedure?
    ... constraints, perform some actions, and then re-enable them. ... My suggestion would be to re-think your 'reasoning' for disabling the ... Suppose you want to bulk load data into database - the data is coming ... Could you guarantee no other connected user ...
    (comp.databases.oracle.server)
  • Re: split records
    ... A UNION query is constructed by writing a set of SELECT queries, ... The words UNION or UNION ALL are then placed between the SELECT ... be duplicates, or when you want to preserve duplications, use ALL. ... constraints). ...
    (microsoft.public.access.queries)
  • Re: subquery problem, wrong reference in the subquery?
    ... The query still runs and give out results. ... constraints, datatype, etc. – It has an independent existence. ... multiple record templates defined on the same data. ...
    (microsoft.public.sqlserver.programming)
  • Re: asp.net report DbSource
    ... DataSet with DataRelations that provide foreign-key constraints? ... being used in the report. ... the base query is used by SQL'05 reporting ... DataColumn is causing the trouble and fix the schema. ...
    (microsoft.public.dotnet.general)
  • Re: How do you set a value to Null in a DataSet when its normally set to another table through a rel
    ... If it doesn't reference the other table at all it's an Orphan which is what ... You can try disabling the constraints but I ...
    (microsoft.public.dotnet.languages.vb)