Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performance issue Expand / Collapse
Author
Message
Posted Thursday, August 22, 2013 5:54 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:07 AM
Points: 697, Visits: 928
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_DETAIL 33240682
EVALUATION 1297899
EVALFORM 338
EVALFORM_CRITERIA 8619
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


  Post Attachments 
execution plan 1.sqlplan (17 views, 64.00 KB)
Post #1487188
Posted Thursday, August 22, 2013 8:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 12,923, Visits: 12,344
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1487280
Posted Thursday, August 22, 2013 8:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:06 AM
Points: 10,193, Visits: 13,118
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1487300
Posted Thursday, August 22, 2013 8:37 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:07 AM
Points: 697, Visits: 928
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
Post #1487309
Posted Thursday, August 22, 2013 8:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 12,923, Visits: 12,344
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1487325
Posted Thursday, August 22, 2013 9:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:06 AM
Points: 10,193, Visits: 13,118
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1487338
Posted Thursday, August 22, 2013 10:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 6:05 PM
Points: 1,283, Visits: 2,960
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_DETAIL 33240682
EVALUATION 1297899
EVALFORM 338
EVALFORM_CRITERIA 8619
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?
Post #1487403
Posted Thursday, August 22, 2013 11:55 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:07 AM
Points: 697, Visits: 928
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
Post #1487643
Posted Thursday, August 22, 2013 11:57 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:07 AM
Points: 697, Visits: 928

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


yes..
Post #1487644
Posted Friday, August 23, 2013 8:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 6:05 PM
Points: 1,283, Visits: 2,960
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.
Post #1487838
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse