Sql-server – Creating indexes after data load Vs Before data load in a large table

indexsql serverssis

I have a huge sql server table of size 430 GB on one server A.

I needed to copy the table as it is to a new server B, so I created an empty table on the server B and moved all data from A to B using SSIS package/job – this took me almost 30+ hours.

Now the data copy is finished and the big task is to build the indexes.

Was I correct in taking this approach, or should I have loaded the data in already indexed table on server B ?

—————————–TABLE—————

CREATE TABLE [CCTSwapForward].[SWAP_DATA](
    [SwapData_ID] [int] NOT NULL,
    [Contract_ID] [varchar](20) NOT NULL,
    [DataSourceName] [varchar](10) NULL,
    [Direction] [varchar](5) NULL,
    [StatusDescription] [varchar](50) NULL,
    [TargetCurrencyCode] [varchar](5) NULL,
    [SettlementCurrencyCode] [varchar](5) NULL,
    [TargetAmount] [float] NULL,
    [SettlementAmount] [float] NULL,
    [ConfirmationNo] [varchar](20) NULL,
    [ItemNo] [int] NULL,
    [ExpiryDate] [datetime] NULL,
    [Original_TargetAmount] [float] NULL,
    [Original_SettlementAmount] [float] NULL,
    [PartyID] [varchar](20) NULL,
    [PrimaryAssetClass] [varchar](50) NULL,
    [SecondaryAssetClass] [varchar](5) NULL,
    [EffectiveDate] [datetime] NULL,
    [PriceNotationType] [varchar](5) NULL,
    [PriceNotationValue] [varchar](5) NULL,
    [Addn_PriceNotationType] [varchar](5) NULL,
    [Addn_PriceNotationValue] [varchar](5) NULL,
    [ProductIDPrefix] [varchar](5) NULL,
    [ProductIDValue] [varchar](50) NULL,
    [AllocationIndicator] [varchar](20) NULL,
    [ExecutionTS] [datetime] NULL,
    [VerificationType] [varchar](50) NULL,
    [ExecutionVenuePrefix] [varchar](10) NULL,
    [ExecutionVenue] [varchar](50) NULL,
    [ClearingDCOValue] [varchar](50) NULL,
    [Collateralized] [varchar](25) NULL,
    [LastReporteddt] [datetime] NULL,
    [Confirmationdt] [datetime] NULL,
    [ConfirmationType] [varchar](25) NULL,
    [Valuationdt] [datetime] NULL,
    [MTMValue] [float] NULL,
    [MTMCurrency] [varchar](3) NULL,
    [ReportingJurisdiction] [varchar](15) NULL,
    [ValueDate] [datetime] NULL,
    [ExchangeRate] [float] NULL,
    [TradePartyRole] [varchar](50) NULL,
    [TradePartyPrefix] [varchar](50) NULL,
    [TradePartyValue] [varchar](20) NULL,
    [ReportingObligation] [varchar](15) NULL,
    [USPersonIndicator] [bit] NULL,
    [FinEntityIndicator] [bit] NULL,
    [ClientOrder_ID] [int] NULL,
    [OrderDetail_ID] [int] NULL,
    [Batch_ID] [int] NOT NULL,
    [Status_ID] [int] NULL,
    [initdt] [datetime] NULL,
    [initid] [int] NULL,
    [upddt] [datetime] NULL,
    [updid] [int] NULL,
    [ReportingPartyLEI] [varchar](20) NULL,
    [ProcessCenter] [varchar](50) NULL,
    [TargetAmount_NDec] [int] NULL,
    [SettlementAmount_NDec] [int] NULL,
    [ExchangeRate_NDec] [int] NULL,
    [MTMRate] [float] NULL,
    [LastUpdatedTS] [datetime] NULL,
    [Client_ID] [int] NULL,
    [Office_ID] [int] NULL,
    [IsInterafiliate] [bit] NULL,
    [TreasuryToBranchRate] [float] NULL,
    [ActualValueDate] [datetime] NULL,
    [SpreadRevenueInSettlementCurrency] [float] NULL,
    [SettlementToUSDRate] [float] NULL,
    [ReportingCurrencyToUSDRate] [float] NULL,
    [TradeCurrencyToUSDRate] [float] NULL,
    [BranchCurrency] [varchar](3) NULL,
    [FirstTradeDate] [datetime] NULL,
    [Action] [varchar](10) NULL,
    [TransactionType] [varchar](25) NULL,
    [LifeCycleEvent] [varchar](25) NULL,
    [TPDomicile] [varchar](200) NULL,
    [TP1Branch] [varchar](50) NULL,
    [TP2FinancialJurisdiction] [varchar](10) NULL,
    [TP2NonFinancialJurisdiction] [varchar](10) NULL,
    [MasterAgreementDate] [datetime] NULL,
    [ReportingDelegation_ID] [int] NULL,
    [RelatedClientOrder_ID] [int] NULL,
    [RelatedOrderDetail_ID] [int] NULL,
    [ReportingDelegationModel] [nvarchar](100) NULL,
    [ExtractDatetimeUTC] [datetime] NOT NULL,
    [IsNDF] [bit] NULL,
    [FixingDate] [datetime] NULL,
    [SettlementExchangeBasis] [varchar](7) NULL
) ON [PRIMARY]

--------------------INDEX---------------------------



USE [ODS]
GO

CREATE UNIQUE NONCLUSTERED INDEX [idx_Batch_ID_SwapData_ID] ON [CCTSwapForward].[SWAP_DATA]
(
    [Batch_ID] ASC,
    [SwapData_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


USE [ODS]
GO

CREATE UNIQUE NONCLUSTERED INDEX [idx_Contract_ID_SwapData_ID] ON [CCTSwapForward].[SWAP_DATA]
(
    [Contract_ID] ASC,
    [SwapData_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


USE [ODS]
GO

CREATE UNIQUE CLUSTERED INDEX [idx_SWAPDATA_ID] ON [CCTSwapForward].[SWAP_DATA]
(
    [SwapData_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Best Answer

Given that your table is very wide, and the indexes very narrow, creating non-clustered indexes on the table following the load should be preferred.

In this instance I would have:

Create the new table with the Clustered Index in place - this is because the process of converting a heap into a clustered index is computationally expensive.
Load the data into the table, in the order of the clustered index SwapData_ID
Using BULK INSERT (ensuring the operation is minimally logged), load into the table
Create the non-clustered indexes

The above approach should be optimal given your scenario.

There is of course then other questions around:

Data drift (will the source data change during your load process? Do these changes need to be taken across)

DR (is log shipping enabled? In this case the recovery model may need to be changed to bulk-logged)

Log file sizing (You'll need to ensure your log file is big enough to accommodate for the non-clustered index creations)

Presizing the database (ensuring it doesn't auto-grow during the load)

but these all seem to be slightly outside the context of what you're asking.

Related Solutions

Sql-server – Indexing – Uniqueidentifier Foreign Key or Intermediary mapping table

Ok, I am making a lot of assumptions (INT instead of VARCHAR(50) being one of them) with this answer, so feel free to correct me if needed. The problem with option B is that it introduces a new join to relate Users to Alerts without any real added benefit. If joining on the UserID, it is best to index the UserID, so you can utilize seeks for your joins.

For Option A, UserID will be the clustering key (index key for the clustered index) on the Users table. UserID will be a nonclustered index key on Alerts table. This will cost 16 bytes per Alert.

For Option B, UserID will be the clustering key on the Users table. UserId will probably be the clustering key in UserMap too, to make joining more efficient. UserKey (assuming this is an INT) would then be a nonclustered index key on the Alerts table. This will cost 4 bytes per Alert. And 20 bytes per UserMap.

Looking at the big picture, one relationship, for Option A, costs 16 bytes of storage, and involves 1 join operation. Whereas, one relationship, for Option B, costs 24 bytes of storage, and involves 2 join operations.

Furthermore, there are a possibility of 340,282,366,920,938,000,000,000,000,000,000,000,000 uniqueidentifiers and only 4,294,967,296 INTs. Implementing a uniqueidentifier to INT map for a this type of relationship could cause unexpected results when you start reusing INTs.

The only reason for creating this type map table, is if you plan on creating a Many to Many relationship between Users and Alerts.

Taking all of this into consideration, I would recommend Option A.

I hope this helps,

Matt

Sql-server – Differences Between Two Different Create Index Commands

It boils down to looking what the default values are. Lets break this down:

CREATE UNIQUE NONCLUSTERED INDEX [DEID_MAP_IDX1] ON [dbo].[DEID_MAP]

nonclustered is specified here. The default (i.e. nothing specified) is nonclustered. So unless clustered is specified it will default to nonclustered. So that's the same in both scripts.

[dbo] is specified here explicitly. As for the second un-specified CREATE INDEX then it all depends on what the current user's default schema is. Only you can answer that at the moment, so that may or may not default to dbo.

WITH (
    PAD_INDEX  = OFF, 
    STATISTICS_NORECOMPUTE  = OFF, 
    IGNORE_DUP_KEY  = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]

PAD_INDEX: the default is OFF, so unspecified will be the same in the second script as it is in the first.

STATISTICS_NORECOMPUTE: the default is OFF, so the second script unspecified has the same value.

IGNORE_DUP_KEY: the default is OFF, so the second CREATE INDEX is identical with this parameter.

ALLOW_ROW_LOCKS: the default is ON, so the second CREATE script has the same behavior.

ALLOW_PAGE_LOCKS: the default is ON...the second script has identical behavior.

... ON [PRIMARY]: just like the default schema one, this all depends on what your default filegroup is. If PRIMARY is the default filegroup, your second CREATE INDEX script will also create the index on PRIMARY. If PRIMARY is not the default filegroup, then it will be a different filegroup, as an unspecified filegroup will go to the default filegroup.

All of this information and default values can be found on this BOL reference here.

Best Answer

Related Solutions

Sql-server – Indexing – Uniqueidentifier Foreign Key or Intermediary mapping table

Sql-server – Differences Between Two Different Create Index Commands

Related Question