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