How to avoid table locks when deleting\ inserting huge records

We have a scenario where a complex calculation happens within a stored 
procedure which produces around 1 Lac records at the end of calculation. 
These 1 Lac records needs to be stored to a table. Everytime, this is 
calculated for the same parameter, it deletes previous result and inserts 
again. Since Delete\ Insert involves huge number of records, Lock Escalation 
happens and 'Table Lock' is applied. As the concurrent users increase, the 
response time from the Stored Proc increases drastically.

Please suggest how else this can be done to bring down the response time.

Thanks,
0
Utf
3/11/2010 2:35:02 AM
sqlserver.programming 1873 articles. 0 followers. Follow

3 Replies
1466 Views

Similar Articles

[PageSpeed] 9

Hi
Divide delete operation into small batches

WHILE 1 = 1
BEGIN
   DELETE TOP(2000)
   FROM Foo
   WHERE <predicate>;

   IF @@ROWCOUNT < 2000 BREAK;
END




"Augustin Prasanna" <AugustinPrasanna@discussions.microsoft.com> wrote in 
message news:1E8FBF7A-0CE2-4D16-A7EC-31A6D8DC7F3F@microsoft.com...
> We have a scenario where a complex calculation happens within a stored
> procedure which produces around 1 Lac records at the end of calculation.
> These 1 Lac records needs to be stored to a table. Everytime, this is
> calculated for the same parameter, it deletes previous result and inserts
> again. Since Delete\ Insert involves huge number of records, Lock 
> Escalation
> happens and 'Table Lock' is applied. As the concurrent users increase, the
> response time from the Stored Proc increases drastically.
>
> Please suggest how else this can be done to bring down the response time.
>
> Thanks, 


0
Uri
3/11/2010 10:45:55 AM
Consider not deleting the records but rolling forward, ie INSERT only.  Make 
sure your code only deals with the latest recordset.

Have a batch job which runs overnight or at a quiet time to remove records 
which aren't required.

Alternately, you may be able to index in such a way as to support the DELETEs.

wBob
HTH

"Augustin Prasanna" wrote:

> We have a scenario where a complex calculation happens within a stored 
> procedure which produces around 1 Lac records at the end of calculation. 
> These 1 Lac records needs to be stored to a table. Everytime, this is 
> calculated for the same parameter, it deletes previous result and inserts 
> again. Since Delete\ Insert involves huge number of records, Lock Escalation 
> happens and 'Table Lock' is applied. As the concurrent users increase, the 
> response time from the Stored Proc increases drastically.
> 
> Please suggest how else this can be done to bring down the response time.
> 
> Thanks,
0
Utf
3/11/2010 1:14:04 PM
What database is it for you and how critical, you can try playing
around changing the Isolation Levels and also Make the Insert to work
as a Bulk Insert if the DB is in Recovery Mode.

Sriram
www.sqllike.com
0
yssr83
3/12/2010 2:25:54 AM
Reply:

Similar Artilces:

read and delete??????
Has anyone heard of Outlook deleting a message as soon as it is read once and then closed? If so, could you tell me how to turn that off? m_alcorn@yahoo.com Thanks I'm not sure how to get rid of it, unless someone set a rule to make that happen. It's certainly not an automatic feature. If I wanted to make it happen, this is how I'd do it. If someone did this on your PC, it should simply be a matter of finding and deleting the rule. Here's how: Click Inbox . On the Tools menu, click Rules Wizard. In the Apply rules in the following order box, click the rule you want ...

I inserted a new title master
I inserted a new title master - (it is the only title master, and there is also a slide master as well). However, no slides use the title master - how do I get a slide to use it - I want to add a title page). -- Marc Are you using PowerPoint 2003? Select the slide you wish to be your title slide. If you created your Title Master then go to Format > Slide Layout. In the Task Pane at the right, hit the drop down menue for the Title Master Slide and select Apply to Selected Slides. Sandy "Marcus Analyst" wrote: > I inserted a new title master - (it is t...

Access can not find a table in append query
Hi all, I have a table called MyCustomers. For some reason, when I try to create Append query, it doesnt show on the list of tables. I know the the table is there and I can select it if I want to create a different type of query but not append. What can cause that? TIA, Tom Does your table have a PRIMARY KEY defined? -- Bob Larson Access World Forums Super Moderator Utter Access VIP Tutorials at http://www.btabdevelopment.com __________________________________ If my post was helpful to you, please rate the post. "Tom" wrote: > Hi all, > > I have a table call...

moving records to new business unit.
We only have one business unit. We have about 400 accounts. We want to create a new business unit and move some of the accounts to the new business unit. The president of our company wants to see ALL records from ALL business units. A salesperson wants to see ONLY records from her business unit. How can we create new business units and move exising account records to the new business unit? Lynn Lynn Business Units are containers for Users. When you create a Business Unit and add users to it, all the records (Accounts, Contacts, Activities etc) will automatically be associated with th...

inserting pictures into table
i am doing a yearbook, so i created a table, with hidden lines, and within each cell i would like to insert a photo of a child, but they do not automatically fit into the cell, i have over 300 students to do. what is an easier way to insert the photo into the cell? You certainly have your work cut out for you. Place the cursor in a cell, fill, fill effects, picture tab. All of Publisher menus are tear-offs, you might want to tear off the fill menu, saves a click or two. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "bran...

What's the best way to learn Pivot Tables and using Macros in Exc.
I think the best way to learn is to find some data and play with it. I think it's better to use real data and have a result in mind--so it's not just an exercise. Pivottables: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl200...

ESC key is the only way to Save the Record?
I'm having a very strange issue here: The only way to save my record is to hit the ESC key? - I have a Tabbed Form on my Main form, with Subforms on each Tabbed Page. - The Main form is based on tblBids - The Tabbed forms are all based on Linked Tables/Detail Table to allow for Many to Many. - When I create a new record, the Main form data will not save until I hit ESC? - Once done, I can then enter the Subform data but cannot save that until I hit ESC? I do not understand what is going on here? I have the correct relationships, correct tables, and my subform Link Master/Child fields ar...

Sorting in Pivot table
I have a data set I am putting into a pivot table to show the data over time but do no not want the data added up. I have the product as a page by, the classification in row, mont in column, and # and % in data. The problem is it is adding the West New Customers and West Continuing Customers together. The data is already calculated so I just need the pivot to view the months across columns and the ability to choose a product to view. Is this possible without changing the data itself? example: Product CLASSIFICATION MONTH # % Cable TV Current Customers 1/1/2010 400 100.00% C...

Pivot Table "stdev" summary
I have a pivot table which reports mean and standard deviation for my population (obtained as external data from SQL Server). Instead, I would prefer to be reporting the maximum and minimum values within 2 standard deviations of the mean. ie. mean = x standard deviation = o How do I get my pivot table to show x + 2o x - 2o ? Thanks, Kim. .. ---------------------------------------------------------- I have posted this question before. Somehow it ended up in the microsoft.public.exchange.misc newsgroup instead of microsoft.public.excel.misc ...

delete mailbox #2
Dear all One of mailbox during move from Exchange 2k3 to Exchange 5.5 has error (during close the connection but not respond), now that mailbox already has a copy in Exchange 5.5 and also has a copy in Exchange 2k3 server. Then I delete that mailbox in Exchange 2k3 server from ESM then it now disaply with a red cross. But when I try to purge it, it prompt that it still connect with a user account; when I try to reconnect it another testing user a/c (without mailbox connected), it prompt that it already connect with a user a/c Actually that user a/c in AD is mapped to the mailbox already move...

How do I delete cells in Excel without changing the data in others
I want to get rid of some of the rows in my spreadsheet but the data in the ones I am leaving behind are linked by formulas? As soon as I delete them all the data goes from the the others. Gemma, I may not understand your situation. An example of what I think you're saying is: Cell A2: A1+1. You want to delete cell A1, but leave A2 with the value it currently has. If this is the case, you need to copy A2 and the, using paste special, paste the value back into A2. This way A2 no longer has any formula at all and will remain unchanged when you delete A1. Art "G...

Entourage crashes when I delete emails. I get following message
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop Microsoft Error Reporting log version: 2.0 <br><br>Error Signature: <br> Exception: EXC_BAD_ACCESS <br> Date/Time: 2010-04-10 14:55:04 -0700 <br> Application Name: Microsoft Entourage <br> Application Bundle ID: com.microsoft.Entourage <br> Application Signature: OPIM <br> Application Version: 12.2.4.100205 <br> Crashed Module Name: JavaScriptCore <br> Crashed Module Version: unknown <br> Crashed Module Offset: 0x00003e...

How to avoid dealing with messages twice
I have an office PC and a home PC, as well as a laptop (used only when I am on the road) - with Office 2003 loaded on each of them. My daily routine involves downloading mail in the office PC (configured to leave mail on the server), coming home later in the afternoon to download mail on the home PC (also configured to leave mail on the server) and then going back to the office the next day to download the previous day's mail in a third 'archive' PC (which has Outlook Express on it and is configured to wipe downloaded messages off the server). 'Server' in this context refe...

Insert data from a Dataset into a SQL Database
Hi guys my name is Ralph, i am kinda new to this so please bear with me. I have written a .net web service which accepts a stream of data see relevent source code). Public Function PutBondInfo(ByVal data As String) As DataSet Try Dim zh As New ZipHelper Dim sz As String Dim cn As SqlConnection Dim da As SqlDataAdapter Dim ds As New DataSet Dim xSet As New DataSet Dim sSql As String Dim mySerializer As XmlSerializer = New XmlSerializer(ds.GetType()) cn = New SqlConnection(&quo...

Updating / Initializing Table Fields Upon Exiting Access
Is it possible to update "Field B" with data from "Field A", and then initialize/clear "Field A" upon exiting Access? Is there a reason you need to do it on exit.. like a time last update thing or something? Otherwise, you might be better of doing it on load... in case the database crashed or hung, and you never got a chance to run your update query. "txmcclain" wrote: > Is it possible to update "Field B" with data from "Field A", and then > initialize/clear "Field A" upon exiting Access? Access does not hav...

deleting phrases in Excel and/or Word
My question is for beginners but I am working with spreadsheets and documents which contains a lot of the same words I want to delete. Can anyone please tell me how I can do this? THANK YOU BEFOREHAND FOR YOUR HELP! Here's one quick play using SUBSTITUTE for progressive deletion .. Assume text data is running in A1 down Enter the exact words/phrases (case sensitive) you want deleted in say K1 down. Eg assuming you have 3 words to delete: in K1: the, in K2: Oops, in K3: milk Then place in B1: =TRIM(SUBSTITUTE(A1,INDEX($K:$K,COLUMN(A1)),"")) Copy B1 across by 3* cols to ...

mails get deleted automatically
Has anyone encountered a situation where e-mails get deleted few minutes after was delivered to a inbox? The server is running Microsoft Exchange 2000, the client is running Outlook (Office 2003). Are the mail deleted from the information store or from the client's point of view.. Could there be an POP3 account fetching the mails and deleting them afterwards ? How is the client settings ? cached or non-cached ? Regards Kenneth Johansen "Karen Yeh" wrote: > Has anyone encountered a situation where e-mails get > deleted few minutes after was delivered to a inb...

Insert file name into Cell
Is there a way to insert the file name into a cell, rather than on header/footer? Hi Bonny, 1996FEDT.XLS =MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1) D:\driveM\excel\TAXES\1996FEDT.XLS [Sheet1] =SUBSTITUTE(SUBSTITUTE(CELL("filename",A1),"[",""),"]"," [") & "]" for more information, worksheet examples, and coding examples for pathname, filename, sheetname and combinations of ...

Send data to table
I'm trying to add some data to a table with this command button code. It hangs at: lID = GetNewID("tblLetterVals") Says: Sub Function not defined Could someone please help me define "GetNewID" Private Sub ThankYou_Click() Dim lCriteria As String Dim lICCNNO As String lICCNNO = Me!ICNNo Dim lID As Long lID = GetNewID("tblLetterVals") lCriteria = "INSERT INTO tblLetterVals (ID, ICNNo, ProvNo)" lCriteria = lCriteria & "SELECT " & lID & " AS ID, tblQuality...

Partitioning
Hi, I have filegroups called 2006, 2007, 2008 etc. I have files, one for each of our larger tables that belong to each filegroup. E.G. File Name File Group 2006_Transactions 2006 2006_Events 2006 2007_Transactions 2007 2007_Events 2007 etc... I have the folloinwg partitioning: CREATE PARTITION FUNCTION [PFBusDate](SMALLDATETIME) AS RANGE RIGHT FOR VALUES (N'2007-01-01T00:00:00', N'2008-01-01T00:00:00', N'2009-01-01T00:00:00', N'2010-01-01T00:00:00', N'2011...

Insert Signature Missing (Outlook 2003)
I have two signatures configured in Outlook 2003 and have neither of them configured to be automatically inserted. Instead, I want to insert one of them on a case by case basis through the "Insert\Signature" menu. However, the Signature menu option is MISSING! Outlook 2003 Help indicates that it should be there, but it's not! Please help. ...

Deleting pictures in Excel using visual basic
I have written a visual basic module to insert pictures from a list contained in a worksheet. I have figured out how to delete the picture when I load the next picture. For this I need to know the picture number. My problem is the indexing or the picture number. The number of shapes ActiveSheet.Shapes.Count doesn't change But the picture number does increment. I am using workaround by updating the picture number in a cell on the active sheet BUT this is a kluge! More importantly, I will be using this to display a large number of images [1000s 100000s]. I don't know wheter there is an ...

Deleting PO's
I need to get rid of old PO's and deleting them sounds great. Does this cause any problems down the road? Will item information stay the same? I hate deleting anything. ...

deleted record
can i configured in exchange to auto delete the users "deleted items" after 1 week or whatever date is stablish? thanks raul rego Yes, using Recipient Policy with Mailbox Manager settings. How to use recipient policies to control mailboxes in Exchange 2000 and Exchange 2003 http://support.microsoft.com/kb/319188 -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- "news.microsoft.com" <jjkgr@hotmail.com> wrote in message news:uGZQn3MtGHA.4252@TK2MSFTNGP02.phx.gbl... >...

SQL server full text indexing, ranking and joining to other tables
i searched the net about sql server full text indexing and ranking and got this sample sql statement and it works using 1 table only: SELECT myTable1.id, myTable1.Title, fulltextSearch.Rank FROM myTable1 JOIN FreeTextTable(myTable1, [myField1], 'awesome ranking') fulltextSearch ON myTable1.id = fulltextSearch.[KEY] ORDER BY Rank DESC what i do not know is how to make it to work when it is joined to another table. how do i do that if i have these sample tables? myTable1 ---------- id myField1 1 rrrrrrr 2 qqqqqqq 3 kkkkkkk I don't see sample tables, merely one...