Swapping ms-sql tables

问题:

I want to swap to tables in the best possible manner.
I have an IpToCountry table, and I create a new one on a weekly basis according to an external CSV file which I import.

The fastest way I've found to make the switch was doing the following:

sp_rename IpToCountry IpToCountryOld
go
sp_rename IpToCountryNew IpToCountry
go

The problem with this is that the table might still be accessed in between.
How do I approach this problem in SQL?
In considered using sp_getapplock and sp_releaseapplock, but I want to keep the read from the table function as quick as possible.

回答1:

Assuming that you're unable to update/insert into the existing table, why don't you wrap all access to the table using a view?

For example, you might initially store your data in a table called IpToCountry20090303, and your view would be something like this:

CREATE VIEW IpToCountry
AS
SELECT * FROM IpToCountry20090303

When the new data comes in, you can create and populate the IpToCountry20090310 table. Once the table is populated just update your view:

ALTER VIEW IpToCountry
AS
SELECT * FROM IpToCountry20090310

The switch will be completely atomic, without requiring any explicit locking or transactions. Once the view has been updated, you can simply drop the old table (or keep it if you prefer).

回答2:

Another method to implement what you are looking to achieve would be the use of table partitioning, a technique that is available in the Enterprise Edition of SQL Server.

The table name can remain the same. After your table import is complete, you just simply switch out the partition containing your old data and switch in the new partition.

The following White Paper contains all the information you would need to get started.

http://msdn.microsoft.com/en-us/library/ms345146.aspx

Cheers, John

回答3:

I've had problems getting partitioning functions to work at scale. CREATE and DROP PARTITION are blocking operations, and you have little control over the blocking, and if it can't get a lock it will fail with a severity level 16 and kill your connection -- which you can't trap and retry without reestablishing the connection. But it might work just fine for you. Also, MSS Enterprise Edition is required, you can't use SE -- might be too much for some smaller or more cost-concerned shops.

I've also found the view redef to block at high-scale (= transaction volume + sheer volume of constantly-inserted data, in my case) on sys tables and objects, so those operations can deadlock on things like reindexing and DTCCs -- and in one case, specifically with a user in SSMS (of all things) trying to browse views in the Object Explorer (somebody needs to tell those guys about READPAST). Again, your mileage may vary.

In contrast, the sp_rename works well for me at scale: it gives you control over the locking and the scope of it. To solve the blocking issue prior to the swap, try it as shown below. At face value this would seem to have the same scale issue at high volume... but I haven't seen it in practice. So, works for me... but again, everybody's needs and experiences are different.

DECLARE @dummylock bit 
BEGIN TRANSACTION 
BEGIN TRY
   -- necessary to obtain exclusive lock on the table prior to swapping
   SELECT @dummylock = 1 WHERE EXISTS (SELECT 1 FROM A WITH (TABLOCKX))
   -- may or may not be necessary in your case
   SELECT @dummylock = 1 WHERE EXISTS (SELECT 1 FROM B WITH (TABLOCKX))
   exec sp_rename 'A', 'TEMP'
   exec sp_rename 'B', 'A'
   exec sp_rename 'TEMP', 'B'
   COMMIT TRANSACTION
END TRY
BEGIN CATCH
   -- other error handling here if needed
   ROLLBACK TRANSACTION 
END CATCH

回答4:

What happens to IpToCountryOld? Do you throw it away? In which case, why not truncate IpToCountry and import my new data.

If you need to keep the data, how about storing the load date on the table and storing the "current" load date somewhere to be used in a WHERE clause? Then you switch the current date when the data is successfully loaded.

You don't say which DB you're using, so I don't know how much use this is, but do you have any stored procedures that reference the table? Be warned that on some platforms SPs are compiled using internal references to tables that will not change with a rename, so there's a risk that SPs won't pick up your new data without a recompile. The same can be true for views and stored parsed queries.

回答5:

Can you not do the import to the one table during off hours?

Or why not just do a data update, ie update the existing records and add any new ones on a record by record basis as you loop to import the data. This would allow the table to stay live and reduce the overall impact of adding and dropping full tables.

What is the structure of the data being imported, table design, format, PK, etc? From that we may be able to give you a better answer.

回答6:

Just ran into a similar issue working on a staging table that had issues scaling with proper locks.

Everywhere your table is referenced you could call a stored procedure asking for the table name.

The stored procedure would optionally create the new table(s) or return the old tables depending on the parameters provided.