.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Post New Web Links

Delete statement running slow

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :Sql Server

Dear Friends

I've got a delete statement which is taking 40 secs to run. There's only a row to delete but it's taking 40 secs . The table has got foreign keys which references other tables. I have checked indexes exist on these foreign key columns.

I have already updated statistics with fullscan on the this table.


Any ideas

Many thanks.


View Complete Post

More Related Resource Links

The DELETE statement conflicted with the REFERENCE constraint


I have created ASP.Net Dynamic data web site using scaffolding in Visual Studio 2010. When I click on delete in data grid for customer table I got below error message.

The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occured in database "../NORTHWIND.MDF", table "dbo.order details", column 'ProductId'

The statement has been terminated


What is the standard practice to make sure one slow-running page doesn't affect other pages?

Page A loads very fast; Page B loads very slowly and does some CPU-intensive things on the web server.I noticed that if someone is loading Page B, then Page A also loads slowly - for ALL users. What is the standard practice for making sure this doesn't happen?  If multiple users are loading Page B at the same time then Page A is ridiculously slow.Is there an IIS setting, web.config setting, or hardware configuration I could use to make sure that the fast-loading pages aren't bogged down by other pages that need more time to load? 

Is this a bug in MERGE statement with DELETE/INSERT?

This looks to me like a bug with MERGE statement. This does not work on SQL 2008 and 2008 R2. Note that the first commneted statement works properly but the second does not. Is there any explanation why not, other than a bug? Duplication script below. use tempdb go /* IF object_id('dbo.Test', 'U') IS NOT NULL DROP TABLE dbo.Test go IF object_id('dbo.Src', 'U') IS NOT NULL DROP TABLE dbo.Src go */ IF object_id('dbo.Test', 'U') IS NULL BEGIN     CREATE TABLE dbo.Test     (         intID int NOT NULL IDENTITY PRIMARY KEY         ,sysID int NOT NULL         ,ioID int NOT NULL         ,Code nvarchar(10) NOT NULL     )     INSERT dbo.test (sysID, ioID, Code) VALUES (1, 1, 'A')       CREATE UNIQUE NONCLUSTERED INDEX [UIXF_Test] ON [dbo].[Test]     (           [sysID] ASC,           [ioID] ASC     )     WHERE ([ioID] IN ((1)))     WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

MDX running very slow

Hello guys....need some help on this. Please review the beolw MDX which is taking more than 30 mins to execute...any idea what is going on here? SELECT NON EMPTY { [Measures].[Budget Amt] } ON COLUMNS, NON EMPTY { ([Account].[Yardi Account Code]. [Yardi Account Code].ALLMEMBERS * [Account].[Account].[Account].ALLMEMBERS * [Date].[Month].[Month].ALLMEMBERS * [Date].[Date].[Date].ALLMEMBERS  * DESCENDANTS([Account Hierarchy].[Account Hierarchy].[Level 02].ALLMEMBERS) ) }  DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, LEVEL_NUMBER ON ROWS FROM  [Finance] WHERE (STRTOSET("{[Account Element Hierarchy].[Account Element Hierarchy].&[1214|425|406|495|1209|421]}"), STRTOMEMBER("[Date].[Year].&[2010]"), STRTOMEMBER("[Budget Version].[Budget Version].&[Budget]"), [Account Hierarchy].[Account Hierarchy Type].&[2]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

ASYNC_NETWORK_IO Qyery running slow

I am running a Stored procedure which select from a table and returns approx 800000 records. When calling from any client machine it takes long time to return the result (90 sec). It waits for ASYNC_NETWORK_IO which is pushing the result to client. If select statement is used with TOP operator to return less number of records it executes faster. When calling from the server the stored proc returns data in 13 sec with all records. In another machine of identical HW and configuration this problem is not there. Can anyone help how to improve ASYNC_NETWORK_IO  issue? Environment SQL-2005 SP1 64 bit Standard  on Active/Passive clusterWindows -2003 Ent. Thanks-Ashis

CAST in delete query-Performance is so slow

Hi all, I've below join in the delete statementt, but it's taking so long the query run, the performance is really bad because of cast conversion, is there a better way, especially performace wise to fasten this below join, A.PHONE in nvarchar data type and DNC.PHONE_NUMBER  is in bigint data type.   A.PHONE=CAST(DNC.PHONE_NUMBER as varchar (15)) Thank you.

Why single SQL delete statement will cause deadlock?


Hello everyone,

I am using SQL Server 2008 Enterprise. I am wondering why even a single delete statement of this stored procedure will cause deadlock if executed by multiple threads at the same time? Any tools or sample codes which could be used to reproduce this scenario?

For the delete statement, Param1 is a column of table FooTable, Param1 is a foreign key of another table (refers to another primary key clustered index column of the other table). There is no index on Param1 itself for table FooTable. FooTable has another column which is used as clustered primary key, but not Param1 column.

    create PROCEDURE [dbo].[FooProc]   
     @Param1 int
     ,@Param2 int 
     ,@Param3 int 
    DELETE FooTable WHERE  Param1 = @Param1    
    INSERT INTO FooTable   

CLR Timeout when running an update statement


I have created several CLR'S for several webservices but i had no need to update my tables until now, most of my CLR'S return data to the cusstomer but now i have a CLR that receives information from a webservice and updates my data, When running a Select statement within the CLR everything is fine but when I send and update statement the CLR times out without good explanation. the code below is what Im using and that will reproduce the problem.

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    <System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert, Unrestricted:=True)> _

Public Shared Sub sp_Request_TransNetwork()

        Using conn As New SqlConnection("context connection=true")

                       ssql = "select fields from my table" --this works perrfect!!!


The DELETE statement conflicted with the REFERENCE constraint "FK_tblAdvanceLink_tblLink


I"m using table adapters here. I tried to use the following in my table adapter:

ALTER TABLE tblAdvanceLInk

DROP FOREIGN KEY FK_tblAdvanceLink_tblLink

DELETE FROM [tblAdvanceLink] WHERE WebsiteID=@WebsiteID 


The DELETE statement conflicted with the REFERENCE constraint "FK_tblAdvanceLink_tblLink". The conflict occurred in database "LinkExchanger", table "dbo.tblAdvanceLink", column 'LinkID'.
The statement has been terminated

I get the same problem every time. So I read you could do this in a sql query. Why doesn't this work in my tableadapter? Do I have this in the right place?  Do I also put this into the other table? (tblLInk)

Isn't there a way to just relax ALL constraints so I can delete some rows? I'm deleting rows from both tables which have the corresponding websiteID

The DELETE statement conflicted with the REFERENCE constraint. The conflict occurred in database.



I am getting the following error 

The DELETE statement conflicted with the REFERENCE constraint "FK_Person_Country_CountryID_CountryID".
The statement has been terminated.

I know what the problem is, but can anyone help me in catching this exception in the best possible way?

I do not want the user to delete the country if there are any FK contrains. Or a better way will be to ask the user if they want to delete the records referencing this "Country".

int countryID = Convert.ToInt32(gvCountry.DataKeys[e.RowIndex].Value);
        db.DeleteCountry(1, 1, countryID);
        gvCountry.EditIndex = -1;

**i am using linq to sql

Thank you.


Delete Statement with nested select in where clause deletes all entries in a table


I have fallen into a trap unadvertedly causing all records in a table being deleted. Can anybody tell me, if this a the intended behaviour of SQL Server 2008 R2 or does anybody know a method how to safely discover those kinds of typos in advance?

Regards, Markus

Here follows a script reproducing the behaviour:









Delete Statement Code for C# ASP.NET SQL


I'm having problems with creating detele functionality to my asp.net page.  I have found lots of bits of code indicating parts of how to do it, but not in its entirety.  Could someone display it in a more complete version so I could have something to compare and figure out what i am doing wrong?


Delete or Truncate statement for data purging



I am writting a stored procedure to purge data from table every month. There is no constraint on table column except primary key.

I am using 'delete' statement as it records an entry in the transaction log for each deleted row. But it's a slow process compared to 'Truncate' statement.

Can you please suggest what should I choose (Delete or truncate) as per best practice.








Record Count on MERGE Statement on Insert,Update,Delete


HI All,

How can I get the numbers of records affected in the Merge statement, INSERT,UPDATE,DELETE  separately and store it in a variable so I can get it in the application side? 



khrizz tell

Merge replication is running very slow


I'm using Web Synchronization for Merge Replication and recently the duration of the synchronizations have went from taking 10-20 seconds per sync to 3-6 hours per sync.  The volume of data per sync has not changed and I'm not receiving any merge agent errors.

Can someone suggest how should I troubleshoot from here?

Adding Dynamic Rows in ASP.NET GridView Control with TextBoxes and with Delete functionality

In my previous examples, I have demonstrated on how to add dynamic rows in GridView control with TextBoxes and how to save the values into the database. Now, seems that most of the developers are asking if how to add a delete functionality with it. So in this example, I'm going to show on how to delete a certain row in the dynamic GridView with TextBoxes.

SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete

Sometimes you end up with a return value of -1 when using the SqlClient.SqlCommand.ExecuteNonQuery method.

Why is that?

Well, the ExecuteNonQuery method is there for statements for changing data, ie. DELETE / UPDATE /INSERT, and the returned value are the number of rows affected by that statement.

When checking the documentation we can see that there are some conditions that return -1.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of

rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend