Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: SSIS - OLE DB Source to Flat File SLOW

    So I have this SSIS package - nothing too clever - it basically just dumps a load of data out to text files.

    Step 1: bit of scripting to create a folder and change the connection managers for the destination files to be within this new folder.

    Step 2: dump out lots of data using Data Flow Tasks

    Each data flow task follows the same format:
    OLE DB source (a single SQL view)
    Flat File Destination (pipe delimited CSV)

    However, one of my steps seems to run stupidly, painfully slow compared to the others!

    Two comparable steps have the same format (same columns, same data types, same everything) just different data.

    One step returns ~3M rows and in SSMS executes in about a minute.
    Another step returns ~1M rows and in SSMS executes in around 25 seconds.

    In SSIS the 3M step takes almost no time to dump out.
    The smaller data set (1M) dumps out about 10K rows every 30 seconds. I have yet to let it finish.

    Any ideas on the cause?

    I had this problem in this package in the past with a different step and all I did was recreate it from scratch and that resolved it. I have done this with the slow step to no avail.

    Tearing my hair out on this!
    George
    Home | Blog

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Deleted the destination on the slow task and it is still taking forever...
    Recreated the step from scratch, no destination, same issue...
    New package, created only this step, no destination, same issue...
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Is the problem entirely in SSIS, or is the query churning in the background in SQL server?

    Any extra lookups that SSIS has to do before writing to the CSV file? Or is everything required returned by the query?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Nothing extra - simple view dumped to CSV. Zero transformations, nothing fancy.

    Is there a way of tracing just BIDS stuff?
    I guess I could just set up a new user and profile them?
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You might be able to trace BIDS by keying off of the program name.

    I suspect there is some extra or missing checkbox when you compare the two data pump tasks. Dump the package out to XML, and compare the two sections for the datapump tasks, to see how similar they are. This is the single biggest problem I have with GUI programming.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Going to end up re-writing without SSIS at this rate. Just prefer having it in SSIS for a maintainability by others perspective. KISS, right?

    Have fudged the output for now by dumping from SSMS. Don't have to run it again for a week or so might have to back burner this. Dozen other things have cropped up and I want to go home at a reasonable hour today.
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Ever play with your BatchSize and BufferMaxRows? Or my favorite "RunInoptimizedMode"? ByPassValidation can help you skip some steps too. Just curious what your file size is for 10K records.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Mild update. Running as a SQL Agent task appears to "fix" the issue.
    Sorry Thrasy, missed your reply before.
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    It's ok. I am really not that deep on SSIS or as deep as I should be. I would say be wary of intermittent performance issues that just go away, because they are likely to reappear and most likely in production and when you have no time to deal with them.

    query tuning is my only cool trick. I took a runaway 9 hour process down to 22 seconds today, and I could not use any additional indexing because it was a read only SAN snapshot. So it was just all SQL.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To be honest I have too much on that I am happy to wait for it to become a problem. I semi-actively monitor the job execution times so if it sneaks over 10 minutes total then that's when I will look to reconsider. Until then I will enjoy the ignorance.

    Reminds me of a job interview question: "how would you improve the performance on this query?"
    A: "first off, I would rewrite it."
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •