Best Option for Working with Files in SSIS

  • Comments posted to this topic are about the item Best Option for Working with Files in SSIS

  • Thanks for sharing the results of this experiment.
    I'd be extremely interested in discovering WHY things slowed down when they were ran in parallel.
    Is it possible that the storage system was simply overwhelmed? Disk seeks were hammering your disk because files were in two different places?

  • Ahhh... don't your results and conclusion contradict themselves?

    Results:
    "The File System task is the worst option and the slowest one with 130 seconds"

    Conclusion:
    "As you can see, the fastest solution is the File System Task."

  • Brent Leslie - Sunday, May 14, 2017 4:19 AM

    Ahhh... don't your results and conclusion contradict themselves?

    Results:
    "The File System task is the worst option and the slowest one with 130 seconds"

    Conclusion:
    "As you can see, the fastest solution is the File System Task."

    It IS a bit confusing until you realize that he was speaking of the non-parallel versions in his conclusion.  It would have been handy if he pointed that out in that statement but he did clarify a bit further on in the Conclusion.

    When we talked about the tasks in parallel, we did not get a better performance except with the cmd. In few words, in this case, having the process in parallel does not guarantee a better performance

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • @calbimonte.Daniel,

    Except for the previously stated bit of confusion in the Conclusion of the article, I liked the article.  It was simple, straight forward, and the experiment appears to be repeatable for any one that takes the time to try it.  Thanks for taking the time to post it, Daniel.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • I'm curious what the impact of reading/writing to the same disk had on the parallel execution. Would results improve by writing to another physical disk?

  • If there are multiple files, an analysis of ROBOCOPY would be interesting with various /MT settings.

  • Might also want to consider BCP and or bulk insert, sometimes it's really fast on imports..... have to see whats faster in situations with bigger files.

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

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