T-SQL (SS2K8) :: Reduce Locking For Insert Statement?
Sep 10, 2014
There are 2 tables which need to have data inserted into them for auditing purposes. The number of inserts per minute seems be at least 50-100. How to reduce locks during inserts.
There are 2 tables
Table1
ID - Surrogate Key/identity Column
SomeColumn1
SomeColumn2
SomeColumn3
SomeColumn4_timestamp
clustered index on ID column
Table2
ID Column ..... there's a call to get id from SCOPE_IDENTITY()
SomeColumn1
SomeColumn2
clustered index on ID column
NC idx on SomeColum1
NC idx on SomeColum2
Does anyone know how to stop SQL server from applying exclusive table locks on mass Insert statements. I need to allow concurrent inserts into the same table using INSERT ... SELECT * FROM ...
SQL Server applies exclusive table locks that prevent this. I've tried setting LE as high as it goes but with no success. Microsoft suggest using single row inserts but this will be far too slow!
Does anyone know how to stop SQL server from applying exclusive table locks on mass Insert statements. I need to allow concurrent inserts into the same table using INSERT ... SELECT * FROM ...
SQL Server applies exclusive table locks that prevent this. I've tried setting LE as high as it goes but with no success. Microsoft suggest using single row inserts but this will be far too slow!
I have two tables for insertion in one transaction scope. Table one have 10 rows. After first table insert statement (not yet committed) if I run select on first table from other session, it holds table until my insert is committed or rolled back and from (SSMS), it display 10 rows and then wait for transaction scope till finished. My question is do I need to use no lock hint in this situation. Or there is something wrong with isolation level. One saying that in this situation table should not hols select while insert is in transaction scope.
I've 2 tables as follow, --> Full script and data as attachment, Scripts.zip
CREATE TABLE [dbo].[myMenuCollection]( [menuCollection_idx] [int] NOT NULL, [parentID] [int] NULL,
[code]....
You can see - User select 3 Menu, which is the Menu Id is 1, 4, 10.If the Parent Id for Menu is 0, there is 1 record only to insert. If the Parent Id for Menu != 0, we've to make sure the Insert statement will insert the Parent Menu automatically
Based on Photo Above, there's 3 Menu is selected. But, in back-end - Insert statement will insert 4 record. Please see Menu Id = 10. The Parent Id = 9. So, we need to insert Menu Id = 9 automatically into myInsertedMenu table
Can we insert into multiple table using merge statement?I'm using SQL Server 2008 R2 and below is my MERGE query...
-> I'm checking if the record exist in Contact table or not. If it exist then I will insert into employee table else I will insert into contact table then employee table.
WITH Cont as ( Select ContactID from Contact where ContactID=@ContactID) MERGE Employee as NewEmp Using Cont as con
I would like to provide the names of columns in an insert statement from a schema table, so that when running through a number of Bus Rule checks I can reference the schema table and only maintain the columns in the schema table rather than maintain named columns in multiple insert statements. So my query for one check looks like below. I'm using dynamic sql to execute the insert statement. My question is, is there a better way or different way to do this without using dynamic sql? Ie, Is there a way that I can use the columns parameter like this instead?
Declare @columns as nvarchar(max); Declare @InvSQL as nvarchar(max); SELECT @columns = STUFF (( SELECT ', [' + name + ']' FROM syscolumns WHERE id = OBJECT_ID('dbo.table_pvt')
============================ ALTER proc [dbo].[sp_CalculateMedianTimeInDepartmentMinutes]
@StartDate date ,@EndDate date as --== Check if count is even or odd declare @modulo int select @modulo = (Select COUNT(*)%2 from ED_data where AdmitDateTime between @StartDate and @EndDate ) --=== Get Median
[Code] ....
My fellow developer is using this code to calcuate a madians in many columns (see below). The problem is that it takes about 2 minutes to execute this code. Is there a way to reduce the time of execution?
I attach also a sample of the view
============== ALTER PROCEDURE [dbo].[sp_ED_Measures] @StartDate date, @EndDate date, @Hospital varchar(5) AS BEGIN SET NOCOUNT ON;
SQL Server 2005XEON CPU 3.0GMEMORY 2.0GRAID Tow tables:HIS_HTTP_ONLINE_LOG(PARTITION) FOR HISTORY DATAREL_HTTP_ONLINE_LOG(NOT PARTITIONED) FOR EVERYDAY DATA,AND THEY HAVE THE SAME STRUCTURE CREATE TABLE HIS_HTTP_ONLINE_LOG(ID numeric(20,0) NOT NULL,USERID varchar(32) NOT NULL,USERIP varchar(16) NOT NULL,USERPORT numeric(10, 0) NULL,OBJECTIP varchar(16) NULL,OBJECTPORT numeric(10, 0) NULL,HTTPURL varchar(256) NULL,HTTPHOST varchar(128) NULL,HTTPDNS varchar(128) NULL,VISITIME numeric(10, 0) NULL,STARTIME datetime NOT NULL,ENDTIME datetime NOT NULL)....... SELECT * INTO REL_HTTP_ONLINE_LOG SELECT * FROM HIS_HTTP_ONLINE_LOGWHERE 1=2 There are 5 indexes in HIS_HTTP_ONLINE_LOG ,There is not one index in REL_HTTP_ONLINE_LOG There are about 5000,000 records in REL_HTTP_ONLINE_LOG everyday,at night it will move into HIS_HTTP_ONLINE_LOG automatically,The data of everyday in REL_HTTP_ONLINE_LOG will be last 90 days. My operations:1: ALTER DATABASE DB SET RECOVERY SIMPLE2: EXEC SP_DBOPTION DB, 'select into/bulkcopy', 'TRUE'3：INSERT INTO REL_HTTP_ONLINE_LOG SELECT * FROM HIS_HTTP_ONLINE_LOGWHERE 1=24: TRUNCATE TABLE REL_HTTP_ONLINE_LOG ASK:why the step 3 cost so much time ? (about 1 hour) and how can I reduce the transaction logs in this period ? Could you give me some suggestions ?Thanks!
SQL Server 2005XEON CPU 3.0GMEMORY 2.0GRAID Tow tables:HIS_HTTP_ONLINE_LOG(PARTITION) FOR HISTORY DATAREL_HTTP_ONLINE_LOG(NOT PARTITIONED) FOR EVERYDAY DATA,AND THEY HAVE THE SAME STRUCTURE CREATE TABLE HIS_HTTP_ONLINE_LOG(ID numeric(20,0) NOT NULL,USERID varchar(32) NOT NULL,USERIP varchar(16) NOT NULL,USERPORT numeric(10, 0) NULL,OBJECTIP varchar(16) NULL,OBJECTPORT numeric(10, 0) NULL,HTTPURL varchar(256) NULL,HTTPHOST varchar(128) NULL,HTTPDNS varchar(128) NULL,VISITIME numeric(10, 0) NULL,STARTIME datetime NOT NULL,ENDTIME datetime NOT NULL)....... SELECT * INTO REL_HTTP_ONLINE_LOG SELECT * FROM HIS_HTTP_ONLINE_LOGWHERE 1=2 There are 5 indexes in HIS_HTTP_ONLINE_LOG ,There is not one index in REL_HTTP_ONLINE_LOG There are about 5000,000 records in REL_HTTP_ONLINE_LOG everyday,at night it will move into HIS_HTTP_ONLINE_LOG automatically,The data of everyday in REL_HTTP_ONLINE_LOG will be last 90 days. My operations:1: ALTER DATABASE DB SET RECOVERY SIMPLE2: EXEC SP_DBOPTION DB, 'select into/bulkcopy', 'TRUE'3：INSERT INTO REL_HTTP_ONLINE_LOG SELECT * FROM HIS_HTTP_ONLINE_LOGWHERE 1=24: TRUNCATE TABLE REL_HTTP_ONLINE_LOG ASK:why the step 3 cost so much time ? (about 1 hour) and how can I reduce the transaction logs in this period ? Could you give me some suggestions ?Thanks!
I have a question regarding a locking scheme in MSSQL I hope you guys can help. In Sybase, I am able to specify datarow locking in DDL (ex. create table, alter table). Can I do the same in MSSQL or is there an equivalent option in CREATE TABLE statement in MSSQL? I came across a few articles in MSDN about datarow locking and it seems to me that MSSQL only allows locking through DML... Is that true? Thanks.
SQL Server 6.5 on-line help states that IRL is only effective if the table has a unique clustered index defined on the table. IF this true and if so does anyone know why.
I am trying to insert data using the select * command from one table into another, but query analyzer just seems to run and run (over one hour before I stopped it). The current activity shows a huge number of extent locks. The table itself has approx 90,000 rows. What is causing this insert not to finish, and how do I sort it?
I am a little confused over LE thresholds etc. At present the server has the following settings: LE threshold max - 10000 LE threshold min - 20 LE threshold % - 0 locks - 60000
Hi, I want to insert a record in a table having an identity column as primary key. I want to lock the table while inserting. so that no one should be able to insert, select, update, delete from the table. and once my insert is over, then will release the lock.
Can I have the code for the same. M using SQL SERVER 2005.
We have a SSIS package which loads the data from csv files to DB. It only loads the new entries ie if the row already exists in the tables than it doesn't insert it. For this we load the CSV to temp tables for respective schemas and than those are compared with base tables of respective schemas and inserted new rows. For this we use Merge statement.
I'm working on inserting data into a table in a database. The table has two separate triggers, one for insert and one for update (I don't like it this way, but that's how it's been for years). When there is a normal insert, done via a program, it looks like the triggers work fine. When I run an insert manually via a script, the first insert trigger will run, but the update trigger will fail. I narrowed down the issue to a root cause.
This root issue is due to both triggers using the same temporary table name. When the second trigger runs, there's an error stating that a few columns don't exist. I went to my test server and test db and changed the update trigger so that the temporary table is different than the insert trigger temporary table, the triggers work fine. The weird thing is that if the temporary table already exists, when the second trigger tries to create the temporary table, I would expect it to fail and say that it already exists.I'm probably just going to update the trigger tonight and change the temporary table name.
I have an SSIS package doing a bulk insert from a file. Then later on I'm trying to delete that file (in a file delete task), but I'm getting an error:[File System Task] Error: An error occurred with the following error message: "The process cannot access the file 'xyz' because it is being used by another process.".I'm wondering if there isn't some way to 'tweak' the bulk insert syntax so that it doesn't lock the file?
I have implemented Remus Resanu's implementation from the Recycling Conversations article and I am experiencing locking issue when I try to insert new conversation handles to the SessionConversations table. I have copied the code in the article exactly including the activation procedure. Any ideas why I may be locking. I am thinking it is related to the HOLDLOCK hint on the table.
The sepcific line where I see locking is directly from the article:
INSERT INTO [SessionConversations] (SPID, FromService, ToService, OnContract, Handle) VALUES (...etc)
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
Hello I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake... Here's the sql management studio diagram :
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Question_SurveyTemplate". The conflict occurred in database "ankietyzacja", table "dbo.SurveyTemplate", column 'id'. The statement has been terminated. at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ? Thanks a lot.
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them 1) Create the table 2) Create the trigger 3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value 4) Drop the trigger 5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
GO /************************************************ 2) - Create the trigger ************************************************/ CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table] INSTEAD OF INSERT AS BEGIN
INSERT INTO my_table ( forename, surname) SELECT forename, surname FROM inserted
END
/************************************************ 3) - Do the insert ************************************************/
INSERT INTO my_table ( forename , surname ) OUTPUT inserted.my_table_id INTO @my_insert VALUES( @forename , @surname )
select @@identity -- expect this value in @my_insert table select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************ 4) - Drop the trigger ************************************************/
drop trigger [dbo].[trig_my_table__instead_insert] go
/************************************************ 5) - Re-run insert from 3) ************************************************/ -- @my_insert now contains row expected with identity of inserted row -- i.e. OK
I have someone telling me that I should have put my AND statement in the join instead of the where part, so is there a difference in the where I put it. Is there a difference in the results in any way between the two here in the results in anyway?
Example: Given a query: But let’s take a simple one here:
Query(1) Select ct1.Name, ct1.address, ct1.city, ct1.state, ct1.zipcode From cutTableA ct1 Left join cutTableA ct2 On ct1.ID = ct2.ID Where ct1.zipcode = '14124' AND ct1.Name = 'Bob'
Query(2) Select ct1.Name, ct1.address, ct1.city, ct1.state, ct1.zipcode From cutTableA ct1 Left join cutTableA ct2 On ct1.ID = ct2.ID AND ct1.zipcode = '14124' Where ct1.Name = 'Bob'
Hi All, I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance. My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code: Insert into myTblA (TblA_ID, mycasefield = case when mycasefield = 1 then 99861 when mycasefield = 2 then 99862 when mycasefield = 3 then 99863 when mycasefield = 4 then 99864 when mycasefield = 5 then 99865 when mycasefield = 6 then 99866 when mycasefield = 7 then 99867 when mycasefield = 8 then 99868 when mycasefield = 9 then 99855 when mycasefield = 10 then 99839 end, alt_min, alt_max, longitude, latitude ( Select MTB.LocationID MTB.model_ID MTB.elevation, --alt min null, --alt max MTB.longitude, --longitude MTB.latitude --latitude from MyTblB MTB );
The error I'm getting is: Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works. I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
I need to write a SQL script where a join condition is using date columns (effective_date, ineffective_date). The effective date columns can be slightly different (e.g. differ by a day) for some rows of data. I need the join condition to accommodate these date differences and return these rows of data as well.
I have a table which uses multiple joins to create another table but it turns out that the effective_date which is used in the join to match row together does not work all the time since some of the dates for the effective date column are out of sync meaning records that show data as missing even when the other table contains the data. I tried the SQL script below using the BETWEEN clause but it returning 6 rows instead of 3–
Select P.S,E.S,E.R from Pack P(nolock) join Exp E on P.Id=E.O on E.R is null case when E.R is not null then '' else '' end where P.s='PLT000044'
I have to query two conditions joining the tables. when E.R is NULL and when E.R is not null. but the value is coming from the join between the 2 tables :P and E.
I am supposed to delete all rows from USER and its child tables based on PracticeID = '55' filter condition.
I have dynamically generated queries to delete child table first followed by parent. Table [EncounterSignOff] si child and [User] is parent.
I would like to know, whether the Query 1 is valid for deleting child records?
Query:1
DELETE Top(100000) FROM [dbo].[EncounterSignOff] FROM [dbo].[User] INNER JOIN [dbo].[EncounterSignOff] ON [User].[UserID] = [EncounterSignOff].[UserId] WHERE [User].PracticeID = '55';
Query:2
DELETE Top(100000) FROM [dbo].[User] WHERE [User].PracticeID = '55';
I have a population split between two vendors. One gets last names between A and R, the other the rest. Now, on a given date vendor 1 gets everybody.
I can accomplish this with a case statement on the upper range (R or Z), but it seems I should be able to do this without testing at all after the turnover date.
A small bit of the code:
declare @get_date datetime = convert(char(10),getdate(),101) select top 10 pt.pt_id, pt.last_name fromsmsmir.mir_acct a join smsmir.mir_pt pt on (a.src_sys_id = pt.src_sys_id and a.pt_id = pt.pt_id and a.from_file_ind = pt.from_file_ind
[Code] ....
Seems I should be able to not test the last name after the turnover date, but I can't figure out how.
Can we use case in pivot like below? I am getting an error. I want to do Pivot on condition basis.
select ( Column1 ,Column2 ,Column3 ,Column4 ,coloumn5 from Mytable ) x pivot ( case when Column1 = 6 then sum(Column3) else max(Column4) End for coloumn5 in (' + @COLS + ') )p
I have a Stored Proc which populates a table and then uses BCP to output the table into a flat file and lastly ftp the file out to a remote site.
I'm trying to update the error handling as I first wrote this script on SQL2000 and it has now moved to SQL2008r2. The stored proc looks something like this:
BEGIN TRY BEGIN TRANSACTION <A whole bunch of inserts and updates> COMMIT TRANSACTION END TRY BEGIN CATCH <Error handling> ROLLBACK TRANSACTION END CATCH BEGIN <xp_cmdshell, BCP, FTP stuff> END
What I need to do is jump to the end of the script if an error invokes the CATCH block, so the xp_cmdshell stuff is not exicuted. Can I simply put a GOTO statement to take it to the end in the CATCH block, or do I have to set a variable in the CATCH block then test the variable outside the CATCH block or indeed is there a better way to simply terminate the script following the ROLLBACK?
