Delete query taking long time to execute

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.
Please comment if you have any idea about it. Any help would be highly
appreciated.
Thanks!
Regards,
~Somesh
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
Thanks
Hari
"Somesh" <Somesh@.discussions.microsoft.com> wrote in message
news:FD6E5B4A-34AF-4CF9-948D-6EA21944587C@.microsoft.com...
> 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.
> Please comment if you have any idea about it. Any help would be highly
> appreciated.
> Thanks!
> Regards,
> ~Somesh
>
|||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@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:55ccm2dgsfvcdco0iogo0oge8j9bvs953c@.4ax.com...
> On Thu, 23 Nov 2006 02:08:26 -0600, Hari Prasad wrote:
>
> 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