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


Top 5 Contributors of the Month
Sharon Maxwell
Post New Web Links

How to delete many records with minimal locks and IO?

Posted By:      Posted Date: September 15, 2010    Points: 0   Category :Sql Server
 
How to delete many records with minimal locks and IO? hi, I have a larg db (160GB) that used both as OLTP and DW. there are several large tables that normally experience 10k-30k transactions per hour. most of them are INSERT and part are UPDATE. Tables have cluster PK of 5-8 fields with datetime one of them. tables have several indexes. normally we dont delete records. BUT, end-user may quit service once in a while and in this case we have a job to delete all his raw data. we have a job that tries to do it in small chunks to reduce impact on other processing jobs, but actually it slows down all other important transactions with long locks. I would like to know what is the best way to delete records from a large table with minimum impact on other transactions (insert and update). The delete task is a background job that is executed during night and may take many hours. here is my code: ALTER @CompanyID     int, @itemID     nvarchar(50) = '-1' AS begin -- @itemID = -1 for all items declare     @d   declare     @upperDate   set     @upperDate = dbo.FN_DateOnly(getdate() + 1) set     @d = @upperDate - 800 while     @d <= @upperDate begin begin     tran DELETE       I_EVENTS with (PAGLOCK) WHERE     G_COMPANY_ID


View Complete Post


More Related Resource Links

How to delete many records with minimal locks and IO?

  
hi, I have a larg db (160GB) that used both as OLTP and DW. there are several large tables that normally experience 10k-30k transactions per hour. most of them are INSERT and part are UPDATE. Tables have cluster PK of 5-8 fields with datetime one of them. tables have several indexes. normally we dont delete records. BUT, end-user may quit service once in a while and in this case we have a job to delete all his raw data. we have a job that tries to do it in small chunks to reduce impact on other processing jobs, but actually it slows down all other important transactions with long locks. I would like to know what is the best way to delete records from a large table with minimum impact on other transactions (insert and update). The delete task is a background job that is executed during night and may take many hours. here is my code: ALTER     PROCEDURE SP_ADMIN_DEL_DATA     @CompanyID int,     @itemID nvarchar(50) = '-1' AS begin     -- @itemID = -1 for all items     declare @d datetime     declare @upperDate datetime       set @upperDate = dbo.FN_DateOnly(getdate() + 1)     set @d = @upperDate - 800     while @d <= @upperDate     begin     begin tran     DELETE I_EVENTS with (PAGLOC

How to delete many records with minimal locks and IO?

  
How to delete many records with minimal locks and IO? hi, I have a larg db (160GB) that used both as OLTP and DW. there are several large tables that normally experience 10k-30k transactions per hour. most of them are INSERT and part are UPDATE. Tables have cluster PK of 5-8 fields with datetime one of them. tables have several indexes. normally we dont delete records. BUT, end-user may quit service once in a while and in this case we have a job to delete all his raw data. we have a job that tries to do it in small chunks to reduce impact on other processing jobs, but actually it slows down all other important transactions with long locks. I would like to know what is the best way to delete records from a large table with minimum impact on other transactions (insert and update). The delete task is a background job that is executed during night and may take many hours. here is my code: ALTER @CompanyID     int, @itemID     nvarchar(50) = '-1' AS begin -- @itemID = -1 for all items declare     @d   declare     @upperDate   set     @upperDate = dbo.FN_DateOnly(getdate() + 1) set     @d = @upperDate - 800 while     @d <= @upperDate begin begin     tran DELETE       I_EVENTS with (PAGLOCK) WHERE     G_COMPANY_ID

Allow or deny roles to update, insert and delete records.

  
Hello, I have two roles "Admin" and "Basic". I also have a listview on the web page. My goal is that to make "Admin" role has the highest privilege to deal with records such as "insert", "update" and "delete". For the role "Basic", it only can update the records. Is there any sample code for that? Thanks.  

I have relation many to many - how can I delete records from intermediate table ?

  
I have tables: Books id title Authors id name BooksAuthors book_id author_id Entity Framework generated only models for tables Books and Authors in relation many to many. How can I delete from table BooksAuthors all records where book_id =5 ?

Delete Multiple Records

  
Hai friends,            How to delete multiple records using checkbox in gridview?

Is the delete records LINQ correct ?

  
I hope to delete all records in DBPrograms, is the following code correct?               DBContext db = DBContext(PublicDBPar.ConnectionString);            db.DBPrograms.DeleteAllOnSubmit(db.DBPrograms);            db.SubmitChanges();

how to delete records automatically at server side

  
the application which i created is a web application which doesnt need much of a database,so i am just using the sql express  free version which allows 4 gb of data,to avoid disasters iw ould like to delete records which are very old like,1 month,2 months old etc,how can i do this periodically should i do this on client side or the server side... what id there are 100000 records and if we put this in page load or some method,it could take some time to delete,which i dont want..........can anyone suggest how i can automate this process at the server side only Please help me... thanks  

Delete records if it is not getting inserted and also in the related tables

  
Hi All, If the insert into table person is successful, but the insert into table subject fails, delete the person record and all records associated with the person that were also inserted. If the insert into table person is successful, and the insert into subject is successful, but the insert into the booking table fails, delete the subject record, the person record ,and all records associated with the person that were also inserted. The reason for the failure into the 3 tables (person, subject, booking) should be logged to tblErrors . Also, a message should be written to tblErrors which logs the fact that this situation has occurred.     -- I have few stored procs which does inserts and updates How would i capture the record which is not getting inserted and log in to log table and delete the previous records which got inserted  which are related to that.

Delete Duplicate Records from MySQL table

  

I have Revision column in Quote table with time stamp (MySql table) now i am viewing the entire date and time my end user want to view only Date he doesnot want to see time how can i pull only date.i am displaying revision column in two ways one in grid view and another one in field value

Query in my Grid view
Dim query As String = "SELECT QuoteNumber,Revision,Vendor,PartNumber,status,Customer,Requestor from quotes"
On select check box in grid view, filed values will be loaded with data.

field value
txtRev.Text = dt.Rows(indx)("Revision").ToString()

 

 

 


how to delete records from multiple tables.

  

Hi Friends,Tell me How to delete records from multiple tables by using a single query.


Insert, Update and delete locks

  

Hi,

I looking for the best way to set locks for the following sql commands:

1)

"

Insert

 

into Users

(

UserName

,

Email

,

Pswrd

 

)

values<

SSIS : Insert, Update, Delete records from destination table by comparing records in source table.

  

Requirement: Inse

rt, Update, Delete records from destination table by comparing records in source table.

Update: if records exist in both the table compare them, and update value in destination table if value is different.

Insert: if record doesn't exist in destination table, add new record in destination table.

Delete: if record exist in destination table but not in source table, delete record from destination table.

----> I have created an SSIS package that does all this task but in order to perform delete operation, I have to store all records of destination table into a temporary table.
And since it's not the most efficient way, I have created another SSIS package by using Lookup & conditional Splits
method, now it works fine with inserting and updating information but I am not sure on how to handle removing record operation!

I would really appreciate if you could help me with this.

(In order to archive inserting and deleting records, I have referred following thread!
http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx )


Add, Modify, Delete Screen with a Datagrid on top listing all the records

  

Hi,

I have the requirement of creating an Add, Modify, Delete Screen with a Datagrid on top listing all the records. This needs to be incorporated using WPF with .Net 4.

1. I have to create a Base Control which holds a Datagrid and a tool bar with Add, Modify, Delete and Cancel buttons.
2. I have to create a Control (detail) having some fields like name, address, dateofbirth, etc. I need to inherit the above base control in this detail control to override events like btnAdd_Click, btnModify_Click, btnDelete_Click, etc.

Please let me know how to achieve this in WPF, I am facing couple of problem here:

1. I followed the approach mentioned in the below link.

http://svetoslavsavov.blogspot.com/2009/09/user-control-inheritance-in-wpf.html

Following this approach I am using a wpf custom control for my base control. But I am not sure if its good to place my Datagrid in Generic.xaml. Even with this approach I am facing problems in wiring up Datagrid Events from Generic.xaml to my custom control (my base control) class.

Please give a example solution for this problem.


How can I Delete the mentioned records in sql server?

  

How can I Delete the hilighted recores in SQL Server that has been shown in the linked file. I can not delete this records because It has an mentioned error.

https://cid-323eca3247461844.skydrive.live.com/redir.aspx?resid=323ECA3247461844!112


Delete records in Gridview with multi-datakeynames in GridView!

  

Hello all,

I have a GridView that displays the sample data as following:

Category    |      Title           |    Grade

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

Math         |      Algebra         |       6

Math         |      Geometry      |       5

Math         |      Algebra         |       4

Reading    |      Basic             |       5

There are no duplicate records. Is it any solution to obtain values of category, book and grade of each row with checkbox marks and pass them to stored procedure parameters: ParamCategory, ParamTitle, ParamGrade for deleting process. I believe that we can get values of one specific row with checkbox mark. Any suggestions or comments how to solve are really appreciated. If possible to give me a sample codes soon, thanks in advance. 

 


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.

ASP.NET GridView - edit records using JQuery Dialog

  
There are many ways in which you could modify modular data in Grid View:
1. Inline by making rows editable when u click on Edit
2. By using separate pages for Edit
3. By using popups/dialogs for adding/editing
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