deleting records takes forever

hi there.
I've got a bit of a problem on a clustered sql server. I don't know
if the cluster has anything to do with it, this is an entirely new
configuration for our company. We've moved out of the days of using
"white-box" pcs for servers to two dl380s clustered with an msa 1000
via fibre.
the servers are win2000 ent
mssql 2000 ent sp 3 (iirc)
with the advent of all this great technology, the big-brains
determined an excellent way to recoup costs of such an expense is to
release our dba. nice.
anyway. I've never run into this so I'm posting to this group, musing
whether this is due to clustering, table indexes or something else.
The cluster appears to be set up fine and we (small-brains) even
decided it was preduent to invest the money in calling MS for support.
Here'e the heart of the issue...
When deleting records from tables with indexes (mostly), by the index,
it takes absolutely forever. It took 8.5 hours to delete 1300 rows.
These are simple indexes, one field each, two indexes per table.
I say mostly because of the following:
deleting by the index, long time
deleting not by the index, fine execution
some tables, not deleting by the index, long time
example, contract table has two indexes on enteredby and contractid
(there are other objects, sps, views, triggers, constraints, etc.)
delete * from contract where enteredby = 5 and contractid < 1000
extremely long time to execute
delete * from contract where enteredby = 5
and
delete from contract where contractid < 1000
extremely long time
delete from contract where program > 1
fine execution
in another table - reports - deleting anything from that table results
in a long execution time whether i'm using indexes or not.
Now, when I select into another table (iirc just copies the data) and
perform any of the above (even on the reports table) execution is
perfect. Deletes several hundred rows <1 second.
Can anyone give me some guidance here?
thanks
>delete * from contract where enteredby = 5 and contractid < 1000
yeah, I know i messed that up

> decided it was preduent to invest the money in calling MS for support.
that too if anyone else noticed
|||Thanks for the suggestions.
In the event this ever gets indexed, it turned out that there were
constraints on the Comments table that referenced the Contract table.

Deletes in an updategram

I think this a bit of a long shot as it doesn't seem (according to the
documentation and just trying it out) possible but I thought I'd double
check. I've got an update happening which also includes rows on a
different table which might have been removed during the transaction
I'd therefore like to delete the rows or remove them from the
relationship (though deleting would be better).
I have something like:
<?xml version="1.0" encoding="UTF-8"?>
<ROOT>
<updg:sync xmlns:updg="urn:schemas-microsoft-com:xml-updategram"
mapping-schema="schema/person.xsd">
<updg:before>
<y0:Person xmlns:y0="pretend:URN"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:sql="urn:schemas-microsoft-com:xml-sql"
id="65654D01-35A9-4901-91DB-5618037ACE2C">
<y0:Address id="EC3831B2-83C4-46FB-A44F-57668E7ADD47"/>
<y0:Identifiers>
<y0:Identifier personID="65654D01-35A9-4901-91DB-5618037ACE2C"
id="5BD49D1F-7521-4247-9251-C195D4BEC079"/>
<y0:Identifier personID="65654D01-35A9-4901-91DB-5618037ACE2C"
id="FFC04502-1D16-4A2A-8939-F02ACFEE134E"/>
<y0:Identifier personID="65654D01-35A9-4901-91DB-5618037ACE2C"
id="868B29EF-B749-4903-B34B-FE39E4FE3D5D"/>
</y0:Identifiers>
</y0:Person>
</updg:before>
<updg:after>
<y0:Person xmlns:y0="pretend:URN"
id="65654D01-35A9-4901-91DB-5618037ACE2C">
<y0:DateOfBirth>2001-03-12</y0:DateOfBirth>
<y0:Sex>Male</y0:Sex>
<y0:Address id="EC3831B2-83C4-46FB-A44F-57668E7ADD47">
</y0:Address>
<y0:Identifiers>
<y0:Identifier id="5BD49D1F-7521-4247-9251-C195D4BEC079"
personID="65654D01-35A9-4901-91DB-5618037ACE2C">
<y0:Value>1203120135</y0:Value>
<y0:Type>Bleh</y0:Type>
</y0:Identifier>
<y0:Identifier id="FFC04502-1D16-4A2A-8939-F02ACFEE134E"
personID="65654D01-35A9-4901-91DB-5618037ACE2C">
<y0:Value>123456A</y0:Value>
<y0:Type>Foo</y0:Type>
</y0:Identifier>
</y0:Identifiers>
</y0:Patient>
</updg:after>
</updg:sync>
</ROOT>
Now what I was hoping was the three identifiers at the top would result
in the deleted identifier being removed from the database. However
instead I get a "All updategram/diffgrams nodes with siblings must have
ids, either user specified ones or mapping schema based key field id"
which I assume is as a result of an ambiguous delete operation.
It would be of great help if someone either shot this down as something
you can't do though better if there is a way to do it without trying to
work out everything that has changed and do multiple sync operations.
Thanks,
Gary
Never mind!
I was being silly. I forgot to upload one of the changed schema that
had the key-fields defined and that was what was causing the problems.
After fixing that everything worked just fine.
Thanks anyway,
Gary

Deletes in an updategram

I think this a bit of a long shot as it doesn't seem (according to the
documentation and just trying it out) possible but I thought I'd double
check. I've got an update happening which also includes rows on a
different table which might have been removed during the transaction
I'd therefore like to delete the rows or remove them from the
relationship (though deleting would be better).
I have something like:
<?xml version="1.0" encoding="UTF-8"?>
<ROOT>
<updg:sync xmlns:updg="urn:schemas-microsoft-com:xml-updategram"
mapping-schema="schema/person.xsd">
<updg:before>
<y0:Person xmlns:y0="pretend:URN"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:sql="urn:schemas-microsoft-com:xml-sql"
id="65654D01-35A9-4901-91DB-5618037ACE2C">
<y0:Address id="EC3831B2-83C4-46FB-A44F-57668E7ADD47"/>
<y0:Identifiers>
<y0:Identifier personID="65654D01-35A9-4901-91DB-5618037ACE2C"
id="5BD49D1F-7521-4247-9251-C195D4BEC079"/>
<y0:Identifier personID="65654D01-35A9-4901-91DB-5618037ACE2C"
id="FFC04502-1D16-4A2A-8939-F02ACFEE134E"/>
<y0:Identifier personID="65654D01-35A9-4901-91DB-5618037ACE2C"
id="868B29EF-B749-4903-B34B-FE39E4FE3D5D"/>
</y0:Identifiers>
</y0:Person>
</updg:before>
<updg:after>
<y0:Person xmlns:y0="pretend:URN"
id="65654D01-35A9-4901-91DB-5618037ACE2C">
<y0:DateOfBirth>2001-03-12</y0:DateOfBirth>
<y0:Sex>Male</y0:Sex>
<y0:Address id="EC3831B2-83C4-46FB-A44F-57668E7ADD47">
</y0:Address>
<y0:Identifiers>
<y0:Identifier id="5BD49D1F-7521-4247-9251-C195D4BEC079"
personID="65654D01-35A9-4901-91DB-5618037ACE2C">
<y0:Value>1203120135</y0:Value>
<y0:Type>Bleh</y0:Type>
</y0:Identifier>
<y0:Identifier id="FFC04502-1D16-4A2A-8939-F02ACFEE134E"
personID="65654D01-35A9-4901-91DB-5618037ACE2C">
<y0:Value>123456A</y0:Value>
<y0:Type>Foo</y0:Type>
</y0:Identifier>
</y0:Identifiers>
</y0:Patient>
</updg:after>
</updg:sync>
</ROOT>
Now what I was hoping was the three identifiers at the top would result
in the deleted identifier being removed from the database. However
instead I get a "All updategram/diffgrams nodes with siblings must have
ids, either user specified ones or mapping schema based key field id"
which I assume is as a result of an ambiguous delete operation.
It would be of great help if someone either shot this down as something
you can't do though better if there is a way to do it without trying to
work out everything that has changed and do multiple sync operations.
Thanks,
GaryNever mind!
I was being silly. I forgot to upload one of the changed schema that
had the key-fields defined and that was what was causing the problems.
After fixing that everything worked just fine.
Thanks anyway,
Gary