Cascading Delete Problem

I have three tables that equate to a customer - order - order items
scenario.
The Customer table has 100,000 rows
The Order table has 3 million rows
The Order items has 30 million rows.
I have the primary and foreign keys all defind and the appropriate indexes.
I have cascading deletes on the foreign keys between all three tables.
It takes over a minute to delete a customer. If I remove the cacading
delete, and delete from the order items then orders then customers it takes
seconds.
Why does the cascading deletes take so long?
Using SQL2000 sp3a
Thanks for any insight!
On Thu, 03 Feb 2005 19:35:25 GMT, Howard Carr wrote:

>I have three tables that equate to a customer - order - order items
>scenario.
>The Customer table has 100,000 rows
>The Order table has 3 million rows
>The Order items has 30 million rows.
>I have the primary and foreign keys all defind and the appropriate indexes.
>I have cascading deletes on the foreign keys between all three tables.
>It takes over a minute to delete a customer. If I remove the cacading
>delete, and delete from the order items then orders then customers it takes
>seconds.
>Why does the cascading deletes take so long?
Hi Howard,
Is your primary key (or unique) constraint for the OrderItems table
defined as (OrderID, CustomerID)?
Try changing it to (CustomerID, OrderID). It will speed up the deletion of
a customer, but it will slow down the deletion of an order. If both
deletion happens frequently enough to need good performance, you might try
adding an extra index, so that both orders are present.
Note that changing indexes might affect performance of all other queries
as well.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Cascading Delete Problem

I have three tables that equate to a customer - order - order items
scenario.
The Customer table has 100,000 rows
The Order table has 3 million rows
The Order items has 30 million rows.
I have the primary and foreign keys all defind and the appropriate indexes.
I have cascading deletes on the foreign keys between all three tables.
It takes over a minute to delete a customer. If I remove the cacading
delete, and delete from the order items then orders then customers it takes
seconds.
Why does the cascading deletes take so long?
Using SQL2000 sp3a
Thanks for any insight!On Thu, 03 Feb 2005 19:35:25 GMT, Howard Carr wrote:

>I have three tables that equate to a customer - order - order items
>scenario.
>The Customer table has 100,000 rows
>The Order table has 3 million rows
>The Order items has 30 million rows.
>I have the primary and foreign keys all defind and the appropriate indexes.
>I have cascading deletes on the foreign keys between all three tables.
>It takes over a minute to delete a customer. If I remove the cacading
>delete, and delete from the order items then orders then customers it takes
>seconds.
>Why does the cascading deletes take so long?
Hi Howard,
Is your primary key (or unique) constraint for the OrderItems table
defined as (OrderID, CustomerID)?
Try changing it to (CustomerID, OrderID). It will speed up the deletion of
a customer, but it will slow down the deletion of an order. If both
deletion happens frequently enough to need good performance, you might try
adding an extra index, so that both orders are present.
Note that changing indexes might affect performance of all other queries
as well.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Cascading Delete Problem

I have three tables that equate to a customer - order - order items
scenario.
The Customer table has 100,000 rows
The Order table has 3 million rows
The Order items has 30 million rows.
I have the primary and foreign keys all defind and the appropriate indexes.
I have cascading deletes on the foreign keys between all three tables.
It takes over a minute to delete a customer. If I remove the cacading
delete, and delete from the order items then orders then customers it takes
seconds.
Why does the cascading deletes take so long?
Using SQL2000 sp3a
Thanks for any insight!On Thu, 03 Feb 2005 19:35:25 GMT, Howard Carr wrote:
>I have three tables that equate to a customer - order - order items
>scenario.
>The Customer table has 100,000 rows
>The Order table has 3 million rows
>The Order items has 30 million rows.
>I have the primary and foreign keys all defind and the appropriate indexes.
>I have cascading deletes on the foreign keys between all three tables.
>It takes over a minute to delete a customer. If I remove the cacading
>delete, and delete from the order items then orders then customers it takes
>seconds.
>Why does the cascading deletes take so long?
Hi Howard,
Is your primary key (or unique) constraint for the OrderItems table
defined as (OrderID, CustomerID)?
Try changing it to (CustomerID, OrderID). It will speed up the deletion of
a customer, but it will slow down the deletion of an order. If both
deletion happens frequently enough to need good performance, you might try
adding an extra index, so that both orders are present.
Note that changing indexes might affect performance of all other queries
as well.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Capabilities of Developer Edition

Hi there,

Forgive me if this has been asked & answered a million times, but I'm trying to get a handle on whether, if I purchased a copy of SQL Server 2000 Developer Edition, it would meet my needs or not.

A long-standing client of mine just called me to ask if I could help her out on a database project. I would be working offsite, so I'd need to pony up the cash to buy software for my own computer, basically.

I keep reading that the DE has "limited functionality" but I can't seem to find details on what those limitations are. Is it the sort of deal where it can do everything I need for development (using Enterprise Manager/Query Analyzer to connect to a db either across the internet or maybe on VPN, and doing your usual EM/QA things from there) but I couldn't run it as a server? Or is there actual functionality that's missing?

If there's just a link I've overlooked that answers my question, I'd appreciate someone pointing it out. Thanks!Here's a good place to see the differences: http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/reskit/sql2000/part2/c0361.asp.|||That page is perfect! Thanks for pointing it out.

Next silly question: why does compusa.com and some other online outlets list SQL Server Developer Edition for forty-something dollars when most offer it for four hundred-something dollars? Would paste the URL to the Epinions search result, but the forum code is sticking a break tag in the middle of it, which breaks the link.

That can't be right, can it? I suspect not, as all the places I found at Epinions listing the low price are all out of stock.

Thanks.|||Should Google before posting. I just answered my own question. Apparently they've cut the price:

http://www.entmag.com/news/article.asp?EditorialsID=5895

So now the question becomes: why do only 4 of 21 stores Epinion has prices for have the new low price? Am I to pay five hundred bucks if I happen to need it now? ... purely rhetorical question, that.|||Can you believe it? Even shop.microsoft.com has it on backorder. I called their customer service line to ask about it, and after the rep tried to convince me that there was no such thing as a SQL Server 2000 Developer Edition, he was finally able to give me an estimated ship date of September 8.

sigh.