Hi Eric,

Did you try to execute DELETE statement directly (w/o sp_executesql)? Did you check execution plan for DELETE statement?

>
>We have a performance problem when we delete single rows
>of data in a cursor with SQL server 2000.
>
>We just migrated our application from SQL server 7 to SQL
>server 2000 and the same process take 20 minutes on our
>older system (sql7) and 1h34 on the new system (sql2000).
>
>The old system was a dual PIII with 4 go of ram and we
>migrated the application on a brand new dual Xeon with 4
>Go of ram (windows server 2003 os) and high speed Raid 5
>500 Go of HD.
>
>To exclude the new hardware setup from the week
>performance behavior we also tested the new application on
>2 other windows 2000 /SQl 2000 server and got the same
>result except even slower (10 hours and 20 hours)results
>because of hardware capabilities.
>
>Now what do we have to do differently on SQL server 2000
>to make it fast again??
>
>To help us out, we provide the following technical details:
>
>The process we have coded feed statistic data and we run
>this task every 20 minutes.
>
>We use a cursor that provides data from four different
>tables.
>
>When we execute the cursor in our store procedure without
>deleting information, the process takes 6 seconds for 12k
>rows.
>
>Tables we use :

>Table			Rows		Number of Index
>InvoiceEntete		500 336	                 2
>InvoiceDetail		14 496 307	         3
>StatsFournProduit      6 226 957	         2
>StatsFournProdDetail	7 010 503	         3

>We use a select cursor:

>DECLARE cur_id2 CURSOR READ_ONLY FOR
>     SELECT  Convert(int,InvoiceEntete.compagnie_ln),
>                       	InvoiceEntete.code_client,
>             		InvoiceDetail.produit_livre,
>			'00' as PackSeq,
>codepromoTmp,
>convert (int,DATEPART(year, InvoiceEntete.date_facture)),
>convert (int,DATEPART(month, InvoiceEntete.date_facture)),
>	                InvoiceEntete.date_facture,
>		       	InvoiceEntete.type_trans,
>	                InvoiceEntete.code_promo,
>	                InvoiceDetail.fournisseur_ln,
>	                InvoiceDetail.esc_promo,
>	                InvoiceEntete.statut,
>	                InvoiceDetail.qte_livree,
>	                InvoiceDetail.montant_net,
>	         FROM InvoiceEntete
>		inner join InvoiceDetail
>		   ON InvoiceEntete.compagnie_ln =
>InvoiceDetail.compagnie_ln
>		      and InvoiceEntete.order_id =
>InvoiceDetail.order_id
>	              and InvoiceEntete.order_suffixe =
>InvoiceDetail.order_suffixe
>	        where InvoiceEntete.compagnie_ln =
>@CompanyId
>	            and InvoiceDetail.order_id = @OrderNo
>	            and InvoiceDetail.order_suffixe =
>@OrderSuf
>
>Note:  Looking at the execution plan of this cursor, it
>use only Clustered Index Seek. (No table or index scan)
>
>Every time we loop in the cursor, we execute two SQL
>queries to delete one row of the StatsFournProduit and
>StatsFournProdDetail tables.
>
>SQL query we use
>
>For table StatsFournProduit
>
>SET @StrSql = N'DELETE FROM statsFournProduit
>		where companyid = @companyid
>		and code_client =  @code_client
>		and produit_ln =  @produit_livre
>		and PackSeq = @PackSeq
>		and annee =  @annee
>		and fournisseur_ln =  @fournisseur_ln '
>
>
>EXEC sp_executesql @StrSql, N'@code_client nvarchar(10),
>					
>	@produit_livre nvarchar(10),
>						@PackSeq
>nvarchar(2),
>						@annee
>int,
>						@CompanyId
>Int,
>					
>	@fournisseur_ln nvarchar(10)',
>					
>	@code_client,
>					
>	@produit_livre,
>						@PackSeq,
>						@annee,
>						@CompanyId,
>					
>	@fournisseur_ln
>
>And for table StatsFournProdDetail
>
>SET @StrSqlDetail = N'DELETE FROM StatsFournProdDetail
>			 where companyid = @companyid
>				and code_client =
>@code_client
>				and produit_ln =
>@produit_livre
>				and PackSeq = @PackSeq
>				and annee =  @annee
>				and code_promo =
>@codepromoTmp
>				and fournisseur_ln =
>@fournisseur_ln '
>
>		EXEC sp_executesql @StrSqlDetail,
>N'@code_client nvarchar(10),
>					
>	@produit_livre nvarchar(10),
>						@PackSeq
>nvarchar(2),
>						@annee
>int,
>						@CompanyId
>Int,
>					
>	@codepromoTmp nvarchar(10),
>					
>	@fournisseur_ln nvarchar(10)',
>					
>	@code_client,
>					
>	@produit_livre,
>						@PackSeq,
>						@annee,
>						@CompanyId,
>					
>	@codepromoTmp,
>					
>	@fournisseur_ln

>To delete 57 rows it takes 22 seconds. Our application
>need to perform 12 000 rows, the time to complete is 1h34
>minutes.
>
>Without the delete, it takes 6 second to perform the 12000
>rows.
>
>We already tried many work around like inserting cursor
>into a temp table and make a delete joint on the temp
>table, we also tryied deleting in batch of 1000 rows
>instead of 1 row at a time, we also tryied to delete with
>Rowlock parameter without any performance improvement.
>
>Can you help us, any ideas?
>
>Thank you in advance,
>
>Eric.