What is the best way to update data in a table during use without locking the table?

Question

I have a table in a SQL Server 2005 database that is used a lot. It contains product availability information. We receive updates every hour from our warehouse, and over the past few years we have launched a procedure that cuts the table and updates the information. It only takes a few seconds and still has not been a problem. We have many more people who use our systems, who request this information now, and as a result we see many timeouts due to blocking processes.

... So...

We explored our options and came up with the idea of ​​mitigating the problem.

• We would have two tables. Table A (active) and Table B (inactive).
• We would create a view pointing to the active table (table A).
• All things that need this tabular information (4 objects) will now need to view the view.
• The hourly procedure trims the inactive table, updates it with the latest information, and then updates the view, pointing to the inactive table, making it active.
• This procedure will determine which table is active and basically switch the view between them.

What happened to this? Problems with a call in the middle of a request will cause problems? Could this work?

Thanks for your experience.

Additional Information

• the subroutine is an SSIS package that displays many steps and ultimately trims / updates the table in question.

• Lock processes are two other stored procedures that query this table.

Answer 1

. SSIS .

, .

Answer 2

, - , .

, . .

, , - .

with (nolock) SQL View. .

Answer 3

, , , /. , .

, (, ), , , . (BatchProcessingEvent), , , .

$0,02,

Answer 4

, SSIS

TableDiferance : http://www.sqlbi.eu/Home/tabid/36/ctl/Details/mid/374/ItemID/0/Default.aspx

alt text http://www.sqlbi.eu/Portals/0/Articles/Table%20Difference%20Images/DataFlowSimple.png

, , ONE ONE, , , , , .

Answer 5

, .

Truncate , .

, .

, , , . , .

Answer 6

, .

.

", "

, :

Table Products:
    ProductId       int
    QuantityOnHand  Int

QuantityOnHand .

, :

Table Prodcuts_WareHouse
    ProductId       int
    QuantityOnHand  Int

"" :

Table Prodcuts_Actions
    ProductId       int
    QuantityOnHand  Int
    Action          Char(1)

:

1.Truncate table Prodcuts_WareHouse

2.Truncate table Prodcuts_Actions

3. Prodcuts_WareHouse

4. Prodcuts_Actions :

:

INSERT INTO Prodcuts_Actions (ProductId, QuantityOnHand,Action)
SELECT     SRC.ProductId, SRC.QuantityOnHand, 'I' AS ACTION
FROM         Prodcuts_WareHouse AS SRC LEFT OUTER JOIN
                      Products AS DEST ON SRC.ProductId = DEST.ProductId
WHERE     (DEST.ProductId IS NULL)

INSERT INTO Prodcuts_Actions (ProductId, QuantityOnHand,Action)
SELECT     DEST.ProductId, DEST.QuantityOnHand, 'D' AS Action
FROM         Prodcuts_WareHouse AS SRC RIGHT OUTER JOIN
                      Products AS DEST ON SRC.ProductId = DEST.ProductId
WHERE     (SRC.ProductId IS NULL)

INSERT INTO Prodcuts_Actions (ProductId, QuantityOnHand,Action)
SELECT     SRC.ProductId, SRC.QuantityOnHand, 'U' AS Action
FROM         Prodcuts_WareHouse AS SRC INNER JOIN
                      Products AS DEST ON SRC.ProductId = DEST.ProductId AND SRC.QuantityOnHand <> DEST.QuantityOnHand

.

5. :

BEGIN TRANS

DELETE Products FROM Products INNER JOIN
Prodcuts_Actions ON Products.ProductId = Prodcuts_Actions.ProductId
WHERE     (Prodcuts_Actions.Action = 'D')

INSERT INTO Prodcuts (ProductId, QuantityOnHand)
SELECT ProductId, QuantityOnHand FROM Prodcuts_Actions WHERE Action ='I';

UPDATE Products SET QuantityOnHand = SRC.QuantityOnHand 
FROM         Products INNER JOIN
Prodcuts_Actions AS SRC ON Products.ProductId = SRC.ProductId
WHERE     (SRC.Action = 'U')

COMMIT TRAN

, , .

, .

Answer 7

SQL Server, SQL Server.

, "Live" "" ( , ).

, "" , "LIVE" OUT "" IN, .

, ( Secondary).

, , / .

SQL Server . :

http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx

: -)

EDIT:

, , SQL Server Row Versioning.

http://msdn.microsoft.com/en-us/library/ms345124(SQL.90).aspx

Answer 8

. , , , dev, . , SSIS, .

Answer 9

, . , , .

Answer 10

It might make sense to do some analysis of the processes that are being blocked, as they seem to be part of your landscape that has changed. To create the blocks you see, only one poorly written query is required. A ban on a poorly written query, maybe the table needs one or more coverage indexes to speed up these queries and get you back without processing existing code.

Hope this helps,

Bill