Copy Between 2 and 3 billion rows from a heap table with no key to a new partioned table.

Question

Post reply

Copy Between 2 and 3 billion rows from a heap table with no key to a new partioned table.

mishka-723908

SSCommitted

Points: 1675
More actions
January 9, 2013 at 6:44 pm

#287456

Hello,
I need to copy Between 2 and 3 billion rows from a heap table with no identity to a new partioned table. The primary key is made up of a smallint value that represents a Month and BIGINT a loan_number.
--The partion key is a small int value.
--I have tried the approach of selecting by the partition key, but as you can imagine because of the amount of data each select can take quite some time.
--I also tried using an import\export task it started moving some data, but it is also extremly slow.
--The new table has 24 partitions. each partition represents 24 values of the partition key from 0 - 552
Here is the definition of the partitioned table. The heap table is the same except it does not have the identity column created_by\date attributes.
--Partitioned table
CREATE TABLE dbo.qc_partition
(
qc_partition_idBIGINT IDENTITY(1,1)
,pool_idCHAR(3)NOT NULL
,deal_noCHAR(5)NOT NULL
,group_noCHAR(3)NOT NULL
,servicerVARCHAR(4)NULL
,internal_loan_idCHAR(6)NOT NULL
,external_loan_idVARCHAR(18)NOTNULL
,last_int_pSMALLDATETIMENULL
,balanceMONEYNULL
,int_rateNUMERIC(6, 3)NULL
,total_payment_dueMONEYNULL
,sched_principalMONEY NULL
,sched_mnth_pMONEYNULL
,mba_statVARCHAR(1)NULL
,ots_statVARCHAR(1)NULL
,payment_histVARCHAR(12)NULL
,exceptionVARCHAR(1)NULL
,start_dateSMALLDATETIMENULL
,end_dateSMALLDATETIMENULL
,fc_end_typVARCHAR(1)NULL
,payoff_dSMALLDATETIMENULL
,payoff_rVARCHAR(1)NULL
,sell_dateSMALLDATETIMENULL
,inv_balMONEYNULL
,next_percentNUMERIC(6, 3)NULL
,loss_valMONEYNULL
,net_rateNUMERIC(7, 4)NULL
,periodSMALLINTNOT NULL
,file_nameCHAR(8)NOT NULL
,created_byVARCHAR(70)NOT NULLDEFAULT CURRENT_USER
,created_dateDATETIMENOT NULLDEFAULT (GETDATE())
) ON partition_scheme_qc (period)
One more question about the indexing. I created the following index, but was wondering if anyone had better idea on that. Also, What would be the best way to create a clustered-index\primary key here.
CREATE NONCLUSTERED INDEX IX_period_external_loan_id
ON qc_partition (period, external_loan_id)
ON partition_scheme_qc (period)
GO
Does anyone have any ideas?
Thank you for your help

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Related post. Direct replies here:

http://www.sqlservercentral.com/Forums/Topic1405106-146-1.aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 2

This is a duplicate post. Posting the same question over and over isn't the thing to do here. All it does is split resources and tick people off.

Please don't post any answers on this thread... instead, post them where this seems to have started....

http://www.sqlservercentral.com/Forums/Topic1405106-146-1.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)