Post reply

Data Flow Transformation Performance Issue

  • December 27, 2013 at 12:39 am

    #286671

    Hi,

    I am using a Data flow transformation for loading data from sql

    store procedure.

    which consist of calculation and take 1 min to show 100000 records.

    The package is taking 6 min for for loading this into csv.

    I used till now a physical to store the data created in store procedure and then retrieve from that table the package execution reduced to 2 min.

    Any other suggestion to improve this :doze:.

  • December 27, 2013 at 11:04 am

    #1676161

    Sorry, I am not able to understand your question. Please explain it clearly.

  • January 9, 2014 at 3:10 am

    #1678718

    I have a store procedure that is source for creation of my CSV, it take one minute to execute to 100000 records

    and this result set is used to preparing csv which take 6 minutes.

    FYI -The storeprocedure consist of temptable and physical table joins

    -- Now My Source is PhysicalTable for same dataset

    But when insert data of storeprocedure into physical table i.e. 100000 records in a table.

    And use this physical table source to prepare csv it take hardly a minute.

    Appreciate if there are any other solution for optimizing it.

  • January 9, 2014 at 9:37 am

    #1678850

    So are you asking: "why does it take less time for SSIS to write data to a table than it does to export it to a .csv file?" ?

  • January 10, 2014 at 12:24 am

    #1679046

    harsimranjeetsinghwasson (1/9/2014)

    I have a store procedure that is source for creation of my CSV, it take one minute to execute to 100000 records

    and this result set is used to preparing csv which take 6 minutes.

    FYI -The storeprocedure consist of temptable and physical table joins

    -- Now My Source is PhysicalTable for same dataset

    But when insert data of storeprocedure into physical table i.e. 100000 records in a table.

    And use this physical table source to prepare csv it take hardly a minute.

    Appreciate if there are any other solution for optimizing it.

    I've just read this 4 times and don't understand the question. Actually, there is no question, just a hint of what you are asking - maybe that's the problem.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • January 14, 2014 at 12:10 pm

    #1680024

    You could try playing with the defaultbuffermaxrows and defaultbuffersize settings.

    I suspect what is happening is this.

    with a stored procedure as data source, the procedure must finish executing, then it dumps ALL its rows into memory (SSIS). And then SSIS does its buffering thing for writing them out.

    With a table as a source, the data source grabs some of the rows, passes them on, and grabs the next ones.

    If you set a higher defaultbuffermaxrows, you'd still get a bigger memory spike, but you could write to the file in fewer iterations.

Viewing 6 posts - 1 through 5 (of 5 total)

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