Issue with ALTER Partition SWITCH to load data

[PartitionLearning]

I have table which is partitioned by acquisition date

Table Structure (TableName : Inventory)

Vendor_Name [varchar](80) NULL,
Model_Name [varchar](80) NULL,
AcquisitionDate [datetime] NOT NULL,
Qty [bigint] NOT NULL

Partition Function For Inventory Table :

CREATE PARTITION FUNCTION [Inventory_PF_New](datetime) AS RANGE LEFT FOR VALUES
(N'2012-07-01T00:00:00.000', N'2012-08-01T00:00:00.000',
N'2012-09-01T00:00:00.000', N'2012-10-01T00:00:00.000',
N'2012-11-01T00:00:00.000', N'2012-12-01T00:00:00.000',
N'2013-01-01T00:00:00.000', N'2013-02-01T00:00:00.000',
N'2013-03-01T00:00:00.000', N'2013-04-01T00:00:00.000',
N'2013-05-01T00:00:00.000', N'2013-06-01T00:00:00.000',
N'2013-07-01T00:00:00.000', N'2013-08-01T00:00:00.000',
N'2013-09-01T00:00:00.000', N'2013-10-01T00:00:00.000',
N'2013-11-01T00:00:00.000', N'2013-12-01T00:00:00.000',
N'2014-01-01T00:00:00.000', N'2014-02-01T00:00:00.000',
N'2014-03-01T00:00:00.000', N'2014-04-01T00:00:00.000',
)

I receive daily feed flat files which can have acquisition date up to 3 months older. I need to process these files and load data to actual partition table (Inventory Table).

Using Bulk Insert/SSIS I can simply load these files into temporary table but How Do I use ALTER SWITCH to SWITCH daily received data into actual partition table? I know
The receiving partition must be empty and when I load data into temporary table I could have three months of data. So, I can't simply perform ALTER SWITCH IN.

Any thoughts How do I implement this. My requirement is to load daily received data quickly and my daily feed can have three months of data?

Appreciate your help in advance!

Thanks,

 

[PartitionLearning]

I would like shed more lights on this, My table is partitioned by month using acquisition date and I receive data feed which can have 3 to 4 months of data. How do I use ALTER SWITCH to load this data into actual partition table?

 

[philhege]

In order to use SWITCH, your partition scheme has to match the boundaries of the data you upload. So, if you have a file with one day's worth of data, the partition has to be based on individual days.

Because your load data set exceeds the boundaries of your scheme, you can't SWITCH it in.

-----------
With business clients like mine, you'd be better off herding cats.

 

[gk53]

YOu should be carreful when partitionning table, becouse witn partition by date you can load data for different days at the same time and it will spped up loading process (id you are using SSIS), but it could singnificantly increase query execution time when you selection data AcquisitionDate between date1 and date2

 

[philhege]

I've heard both sides of the "partitioning degrades performance" debate. Do you have a citation for your statement?

-----------
With business clients like mine, you'd be better off herding cats.

 

[gk53]

It is just from my experience I have partitioned table about 200,000,000 records with partition by element_cd and see performance problem when run select where element_CD BETWEEN val1 and val2 it takes a few minutes to get results, but if I make a table variable with all values between val1 and val2 and join original table with table variable I have result in 0 seconds...

 

[philhege]

I don't believe that's entirely attributable to partitioning. I have the same behavior with an unpartitioned value that I join to an index column. Have you run the straight query on an un-partitioned version of the table?

-----------
With business clients like mine, you'd be better off herding cats.

 

[gk53]

No this table must be partitioned. it is reloads big amount of data every night and to reduce update time I need to run that as paralel tasks in SSIS. With partitions by element_cd I can update different codes (deletes/inserts/updates) at the same time...

 

[PartitionLearning]

Existing table structure is in production and there are number of reports running..in my case performance is much better because all the reports use acquisition date because current table is partitioned by month using acquisition date

I can't change the existing structure...How about if i use following approach?

Create new empty staging table same structure compare to actual source table and also partition on same schema
then I will load this staging table using SSIS and
next I will try to use switch IN.

I probably have to switch out appropriate partition based on data I receive on in my daily feed and then merge with staging table before I perform SWITCH IN

 

[philhege]

I probably have to switch out appropriate partition based on data I receive on in my daily feed and then merge with staging table before I perform SWITCH IN

That defeats the purpose of a SWITCH. You want existing data to be available while the partition activity is going on. If you SWITCH out existing data, you make it unavailable to the application. Also, your merge routine would take just as long as loading it straight.

-----------
With business clients like mine, you'd be better off herding cats.

 

[PartitionLearning]

Yes that's correct, so now I am selecting records from live table and inserting into my staging table...so let say If i receive 2 months of data in my daily feed I will load staging table first and then select 2 months of data from live table and insert them into staging table --> then because my staging table is partitioned on same schema I can then switch IN...

Just before SWITCH IN I will switch out those partitions from live table and switch IN using staging partition table.

I don't see any other way to implement this....(Current loading process which is in production loading data directly into live table and we are having performance issues that's why I am trying to implement this using SWITCH)