Post reply

Incremental load pattern – best practice

  • July 2, 2015 at 7:52 am

    #302144

    Hi folks,

    I am trying to improve the performance of a SSIS package.

    To simplify let’s say I have the following pattern:

    The current design use the primary key of the source table as clustered primary key of the target table, and the temporary table (##Updates). I think this reduces the performance of the inserts, but of course helps the update step, because these keys are used in the join criteria.

    I am thinking in several possibilities:

    -Use a surrogated key in the target table instead of the Id from the source. Then store the Id from the source in another column and index this column. Drop the index before the inserts and build it before the update.

    -Leave it as it is, but dropping the clustered index before the inserts and build it again for the update.

    -Have no primary keys in the target table. Instead use a HashValueBK which is already available in the source table + a timestamp.

    - I am not sure about what would happen with the performance of the lookup step.

    I forgot to mention that I have to historized the facts. The source and the target tables have a kind of row versioning with Valid From, Valid To and Current flag columns. New Valid From's from the source have to be transferred to the target as new records but also new Valid To's should be considered (usually the first ones are inserts and the former updates)

    Any experience you can share with me regarding this topic would be really appreciated.

    Kind Regards,

    Paul Hernández

  • July 3, 2015 at 3:50 am

    #1810155

    For incremental load, I guess you should use encryption techniques to find out actual updated records.

    In case loading the updated records in temp table.

    Also please let us know your package design and which part of your package is actually taking time.

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
     🙂

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

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