SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance issue


Performance issue

Author
Message
PRAMANA.DBA
PRAMANA.DBA
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1682 Visits: 1030
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
Attachments
execution plan 1.sqlplan (21 views, 64.00 KB)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62613 Visits: 17959
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.

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)
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44663 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
PRAMANA.DBA
PRAMANA.DBA
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1682 Visits: 1030
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62613 Visits: 17959
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.

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)
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44663 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
curious_sqldba
curious_sqldba
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7402 Visits: 3696
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?
PRAMANA.DBA
PRAMANA.DBA
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1682 Visits: 1030
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
PRAMANA.DBA
PRAMANA.DBA
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1682 Visits: 1030

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


yes..
curious_sqldba
curious_sqldba
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7402 Visits: 3696
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search