Update/Insert and Table Locking

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Update/Insert and Table Locking

Author

Topic

sduffy77
Starting Member

18 Posts

Posted - 2008-11-20 : 09:23:48

Hello everyone.
Each night we have several bulk imports that update our inventory table.

Each of these different types of imports go into it's own table via bulk import. From there another sproc will do any updates if the item exists in the inventory table or insert it if it does not exist. This is where I believe it is doing Table locks. Each import has anywhere from 30K records to 700K records.

I also want each import in it's own table for redundancy and so that if there's a problem with the main inventory table that we can replicate it quickly.

What I've been struggling with is getting it so that even when there is a import occurring that we can still view the inventory. So far everything I've done has not worked and the webpage usually times out.

What solutions are out there that could help us with this problem?

Any help is greatly appreciated.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-24 : 06:22:48

If you just need to view data use the no lock hint:

Select * from table
with (nolock)

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-24 : 06:32:03

Also are you just updating / inserting or are you also deleting?
Deletion can be slow (i.e. increases chance of locks) because each row deleted is logged if you use Full Recovery Model.
If you are deleting large number of rows it might be helpful to look at work-arounds to minimise logging.
If this is the case let us know - there are a few different options.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 06:44:23

quote:
Originally posted by darkdusky

If you just need to view data use the no lock hint:

Select * from table
with (nolock)

but this will cause dirty reads if data is changing frequently.

sduffy77
Starting Member

18 Posts

Posted - 2008-11-24 : 09:18:02

Yes I am using the nolock hint and the insert/updates only occur once a day. Is it possible that the SQL server cannot handle the workload which is why it may not respond during the import?

Any suggestions on a SQL monitoring tool and/or "how to" articles?

sduffy77
Starting Member

18 Posts

Posted - 2008-11-24 : 09:19:40

Also, is it possible to fix this using more hardware? Currently we only have one sql box but is it possible to have another sql box and have it mirrored on the other and have changes trickle instead of all at once and not responding during the import?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 09:22:27

quote:
Originally posted by sduffy77

Yes I am using the nolock hint and the insert/updates only occur once a day. Is it possible that the SQL server cannot handle the workload which is why it may not respond during the import?

Any suggestions on a SQL monitoring tool and/or "how to" articles?

Are there triggers existing for these tables?Also how does bulk import occur? are you using BULK INSERT command? or any other way?

sduffy77
Starting Member

18 Posts

Posted - 2008-11-24 : 10:20:19

quote:
Originally posted by visakh16

quote:
Originally posted by sduffy77

Yes I am using the nolock hint and the insert/updates only occur once a day. Is it possible that the SQL server cannot handle the workload which is why it may not respond during the import?

Any suggestions on a SQL monitoring tool and/or "how to" articles?

Are there triggers existing for these tables?Also how does bulk import occur? are you using BULK INSERT command? or any other way?

I'm not using any triggers for this. The Bulk Insert is done to populate each feeds table but this isn't where the problem occurs. It's when we copy from that individual feed table to the master inventory table.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-26 : 04:44:50

RE: "From there another sproc will do any updates if the item exists in the inventory table or insert it if it does not exist."
Is this the area which is slow? Are you using cursors? It may be helpful to post this procedure.

sduffy77
Starting Member

18 Posts

Posted - 2008-11-26 : 09:22:50

quote:
Originally posted by darkdusky

RE: "From there another sproc will do any updates if the item exists in the inventory table or insert it if it does not exist."
Is this the area which is slow? Are you using cursors? It may be helpful to post this procedure.

Here's a snippet of the sproc


UPDATE [dbo].[tblInventory_INV]
   SET 
	  [INV_ID] = TMP.ID
	  ...
from (
		SELECT 
		...
		from dbo.tblHNitems_HNI HNI
		inner join dbo.ctDealers DEL 
			ON HNI.HNI_DealerID = DEL.DealerID 
		INNER JOIN dbo.tblItemFeedTypes_IFT IFT 
			ON IFT.IFT_ID = 'f3bf8f68-4965-46e6-9fa8-6657d6d3a35d'
		WHERE EXISTS (
			Select INV.INV_ID 
			from [dbo].[tblInventory_INV] INV
			INNER JOIN dbo.tblItemFeedTypes_IFT itmFT -- Feed Info
			ON itmFT.[VFT_ID] = INV.[IFT_ID]
			where INV.INV_ID = HNI.HNI_ID
			AND itmFT.IFT_Priority >= IFT.IFT_Priority
			)
		) TMP
Where TMP.ID = [INV_ID]

INSERT INTO [dbo].[tblInventory_INV]
   (
	...
	)
SELECT  
	...
from dbo.tblHNitems_HNI HNI
inner join dbo.ctDealers DEL 
	ON HNI.HNI_DealerID = DEL.DealerID 
INNER JOIN dbo.tblItemFeedTypes_IFT IFT 
	ON IFT.IFT_ID = 'f3bf8f68-4965-46e6-9fa8-6657d6d3a35d'
WHERE NOT EXISTS (
	Select INV.INV_ID 
	from dbo.[tblInventory_INV] INV
	where INV.INV_ID = HNI.HNI_ID
	)

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-26 : 11:15:02

RE: the update section of query
I used similar code to yours for the update part on two test tables with 1 million rows each. Table1 being updated Table2 representing your joined tables. It took 29 seconds (without even having joins etc and only using indexed fields.
Re-coded to:
UPDATE t1
SET cl = t2.clid
FROM dbo.Table2 t2
INNER JOIN dbo.Table1 t1
ON t2.id = t1.id

It took 1 second. i.e 29 times faster

Try changing your Update section to something like:

UPDATE t1
SET
t1.[INV_ID] = t2.ID
...
from
(
SELECT
...
from dbo.tblHNitems_HNI HNI
inner join dbo.ctDealers DEL
ON HNI.HNI_DealerID = DEL.DealerID
INNER JOIN dbo.tblItemFeedTypes_IFT IFT
ON IFT.IFT_ID = 'f3bf8f68-4965-46e6-9fa8-6657d6d3a35d'
)
) t2
Inner join
[dbo].[tblInventory_INV] t1
ON t2.ID = t1.[INV_ID]

sduffy77
Starting Member

18 Posts

Posted - 2008-12-09 : 10:33:06

I tried darkdusky's suggestion with not much change.

Is there a hardware solution to this problem?
For instance, if you have a mirrored database, and you update one database, do the updates then get mirrored with no locks?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources