Ygthkb

Removing whitespace between consecutive numbers

Why was Lupin comfortable with saying Voldemort's name?

Why is it that Bernie Sanders is always called a "socialist"?

Short story where statues have their heads replaced by those of carved insect heads

Why don't key signatures indicate the tonic?

What language shall they sing in?

Globe trotting Grandpa. Where is he going next?

Is there any risk in sharing info about technologies and products we use with a supplier?

How to politely refuse in-office gym instructor for steroids and protein

Is the child responsible for the Parent PLUS Loan when the parent has passed away?

What will happen if I transfer large sums of money into my bank account from a pre-paid debit card or gift card?

Eww, those bytes are gross

Is there a verb that means to inject with poison?

Boss asked me to sign a resignation paper without a date on it along with my new contract

What is a good reason for every spaceship to carry a weapon on board?

Can I announce prefix 161.117.25.0/24 even though I don't have all of /24 IPs

In Linux what happens if 1000 files in a directory are moved to another location while another 300 files were added to the source directory?

How to assess the long-term stability of a college as part of a job search

Why would space fleets be aligned?

What makes papers publishable in top-tier journals?

Does diversity provide anything that meritocracy does not?

How do you funnel food off a cutting board?

How does one write from a minority culture? A question on cultural references

How do you voice extended chords?

Large table partition

SQL large table designSlow Performance Inserting Few Rows Into Huge TableParent-Child Tree Hierarchical ORDERHow to partition a very large table with limited db space?deteriorating stored procedure running timesPrimary keys, clustered indexes and partitioningStoring NULL versus storing '' in a varchar columnPartition a table at creationSQL Server 2016 concurrency limitations? Tuning for db concurrencyCreate Range Partition on existing large MySQL table

0

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]

sql-server sql-server-2008 partitioning

share|improve this question

edited Dec 7 '16 at 14:29

Paweł Tajs

1,206513

asked Dec 1 '16 at 23:11

LeoLeo

11

bumped to the homepage by Community♦ 13 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  

add a comment | 

0

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]

sql-server sql-server-2008 partitioning

share|improve this question

edited Dec 7 '16 at 14:29

Paweł Tajs

1,206513

asked Dec 1 '16 at 23:11

LeoLeo

11

bumped to the homepage by Community♦ 13 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  

add a comment | 

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]

sql-server sql-server-2008 partitioning

share|improve this question

edited Dec 7 '16 at 14:29

Paweł Tajs

1,206513

asked Dec 1 '16 at 23:11

LeoLeo

11

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]

share|improve this question

edited Dec 7 '16 at 14:29

Paweł Tajs

1,206513

asked Dec 1 '16 at 23:11

LeoLeo

11

share|improve this question

edited Dec 7 '16 at 14:29

Paweł Tajs

1,206513

asked Dec 1 '16 at 23:11

LeoLeo

11

edited Dec 7 '16 at 14:29

Paweł Tajs

1,206513

edited Dec 7 '16 at 14:29

Paweł Tajs

1,206513

asked Dec 1 '16 at 23:11

LeoLeo

11

asked Dec 1 '16 at 23:11

LeoLeo

11

1 Answer
1

active

oldest

votes

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?

share|improve this answer

answered Dec 2 '16 at 6:33

David דודו MarkovitzDavid דודו Markovitz

2,903518

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

 | 
show 4 more comments

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f156961%2flarge-table-partition%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

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?

share|improve this answer

answered Dec 2 '16 at 6:33

David דודו MarkovitzDavid דודו Markovitz

2,903518

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

 | 
show 4 more comments

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?

share|improve this answer

answered Dec 2 '16 at 6:33

David דודו MarkovitzDavid דודו Markovitz

2,903518

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

 | 
show 4 more comments

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?

share|improve this answer

answered Dec 2 '16 at 6:33

David דודו MarkovitzDavid דודו Markovitz

2,903518

share|improve this answer

answered Dec 2 '16 at 6:33

David דודו MarkovitzDavid דודו Markovitz

2,903518

answered Dec 2 '16 at 6:33

David דודו MarkovitzDavid דודו Markovitz

2,903518

answered Dec 2 '16 at 6:33

David דודו MarkovitzDavid דודו Markovitz

2,903518