SQL Server Performance

Tuning question

Discussion in 'SQL Server 2005 Integration Services' started by akus, Jun 17, 2008.

  1. akus New Member

    I am building customer detail de-duping process in SSIS. While flowing say 2000 rows against 20 or so lookups (lookup set averaging 200000 rows) the performance is great at 45 seconds. This is fine for the periodic increments. However, I have requirement of de-duping the existing set, and this means that I need to compare 800000 records against the same lookup criteria. This process ran for 13 hours, and is not acceptable at this time.
    What can I do - surely there must be a solution other that manually mangaing the flow in smaller sets i.e. implementing a loop for throttling the set...
    Cheers, Aku
  2. satya Moderator

    Can you take advantage of staging table while processing this de-duping check process?
    In this case if the base data is constant then you can build up a temporary index and by reorganizing the same will get you better performance while calculations are made.
  3. akus New Member

    Thank you for suggestion Satya,
    My staging table had already appropriate indexes on it, and it turned out to be my output task for matching rows, which was the problem.
    Since I used same Match FK output table multiple times in the same dataflow, and with larger set, multiple match condition being met, destination table locked. I expected SSIS to hadle this, but did hang indefinitely (taskes painted yellow) instead.
    Not having tested this, I presume that by unticking "table lock" on "OLE DB Destination" editor, this issue would be solved(?). I decided to go with unioning all results.

Share This Page