Post reply

FASTEST way to Index Table - ETL Load

  • November 25, 2018 at 9:33 pm

    #409336

    Hi All - I need to load data into a SQL DW for a POC. The goal is to reduce the time it takes to load data into tables with 100+ million records and then quickly re-create the indexes back. This is on SQL 2016 with latest CU. 

    Approach:
    Full Table Loads:
    1. Script indexes 2. Drop Indexes 3. Load data to heap 4. add Clustered index 5.Add NCI (if exists)
    Problem: Adding clustered index takes more time than loading full tables with 100+ million records, I tried redoing disk layout and db configurations but still need to find a better and fastest way to put the indexes back. What are my options? 

    Incremental Table Loads
    1. Just update records without disabling indexes if records less than 100K 2. For records over 100K disable/reenable indexes after data loads

    Note: I am staging the tables where indexes don't exist. However in the target DW Indexes are needed for the reports. So, "I want to Minimize the time it takes to add this indexes in the Target".

    Thanks again.

  • November 26, 2018 at 11:43 am

    #2014560

    sizal0234 - Sunday, November 25, 2018 9:33 PM

    Hi All - I need to load data into a SQL DW for a POC. The goal is to reduce the time it takes to load data into tables with 100+ million records and then quickly re-create the indexes back. This is on SQL 2016 with latest CU. 

    Approach:
    Full Table Loads:
    1. Script indexes 2. Drop Indexes 3. Load data to heap 4. add Clustered index 5.Add NCI (if exists)
    Problem: Adding clustered index takes more time than loading full tables with 100+ million records, I tried redoing disk layout and db configurations but still need to find a better and fastest way to put the indexes back. What are my options? 

    Incremental Table Loads
    1. Just update records without disabling indexes if records less than 100K 2. For records over 100K disable/reenable indexes after data loads

    Note: I am staging the tables where indexes don't exist. However in the target DW Indexes are needed for the reports. So, "I want to Minimize the time it takes to add this indexes in the Target".

    Thanks again.

    I would disable non clustered indexes, load data, then rebuild all indexes. 

    ALTER INDEX ALL ON ... WITH REBUILD;

    This works for either full or incremental loads.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

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

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