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