Primary Key / Referntial Integrity Question

Hi,

I am supporting an application that was converted from ACCESS to SQL
Server 2000. My question focuses on two particuliar tables. The
parent table has 14000 rows while child table has over 9 million rows.
Referential integrity is set up between the two tables. The parent
table has a composite primary key of:
CustomerId (int)
LocationId (int)
ProductId (int)

The child table has a composite primary key of:
CustomerId (int)
LocationId (int)
ProductId (int)
InvTime (datetime)

A constraint between the parent and child table consists of:
CustomerId
LocationId
TankId

I have been asked to write an application that will allow users to
update the Customerid and LocationId columns of the parent table. My
program works fine when you update just one parent record but if you
try and update 30 parent records I get a timeout error. I believe the
bottleneck is the constraint between the parent and child tables.
Outside of re-designing the tables (NOTE: I was not the original
DBA/Developer), does anyone have any suggestions I can do to improve
performance for my app?

ThanksRodney King () writes:
> I am supporting an application that was converted from ACCESS to SQL
> Server 2000. My question focuses on two particuliar tables. The
> parent table has 14000 rows while child table has over 9 million rows.
> Referential integrity is set up between the two tables. The parent
> table has a composite primary key of:
> CustomerId (int)
> LocationId (int)
> ProductId (int)
> The child table has a composite primary key of:
> CustomerId (int)
> LocationId (int)
> ProductId (int)
> InvTime (datetime)
> A constraint between the parent and child table consists of:
> CustomerId
> LocationId
> TankId
> I have been asked to write an application that will allow users to
> update the Customerid and LocationId columns of the parent table. My
> program works fine when you update just one parent record but if you
> try and update 30 parent records I get a timeout error. I believe the
> bottleneck is the constraint between the parent and child tables.
> Outside of re-designing the tables (NOTE: I was not the original
> DBA/Developer), does anyone have any suggestions I can do to improve
> performance for my app?

I assume that the referential integrity is set up with ON UPDATE CASCADE?

First all, are there any triggers on the tables? In such case, what
do they do?

Next, what are the indexes on the tables? Obviosly the PKs are indexes,
but are the clustered or non-clustered?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for responding.
1) To answer your questions I have full Delete/Update cascade on my
constraint.

2) There are no DB triggers and here are the indexes on my tables:

3) Indexes are as follows:
Master table:
Primary Key, Unique, Nonclustered index on:
CustomerId,LocationId,ProductId

Child table:
Nonclustered index on:
CustomerId,LocationId,ProductId

Nonclustered index on:
MinNumber

Primary Key, Unique, Nonclustered index on:
ProductId,InvTime

When I run the following query in SQL Query Analyzer:

Update MasterTable set Customerid=23,LocationId=34 where CustomerId=44
and LocationId=788 and ProductId=91022

My Explain plan tells me that a full table scan would be performed
against my child table. However, if I perform the same statement
against my Child table, the Explain plan uses my Nonclustered index of
CustomerId,LocationId and ProductId

Can you tell me what's going on?

Thanks
On Wed, 8 Dec 2004 20:52:38 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Rodney King () writes:
>> I am supporting an application that was converted from ACCESS to SQL
>> Server 2000. My question focuses on two particuliar tables. The
>> parent table has 14000 rows while child table has over 9 million rows.
>> Referential integrity is set up between the two tables. The parent
>> table has a composite primary key of:
>> CustomerId (int)
>> LocationId (int)
>> ProductId (int)
>>
>> The child table has a composite primary key of:
>> CustomerId (int)
>> LocationId (int)
>> ProductId (int)
>> InvTime (datetime)
>>
>> A constraint between the parent and child table consists of:
>> CustomerId
>> LocationId
>> TankId
>>
>> I have been asked to write an application that will allow users to
>> update the Customerid and LocationId columns of the parent table. My
>> program works fine when you update just one parent record but if you
>> try and update 30 parent records I get a timeout error. I believe the
>> bottleneck is the constraint between the parent and child tables.
>> Outside of re-designing the tables (NOTE: I was not the original
>> DBA/Developer), does anyone have any suggestions I can do to improve
>> performance for my app?
>I assume that the referential integrity is set up with ON UPDATE CASCADE?
>First all, are there any triggers on the tables? In such case, what
>do they do?
>Next, what are the indexes on the tables? Obviosly the PKs are indexes,
>but are the clustered or non-clustered?|||Rodney King () writes:
> Thanks for responding.
> 1) To answer your questions I have full Delete/Update cascade on my
> constraint.
> 2) There are no DB triggers and here are the indexes on my tables:
> 3) Indexes are as follows:
> Master table:
> Primary Key, Unique, Nonclustered index on:
> CustomerId,LocationId,ProductId
> Child table:
> Nonclustered index on:
> CustomerId,LocationId,ProductId
> Nonclustered index on:
> MinNumber
> Primary Key, Unique, Nonclustered index on:
> ProductId,InvTime

Do I read this right that there are no clustered indexes? While an update
to the clustered index would be more costly, locating the rows will be
faster.

The problem now is that the optimizer estimates that using the non-clustered
index on the table will be more expensive than a table scan. This is not
that strange as it sounds. If you need to access 50% of the rows in a
table, this is lot more expensive to do through a non-clustered index
than a table scan. (Because you will need to access many pages more
than once.) The optimizer therefor tends to be somewhat conservative
in its use of nonclustered indexes.

If you don't want to change the index to a clustered index, an UPDATE
STATISTICS WITH FULLSCAN on the tables may help, but I would not really
count on it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp