Performance issue

  • Hi All,

    I am migrating 35 million data to another new table(EVALUATION_CRITERIA) using simple joins, But it is taking to execute around 24 hrs and system ram=16 GB,

    Below are the insert query and attached Execution plan

    Can any one suggest , is there any best ways

    can i get Any Time difference If run same code in SSIS Package ?

    Records count in user tables

    EVALUATION_DETAIL33240682

    EVALUATION1297899

    EVALFORM 338

    EVALFORM_CRITERIA8619

    EVALFORM_CRITERIA_DETAIL 34630

    --Script

    Declare @EvaluationId Int, @EvalformId Int

    Declare CursorData Cursor For

    Select Evaluation_Id, Evalform_Id From Evaluation

    Open CursorData

    Fetch Next From CursorData Into @EvaluationId, @EvalformId

    While @@Fetch_Status = 0

    Begin

    Insert EVALUATION_CRITERIA (EVALUATION_ID, EVALFORM_CRITERIA_ID, TITLE_NAME, BASE_CRITERIA_ID, EVALFORM_CRITERIA_DETAIL_ID, CHILD_EXIST, PRIORITY_ORDER, WEIGHTAGE, ANSWER_TYPE, STATUS, CREATE_BY, CREATE_DATE, LEVEL, SCORE)

    select e.EVALUATION_ID, efcd.EVALFORM_CRITERIA_ID, efc.TITLE_NAME, efc.BASE_CRITERIA_ID,

    efcd.EVALFORM_CRITERIA_DETAIL_ID, efc.CHILD_EXIST, efc.PRIORITY_ORDER, efc.WEIGHTAGE, NULL, --efc.ANSWER_TYPE,

    --'A', 1, GETDATE(), efc.LEVEL, efcd.SCORE

    'A', 1, GETDATE(), efc.LEVEL, (select MAX(score) from EVALFORM_CRITERIA_DETAIL where EVALFORM_CRITERIA_ID = efcd.EVALFORM_CRITERIA_ID and STATUS = 'A' and OMIT_FROM_SCORE = 'N') score

    from EVALFORM ef

    inner join EVALFORM_CRITERIA efc on ef.EVALFORM_ID = efc.EVALFORM_ID

    left join EVALFORM_CRITERIA_DETAIL efcd on efc.EVALFORM_CRITERIA_ID = efcd.EVALFORM_CRITERIA_ID

    left join EVALUATION_DETAIL ed on efcd.EVALFORM_CRITERIA_DETAIL_ID = ed.EVALFORM_CRITERIA_DETAIL_ID

    left join EVALUATION e on ed.EVALUATION_ID = e.EVALUATION_ID

    where e.EVALUATION_ID=@EvaluationId and efc.STATUS = 'A'

    Union

    select @EvaluationId, efc.EVALFORM_CRITERIA_ID, efc.TITLE_NAME, efc.BASE_CRITERIA_ID,

    0, efc.CHILD_EXIST, efc.PRIORITY_ORDER, efc.WEIGHTAGE, NULL, --efc.ANSWER_TYPE,

    'A', 1, GETDATE(), efc.LEVEL, NULL --efcd.SCORE

    from EVALFORM ef

    inner join EVALFORM_CRITERIA efc on ef.EVALFORM_ID = efc.EVALFORM_ID

    left join EVALFORM_CRITERIA_DETAIL efcd on efc.EVALFORM_CRITERIA_ID = efcd.EVALFORM_CRITERIA_ID

    where efc.CHILD_EXIST = 'Y' and efc.STATUS = 'A' and ef.EVALFORM_ID = @EvalformId

    --Select @EvaluationId, @EvalformId

    Fetch Next From CursorData Into @EvaluationId, @EvalformId

    End

    Close CursorData

    Deallocate CursorData

    --Attached Execution plan

  • The reason you have such horrible performance is because you are using a cursor for inserts. This should be done in a single statement instead of 35 million individual inserts. There may be some indexing and such you can do once this is a set based operation but as long as you have a cursor looping through 35 million rows your performance is going to be awful.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean does a have point, but, I'd say that a 35 million row insert might be better done batched, but I'd do an explicit transaction for each loop of the batch. I don't think you'd gain much, if anything from SSIS since you are staying on the same server in the same database.

  • Thanks for your reply .

    As per above code(as per functional wise) not possible to insert at single time . It is looping 1.2 millions time and joining with EVALUATION_DETAIL(35 millions) table. All indexes are working fine, if observe execution plan (attached with question) all are Clustered index seek and Index seek. Even though it is talking 24 hrs time

    If I keep while loop instead of cursor , can i get performance

  • PRR.DB (8/22/2013)


    Thanks for your reply .

    As per above code(as per functional wise) not possible to insert at single time . It is looping 1.2 millions time and joining with EVALUATION_DETAIL(35 millions) table. All indexes are working fine, if observe execution plan (attached with question) all are Clustered index seek and Index seek. Even though it is talking 24 hrs time

    If I keep while loop instead of cursor , can i get performance

    No a while loop is pretty much the same thing as a cursor. Given that each iteration of your loop you are performing 3 select statements and an insert you are executing somewhere around 140 million queries.

    From the code you posted I don't see any reason you have to loop. I agree with Jack that this should be done in batches or the transactions will be huge. Maybe do 10-20k at a time. This does mean that you need a looping mechanism but each iteration of the loop would be thousands of rows, not just one.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • The attached execution plan doesn't include the UNION. A UNION ALL there if you know there are no duplicates would help some. Also your execution plan doesn't show 1.2 million rows it is showing 35 rows total.

    In your first query you do understand that the LEFT JOIN to EVALUATION is turned into an INNER JOIN because of the criteria WHERE "e.EVALUATION_ID = @EvaluationID" so you may not be getting the result you expect and because of this the only LEFT JOIN you are actually getting is to EVALUATION_CRITERIA_DETAIL.

    The correlated sub-query to get the max(score) is most likely your biggest performance killer. I normally try to do something like that using either CROSS/OUTER APPLY or a CTE/Derived table to get the SUM. I might to it something like this:

    SELECT

    e.EVALUATION_ID,

    efcd.EVALFORM_CRITERIA_ID,

    efc.TITLE_NAME,

    efc.BASE_CRITERIA_ID,

    efcd.EVALFORM_CRITERIA_DETAIL_ID,

    efc.CHILD_EXIST,

    efc.PRIORITY_ORDER,

    efc.WEIGHTAGE,

    NULL, --efc.ANSWER_TYPE,

    -- 'A', 1, GETDATE(), efc.LEVEL, efcd.SCORE

    'A',

    1,

    GETDATE(),

    efc.LEVEL,

    detailScore.score

    FROM

    EVALFORM ef

    INNER JOIN EVALFORM_CRITERIA efc

    ON ef.EVALFORM_ID = efc.EVALFORM_ID

    LEFT JOIN EVALFORM_CRITERIA_DETAIL efcd

    ON efc.EVALFORM_CRITERIA_ID = efcd.EVALFORM_CRITERIA_ID

    OUTER APPLY (

    SELECT

    MAX(score) AS score

    FROM

    EVALFORM_CRITERIA_DETAIL

    WHERE

    EVALFORM_CRITERIA_ID = efcd.EVALFORM_CRITERIA_ID AND

    STATUS = 'A' AND

    OMIT_FROM_SCORE = 'N'

    ) AS detailScore

    INNER JOIN EVALUATION_DETAIL ed

    ON efcd.EVALFORM_CRITERIA_DETAIL_ID = ed.EVALFORM_CRITERIA_DETAIL_ID

    INNER JOIN EVALUATION e

    ON ed.EVALUATION_ID = e.EVALUATION_ID

    WHERE

    e.EVALUATION_ID = @EvaluationId AND

    efc.STATUS = 'A'

    UNION

    SELECT

    @EvaluationId,

    efc.EVALFORM_CRITERIA_ID,

    efc.TITLE_NAME,

    efc.BASE_CRITERIA_ID,

    0,

    efc.CHILD_EXIST,

    efc.PRIORITY_ORDER,

    efc.WEIGHTAGE,

    NULL, --efc.ANSWER_TYPE,

    'A',

    1,

    GETDATE(),

    efc.LEVEL,

    NULL --efcd.SCORE

    FROM

    EVALFORM ef

    INNER JOIN EVALFORM_CRITERIA efc

    ON ef.EVALFORM_ID = efc.EVALFORM_ID

    LEFT JOIN EVALFORM_CRITERIA_DETAIL efcd

    ON efc.EVALFORM_CRITERIA_ID = efcd.EVALFORM_CRITERIA_ID

    WHERE

    efc.CHILD_EXIST = 'Y' AND

    efc.STATUS = 'A' AND

    ef.EVALFORM_ID = @EvalformId

  • PRR.DB (8/22/2013)


    Hi All,

    I am migrating 35 million data to another new table(EVALUATION_CRITERIA) using simple joins, But it is taking to execute around 24 hrs and system ram=16 GB,

    Below are the insert query and attached Execution plan

    Can any one suggest , is there any best ways

    can i get Any Time difference If run same code in SSIS Package ?

    Records count in user tables

    EVALUATION_DETAIL33240682

    EVALUATION1297899

    EVALFORM 338

    EVALFORM_CRITERIA8619

    EVALFORM_CRITERIA_DETAIL 34630

    --Script

    Declare @EvaluationId Int, @EvalformId Int

    Declare CursorData Cursor For

    Select Evaluation_Id, Evalform_Id From Evaluation

    Open CursorData

    Fetch Next From CursorData Into @EvaluationId, @EvalformId

    While @@Fetch_Status = 0

    Begin

    Insert EVALUATION_CRITERIA (EVALUATION_ID, EVALFORM_CRITERIA_ID, TITLE_NAME, BASE_CRITERIA_ID, EVALFORM_CRITERIA_DETAIL_ID, CHILD_EXIST, PRIORITY_ORDER, WEIGHTAGE, ANSWER_TYPE, STATUS, CREATE_BY, CREATE_DATE, LEVEL, SCORE)

    select e.EVALUATION_ID, efcd.EVALFORM_CRITERIA_ID, efc.TITLE_NAME, efc.BASE_CRITERIA_ID,

    efcd.EVALFORM_CRITERIA_DETAIL_ID, efc.CHILD_EXIST, efc.PRIORITY_ORDER, efc.WEIGHTAGE, NULL, --efc.ANSWER_TYPE,

    --'A', 1, GETDATE(), efc.LEVEL, efcd.SCORE

    'A', 1, GETDATE(), efc.LEVEL, (select MAX(score) from EVALFORM_CRITERIA_DETAIL where EVALFORM_CRITERIA_ID = efcd.EVALFORM_CRITERIA_ID and STATUS = 'A' and OMIT_FROM_SCORE = 'N') score

    from EVALFORM ef

    inner join EVALFORM_CRITERIA efc on ef.EVALFORM_ID = efc.EVALFORM_ID

    left join EVALFORM_CRITERIA_DETAIL efcd on efc.EVALFORM_CRITERIA_ID = efcd.EVALFORM_CRITERIA_ID

    left join EVALUATION_DETAIL ed on efcd.EVALFORM_CRITERIA_DETAIL_ID = ed.EVALFORM_CRITERIA_DETAIL_ID

    left join EVALUATION e on ed.EVALUATION_ID = e.EVALUATION_ID

    where e.EVALUATION_ID=@EvaluationId and efc.STATUS = 'A'

    Union

    select @EvaluationId, efc.EVALFORM_CRITERIA_ID, efc.TITLE_NAME, efc.BASE_CRITERIA_ID,

    0, efc.CHILD_EXIST, efc.PRIORITY_ORDER, efc.WEIGHTAGE, NULL, --efc.ANSWER_TYPE,

    'A', 1, GETDATE(), efc.LEVEL, NULL --efcd.SCORE

    from EVALFORM ef

    inner join EVALFORM_CRITERIA efc on ef.EVALFORM_ID = efc.EVALFORM_ID

    left join EVALFORM_CRITERIA_DETAIL efcd on efc.EVALFORM_CRITERIA_ID = efcd.EVALFORM_CRITERIA_ID

    where efc.CHILD_EXIST = 'Y' and efc.STATUS = 'A' and ef.EVALFORM_ID = @EvalformId

    --Select @EvaluationId, @EvalformId

    Fetch Next From CursorData Into @EvaluationId, @EvalformId

    End

    Close CursorData

    Deallocate CursorData

    --Attached Execution plan

    Are you running this as a query from SSMS from your local machine?

  • Jack Corbett (8/22/2013)


    The attached execution plan doesn't include the UNION. A UNION ALL there if you know there are no duplicates would help some. Also your execution plan doesn't show 1.2 million rows it is showing 35 rows total.

    Attached single loop execution plan only thats why it is showing 35 rows and taken execution plan without "union operator" in insert script

  • Are you running this as a query from SSMS from your local machine?

    yes..

  • PRR.DB (8/22/2013)


    Are you running this as a query from SSMS from your local machine?

    yes..

    If you using a scipt from your local machine using SSMS to populate 35 M records,there itself you have a first performance hit. There could be more but this would be first place to look at. Try putting this in a package or a sql job.

Viewing 10 posts - 1 through 9 (of 9 total)

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