Multiple deletes with ExecuteNonQuery

[Brian Madden]

Hi,

I have a table that I'm deleting from (around 200,000
records). My delete statement is : Delete from
contractholder where chd_id in (1,2,3,.....) Chd_id is the
pk

I've pasted the code below that does the deletions.

Basically I'm deleting 1000 records at a time, but I'm
finding that when first run, the call to ExecuteNonQuery
takes around 20 seconds. After 30 iterations this time has
increased to 4 minutes or more!! Does anyone have any
ideas ???

Thanks!!
Brian


myEnumerator = oArr.GetEnumerator();
while( myEnumerator.MoveNext() )
{
if( nCount == 1000 )
{
oStr.Append( ")" );
SqlConnection oConn = new SqlConnection
("server=bmadden01;database=vdsa;uid=sa;pwd=");

oConn.Open();
SqlCommand oCmd = new SqlCommand( oStr.ToString(), oConn );
oCmd.CommandType = CommandType.Text;
oCmd.CommandTimeout = 0;
m_oEvt.WriteEntry( "Start Query" );
oCmd.ExecuteNonQuery();
m_oEvt.WriteEntry( "End Query" );

oConn.Close();
oCmd.Dispose();
oCmd = null;
oConn = null;
bAdd = false;
nCount = 0;
}
else
{
if( bAdd == false) {oStr.Append( "Delete from
contractholderstaging where cht_id in (" );}
if( bAdd == true ) {oStr.Append( ", " );}
oStr.Append( myEnumerator.Current.ToString() );
bAdd = true;
nCount++;
}
}

 

[David Browne]

Hi,

I have a table that I'm deleting from (around 200,000
records). My delete statement is : Delete from
contractholder where chd_id in (1,2,3,.....) Chd_id is the
pk

I've pasted the code below that does the deletions.

Basically I'm deleting 1000 records at a time, but I'm
finding that when first run, the call to ExecuteNonQuery
takes around 20 seconds. After 30 iterations this time has
increased to 4 minutes or more!! Does anyone have any
ideas ???

Yeah this is fundamentally inefficient and expensive.

As a quick fix, you should wrap all the deletes in one big transaction.
This will push off alot of reoginazation io stuff until the end.

As a slightly less quick fix, insert al of the id's you want to delete into
a temp table and then issue a "delete from contracholder where chd_id in
(select id from #deleteList)"

If contracholder can be truncated (ie has no relationships), then you might
get better performance by creating a temp table with the same structure as
contractholder and then

begin transaction
insert into #contractholder select * from contractholder;
truncate table contractholder;
insert into contractholder
select * from #contractholder where chd_id not in (select id from
#deleteList)"
commit transaction


David