0

I have non-partitioned production table with 100 million records for last 2 years. The table has the clustered and non clustered indexes.

I want to insert daily about 150K records from external server. The load will happen early in the morning when there's no user activity. We decided to reload entire table once a week on Sunday due to the business rules. I would like to partition the data by 3 months and I don't need to archive the deleted data.

I need to rebuild entire table with 2 years of data on Sunday, delete records from last week Sunday and reload them daily for prior week plus this week to date(I.e. load 8 days on Monday, 9 - on Tuesday, 10 - on Wednesday etc until full rebuild on Sunday).

What strategy could you recommend? Do I need to use the staging table?

Here is my table structure:

CREATE TABLE [dbo].[SALES_PROD]

(
    [PROD_DIM_ID] [decimal](18, 0) NOT NULL,
    [UPC] [decimal](18, 0) NULL,
    [GREG_DATE] [date] NOT NULL,
    [LOC_DIM_ID] [int] NOT NULL,
    [STORE_NBR] [int] NULL,
    [FIL_LOC_DIM_ID] [int] NOT NULL,
    [FIL_STORE_NBR] [int] NULL,
    [GROSS_SALES_UNITS] [decimal](15, 0) NULL,
    [GROSS_SALES_AMT] [decimal](15, 2) NULL,
    [GROSS_SALES_POS_AMT] [decimal](15, 2) NULL,
    [OCD_CD] [smallint] NOT NULL,
    CONSTRAINT [PK_OC_SALES_PROD_LOC_DAY] PRIMARY KEY NONCLUSTERED 
(
    [PROD_DIM_ID] ASC,
    [GREG_DATE] ASC,
    [LOC_DIM_ID] ASC,
    [FIL_LOC_DIM_ID] ASC,
    [OCD_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

--CLUSTERED Index

CREATE CLUSTERED INDEX [IX_GREG_DATE] ON [dbo].[SALES_PROD]
(
    [GREG_DATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
  • Questions such as this can be quite complex. It would help if you showed the create table statement for your table and indexes, also information like how long is your window to load data, and if there is any other maintenance going on during your window. – mendosi Dec 2 '16 at 0:28
  • 1
    If you are reloading weekly and not using SWITCH to purge/load new data, why partition at all? What is your reason for partitioning? – Dan Guzman Dec 2 '16 at 2:19
  • Hi Dan, I'm thinking about partition for 2 reasons: speed up the load and increase the query performance when reading the data. – Leo Dec 2 '16 at 12:52
0

Switch between 2 tables.

Assume you currently have 2 tables, T which holds an up-to-date data and T_STG.

  • Truncate T_STG.
  • Drop non-clustered indexes from T_STG.
  • Load T_STG with the newest data.
  • Build the non-clustered indexes for T_STG.
  • Rename T to T_TMP.
  • Rename T_STG to T.
  • Rename T_TMP to T_STG.

P.s.
Could you please share what is the main reason for the reloading of the full table?

  • Hi Dudu, thank you for the suggestion. It won't work because I need to add the data to T table daily not replace it with stage. The main reason for reloading the entire table once a week is to correct any switches for historical data during the week. Sometimes it happens on external server. – Leo Dec 2 '16 at 13:05
  • (1) I don't see the issue here. The daily loading should be done directly into T. Only on the full reload you involve the staging table. (2) why not build only the last week or few weeks? why rebuilding data from a year ago? Do you really think it would change over time? – David דודו Markovitz Dec 2 '16 at 13:19
  • Yes, we need to reload entire year on weekend because the data is shifting historically. So, no partition suggestion? – Leo Dec 4 '16 at 23:43
  • If I understand correctly you are not rebuilding the whole table but 1 year out of 2. In that can we would want to use partitions. – David דודו Markovitz Dec 5 '16 at 6:36
  • The latest task is to rebuild entire table with 2 years of data on Sunday , delete records from last week Sunday and reload them daily(I.e. If today is Wednesday then reload data for last 10 days etc.) – Leo Dec 5 '16 at 12:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.