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


Top 5 Contributors of the Month
david stephan
Asad Ali
Post New Web Links

Index to speed up DELETE

Posted By:      Posted Date: September 28, 2010    Points: 0   Category :Sql Server
 

I promise I have searched the forums and tried to figure this out on my own! I have tried adding indexes. I need some expert advice!

I am trying to delete about 8 million rows. The TABLE is 9 million rows. I am using a cursor because I don't want to lock the user's out of the system.

TABLE (F1 (PK,FK), F2 (PK,FK), F3 (PK,FK), F4 (PK,FK), F5 (FK), createdate, createby, moddate, modby)

Clustered primary key with the first 4 fields; FK for field 5; Index on createdate and moddate.

My cursor:

declare curDeleteSystemID insensitive cursor for

select F1, F2, F3, F4 from TABLE where ID <> '1'

open curDeleteSystemID

fetch next from curDeleteSystemID into @F1, @F2, @F3, @F4

WHILE( @@fetch_status <> -1 )
BEGIN
             DELETE FROM TABLE
             WHERE F1 = @F1 AND F2 = @F2 AND F3 = @F3 AND F3 = @F4

             fetch next from curDeleteSystemID into @F1, @F2, @F3, @F4

END

close curDeleteSystemID
deallocate curDeleteSystemID

This is taking a minute for 20,000 rows. It is going to take a long t


View Complete Post


More Related Resource Links

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.

update/delete not working on server only

  

my aspx page works in VWD, and everything works on the server EXCEPT update and delete sql functions. any ideas?

thanks 


How to use TOP in DELETE

  

Hi,

I have a large database in MSSQL 2005 (rows around 3,788,299 : size 4GB)

Two questions

1. SQL query ~ select top 100 * from [tablename], how do capture the next 100 record?

2. SQL query ~ delete top 100 from [tablename] --- MSSQL show me syntax error? How can I only delete the top 100 record?

 

thk.


delete record from excel sheet

  



Hello,

           I have write the code for deleting the data in excel sheet. but we have got the error" Deleting data in a linked table is not supported by this ISAM."

     below I paste some code snippest.

string strDelete = "Delete  from [" + strSheetName + "$]";

            cmdExcel.Connection = con;
            cmdExcel.CommandType = CommandType.Text;
            cmdExcel.CommandText = strDelete;

            cmdExcel.ExecuteNonQuery();


Please can anybody this answer.It is urgent


delete data in excel sheet

  

Hello,

           I have write the code for deleting the data in excel sheet. but we have got the error" Deleting data in a linked table is not supported by this ISAM."

     below I paste some code snippest.

string strDelete = "Delete  from [" + strSheetName + "$]";

            cmdExcel.Connection = con;
            cmdExcel.CommandType = CommandType.Text;
            cmdExcel.CommandText = strDelete;

            cmdExcel.ExecuteNonQuery();


Please can anybody this answer.It is urgent




Speed SQL: Tuning Your Database Calls with Tier Interaction Profiling

  

Tier interaction profiling (TIP) is a feature of the Visual Studio profiling tools that measures the duration of synchronous calls to ADO.NET-compliant data stores. We'll show you how TIP works and provide some best practices for using TIP to diagnose performance problems in your apps.

Mark Friedman

MSDN Magazine August 2010


Built For Speed: Develop Turbocharged Apps For Windows Compute Cluster Server

  

This article explores the services provided by Compute Cluster Server 2003 and the tools provided by Visual Studio 2005 that will help you develop High-Perfomance Computing applications.

Rich Ciapala

MSDN Magazine April 2006


Speed: NGen Revs Up Your Performance with Powerful New Features

  

In the .NET Framework 2.0, NGen has been greatly improved and offers a number of compelling new features to make it easier and faster to deploy high-performance managed applications. This article introduces some of these new features and explains how and when you should use NGen to improve the performance of your apps.

Reid Wilkes

MSDN Magazine April 2005


ASP.NET 2.0: Speed Up Your Site with the Improved View State in ASP.NET 2.0

  

View state is a wonderful thing. It allows the ASP.NET developer to maintain state for server-side controls that are not form elements.Used judiciously, it can improve the user experience. But in the wrong hands, it can cause your pages to grind to a halt. The release of ASP.NET 2.0 will include a variety of improvements to view state that will make it easier to use and less likely to slow performance.

Fritz Onion

MSDN Magazine October 2004


Speed Up Visual Studio 2008

  
The article Speed Up Visual Studio 2008 was added by tmorton on Monday, August 09, 2010.

1- Disable toolbox populate from the mainmenu open Tools->Options Select the general tab under Windows Forms Designer Set AutoToolBoxPopulate to false. Although the setting is under Windows Forms Designer, it is just as effective for ASP.Net developers

Add, Edit, update, Delete gridview

  
The article Add, Edit, update, Delete gridview was added by anjankant on Friday, July 09, 2010.

Thousandas operation we can do on Gridview control. Here I as explaining how we can add new recored and edit of existing record and delete a record in Gridview. Step1 : You can add this gridview in your aspx form. Here some events of Gridview are fired

Sql Scripts - Delete all Tables, Procedures, Views and Functions

  

In a shared environment you typically don't have access to delete your database, and recreate it for fresh installs of your product. 

I managed to find these scripts which should help you clean out your database.

Use at your own risk.

 

Delete All Tables

--Delete All Keys

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
OPEN @Cursor FETCH

Delete not working on List.aspx - out of box

  

Hello,


I'am trying to use Dynamic data web site on my project. I have Visual Studio 2010 enf Framework 4.0. I create the default project with the wizard. I add my own Entity Data Model.

When I try to use the "delete" autogenerated button on List.aspx, on the table ServerSetting (see image below), I receive a null exception.  I have no custom code involved in this operation and cannot seem to determine what is Null.

Any help would be appreciated!



Thanks,




Ben

[NullReferenceException: Object reference not set to an instance of an object.]
   System.Data.Objects.Internal.ForeignKeyFactory.CreateConceptualNullKey(EntityKey originalKey) +30
   System.Data.Objects.DataClasses.EntityReference.NullAllForeignKeys() +722
   System.Data.Objects.EntityEntry.NullAllForeignKeys() +235
   System.Data.Objects.EntityEntry.Delete(Boolean doFixup) +77
   System.Data.Objects.EntityEntry.Delete() +9
   System.Data.Objects.ObjectContext.DeleteObject(Object entity, EntitySet expectedEntitySet) +169
   System.Data.Objects.ObjectContext.DeleteObject

Delete spaces created by combining 2 fields

  

Howdy, this is kind of a continuation of an answer I got from this thread:  http://forums.asp.net/t/1592766.aspx

My users will enter a city/station where an incident occured, and the date.  The trigger below does work, but it will occasionally put spaces between the citystation and the date.  I think this happens when the citystaion name is not very long, but not sure.

(ex: city____08242010)

How can I make sure that if the incidentid is going to have spaces, that sql deletes those spaces?  I need the id to be space free.

Thanks!

===============================

ALTER

AS

TRIGGER [dbo].[tr_AddID1] ON [dbo].[tblIncidents] INSTEAD

how to create index for a column

  

Hi Friends,

                       I need to create the index for a column and accessing that in sqlserver 2005. Please any one help me.



Thanks..,


Categories: 
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