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 2008 Forums  Transact-SQL (2008)  Database locks

Author  Topic  Oferoh Starting Member 1 Post Posted - 2012-02-27 : 09:31:18 Hi All, I would like to share with you a problem I'm facing with SQL Server 2008 I have a very large table with partition on a Date column Every hour I'm getting new information from text files, around half a million lines that I want to insert into my historical table, In order to get best performance, I extract the specific date partition (based on the dates in my files) to a side table, Then I'm inserting to that table the new data from my files and using "SWITCH PARTITION" I return the data into the original table. This way the insert is very quicker. Altough the table is smaller it's still takes several minutes to insert to the side table. This process is backup with Transaction command and untill I do Commit my large table is locked on any paratition, even ones I'm not working on. Maybe my solution is not the correct one and there is an alternative way to do it, I will be very happy to learn and understand where I'm worng. Below is a script to build the table and the partition and a simulation of the problem, I've marked the commit command, so after you finish to run it, Please open a new window and run the following command: SELECT * FROM AggTable WITH(NOLOCK) WHERE DateOnly = '2011-01-02' This is the simulation: /****** Object: Table [dbo].[AggTable] Script Date: 09/05/2011 17:20:31 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AggTable]') AND type in (N'U')) DROP TABLE [dbo].[AggTable] GO /****** Object: Table [dbo].[AggTable] Script Date: 09/05/2011 17:20:31 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AggTable_NEW]') AND type in (N'U')) DROP TABLE [dbo].[AggTable_NEW] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AggTable_Log]') AND type in (N'U')) DROP TABLE [dbo].[AggTable_Log] /****** Object: PartitionScheme [PS_Daily] Script Date: 09/05/2011 17:19:05 ******/ IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'PS_Daily') DROP PARTITION SCHEME [PS_Daily] GO /****** Object: PartitionFunction [PF_Daily] Script Date: 09/05/2011 17:19:17 ******/ IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'PF_Daily') DROP PARTITION FUNCTION [PF_Daily] GO /****** Object: PartitionFunction [PF_Daily] Script Date: 09/05/2011 17:13:03 ******/ CREATE PARTITION FUNCTION [PF_Daily](DATE) AS RANGE LEFT FOR VALUES ( N'2011-01-01', N'2011-01-02', N'2011-01-03', N'2011-01-04', N'2011-01-05') GO /****** Object: PartitionScheme [PS_Daily] Script Date: 09/05/2011 17:13:36 ******/ CREATE PARTITION SCHEME [PS_Daily] AS PARTITION [PF_Daily] ALL TO ([PRIMARY]) GO CREATE TABLE AggTable( DateOnly Date, AdvertiserId int, ActioinId int, Total Int) ON [PS_Daily](DateOnly) CREATE TABLE AggTable_NEW( DateOnly Date, AdvertiserId int, ActioinId int, Total Int) ON [PS_Daily](DateOnly) CREATE TABLE AggTable_Log( DateOnly Date, AdvertiserId int, ActioinId int, Total Int) GO INSERT INTO AggTable SELECT '2011-01-01',150,100,50 GO 20 GO INSERT INTO AggTable SELECT '2011-01-02',650,100,64 GO 43 GO INSERT INTO AggTable SELECT '2011-01-03',23,245,99 GO 66 GO INSERT INTO AggTable SELECT '2011-01-04',243,34,12 GO 11 GO INSERT INTO AggTable SELECT '2011-01-05',132,34,132 GO 1999 GO INSERT INTO AggTable_Log SELECT '2011-01-05',32,34,1342 GO 3455 SELECT CONVERT(DATE,prv.value) AS [Date] , CAST(p.rows AS float) AS [RowCount] FROM sys.tables AS tbl with(nolock) INNER JOIN sys.indexes AS idx with(nolock) ON idx.object_id = tbl.object_id and idx.index_id < 2 INNER JOIN sys.partitions AS p with(nolock) ON p.object_id=CAST(tbl.object_id AS int) INNER JOIN sys.partition_range_values AS prv with(nolock) ON prv.boundary_id = p.partition_number WHERE tbl.name = 'AggTable' AND CAST(p.rows AS float) > 0 SELECT COUNT(*) FROM AggTable_Log BEGIN TRANSACTION ALTER TABLE AggTable SWITCH PARTITION $PARTITION.PF_daily('2011-01-05') TO AggTable_NEW PARTITION $PARTITION.PF_daily('2011-01-05') SELECT tbl.name, CONVERT(DATE,prv.value) AS [Date] , CAST(p.rows AS float) AS [RowCount] FROM sys.tables AS tbl with(nolock) INNER JOIN sys.indexes AS idx with(nolock) ON idx.object_id = tbl.object_id and idx.index_id < 2 INNER JOIN sys.partitions AS p with(nolock) ON p.object_id=CAST(tbl.object_id AS int) INNER JOIN sys.partition_range_values AS prv with(nolock) ON prv.boundary_id = p.partition_number WHERE tbl.name IN( 'AggTable' , 'AggTable_NEW' ) AND CAST(p.rows AS float) > 0 INSERT INTO AggTable_NEW (DateOnly,AdvertiserId,ActioinId,Total) SELECT * FROM AggTable_Log ALTER TABLE AggTable_NEW SWITCH PARTITION $PARTITION.PF_daily('2011-01-05') TO AggTable PARTITION $PARTITION.PF_daily('2011-01-05') SELECT tbl.name, CONVERT(DATE,prv.value) AS [Date] , CAST(p.rows AS float) AS [RowCount] FROM sys.tables AS tbl with(nolock) INNER JOIN sys.indexes AS idx with(nolock) ON idx.object_id = tbl.object_id and idx.index_id < 2 INNER JOIN sys.partitions AS p with(nolock) ON p.object_id=CAST(tbl.object_id AS int) INNER JOIN sys.partition_range_values AS prv with(nolock) ON prv.boundary_id = p.partition_number WHERE tbl.name IN( 'AggTable' , 'AggTable_NEW' ) AND CAST(p.rows AS float) > 0 --COMMIT