Post reply

Data flow performance issue for more no.of rows

  • June 6, 2011 at 11:11 am

    #251294

    Hi,

    I am having a performance issue with my package. First I have tried to load 52,000 rows from source to target tables. For each table I have one data flow task (each having the same source table). First it gave time out error for which I resolved by adding 'sqlCmd.CommandTimeout = 600'. Then ran fine for two data flows but for the third one it passed 22, 000 rows and stopped executing (color not turning from yellow to green). I checked by taking 17,000 rows for source table, then it worked fine. But, for rows more than 20,000 the data flow is not executing successfully. It is not even giving any error. I guess it needs performance improvement. I tried changing 'DefaultBufferMaxRows' to 1000000 and 'DefaultBufferSize' to 104857600. But, no use. Please give me some solution.

    Thanks

  • June 6, 2011 at 11:36 am

    #1334564

    If 17k works and 20k doesn't, set the default max rows to 15000 and give it a try.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • June 6, 2011 at 12:05 pm

    #1334594

    Thank you for your response. I have tried different combinations. Nothing is working. I tried till 100000 for maxrows and 10485760 for size. Please let me know for any other option.

    Thanks

  • June 7, 2011 at 7:02 am

    #1334969

    What are you exactly doing in the package?

    What is the source and what is the destination?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • June 7, 2011 at 9:08 am

    #1335077

    Thank you for the response. I am taking a source table and splitting the data into multiple related tables. When I am using the script component at the place of destination tables (some other script components already existing) then I am not having the issue I have mentioned. But, it is taking much time. It is basically performance issue. Please check the attached file for the structure of the flow.

    Thanks

  • June 9, 2011 at 10:42 pm

    #1336803

    The problem is not SSIS. Just by looking at the timeout you have specified, it looks like you have connection issues. Can you elaborate what is the exact topology and what type of database server you are trying to access? Are you using some kind of linked server, VPN, etc ? The more details you provide, the better.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • June 10, 2011 at 8:00 am

    #1337011

    Thank you for asking. I found the solution (from 40min to 1 min) by replacing all the script components with the other tools available in the data flow. But, only issue is to replace one particular script I need multiple fuzzy look ups which is no allowing. I creates a seperate post for this issue but, no responses :(.

  • June 10, 2011 at 12:33 pm

    #1337194

    I found the solution by changing the similarity and confidence variable names in the second fuzzy match. Thank you.

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

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