August 22, 2013 at 5:54 am
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
August 22, 2013 at 8:03 am
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/
August 22, 2013 at 8:26 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 22, 2013 at 8:37 am
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
August 22, 2013 at 8:55 am
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/
August 22, 2013 at 9:07 am
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
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 22, 2013 at 10:33 am
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?
August 22, 2013 at 11:55 pm
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
August 22, 2013 at 11:57 pm
Are you running this as a query from SSMS from your local machine?
yes..
August 23, 2013 at 8:15 am
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