Re: Delete query taking long time to execute

On Wed, 22 Nov 2006 04:30:01 -0800, Somesh wrote:

hi,
I have a table which is a Master to other 12 tables and it is Child for
other 2 tables.
Therefore, It has 14 relationships with other tables. The size of table is
very small. (approx 30-40 rows). It also has a clustered index in its primary
key.

The problem I am facing here is that when I try to delete a row from the
table using its primary key in where clause, it takes more than 30 seconds to
execute it. Any subsequent attempt will be of a shorter duration (approx
20-25 sec.) but still it is very time taking.

I can't understand what could be the problem here when the table size is
very small. Is it because of constraints or any other sort.

Hi Somesh,

Indeed. The size of this table is not the problem. The number of tables
referencing this table (12), the size of the referencing tables, and the
indexing of those tables is.

For the DELETE to succeed, each of the 12 "Child" tables has to be
checked since the DELETE may not leave any orphins. The FOREIGN KEY
constraints in those tables do not automatically create an index, so if
you have not done so, this check requires a table scan.

--
Hugo Kornelis, SQL Server MVP
.

Relevant Pages

  • Re: temporal data constraints
    ... That seems like a great solution, but the problem is constraints. ... A child record can be created only if its parent record already ... A child lifespan must be contained within the parent lifetime ...
    (comp.databases.theory)
  • Re: Storing codes vs. human-intelligible values - best practice?
    ... The child tables referencing these domain tables use the COL_NM human ... -- Can't use check constraints (and wouldn't want to; ... On the other hand if three or more table will reference this column ... you definitely want to use a lookup table. ...
    (comp.databases.oracle.server)
  • Re: How to deal with cascading changes?
    ... I have two tables: Parent and Child. ... This primary key is also part of the primary key of table Child. ... In the database the primary key will cascade to table Child. ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Display mutliple 1-1 tables in DataGrid?
    ... I do not have multiple children and the data is text so there is ... no aggregate function that I can use in referencing the child item. ... I cannot find any way to make the DataGrid display data from both ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Subform Wizard and number of primary key fields
    ... I have created two forms- one for the parent and one for the child table. ... I want to import the child table's form as a subform on the parent table's form. ... Using the Subform Wizard, everything goes well until it gets to the point where I need to specify the linking fields. ... My problem is that Access allows only up to 3 fields for linking a subform to a parent form, and that would force my having to enter manually the other 2 fields in the primary key from the parent into the child. ...
    (comp.databases.ms-access)