Best Option for Working with Files in SSIS

  • Daniel Calbimonte

    SSCarpal Tunnel

    Points: 4757

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

  • joeyhayesus

    SSC Enthusiast

    Points: 157

    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?

  • Brent Leslie

    Old Hand

    Points: 386

    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."

  • Jeff Moden

    SSC Guru

    Points: 996832

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeff Moden

    SSC Guru

    Points: 996832

    @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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • daniel.swanson

    SSC Enthusiast

    Points: 197

    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?

  • paul.hermeneutic

    Grasshopper

    Points: 20

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

  • sqlman63

    SSC-Addicted

    Points: 479

    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