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 12»»

No Progress while Update Expand / Collapse
Author
Message
Posted Tuesday, September 18, 2012 8:07 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: Tuesday, October 28, 2014 8:16 AM
Points: 691, Visits: 766
Hi Friends,

I am about to update some rows so i use OLEDB command component. When i do this, my updation starts to update for few count of records and does not move further....This is my flow , OLEDB source ---> Look Up1---> LookUp2 ---> OLEDB Command
For example , It starts to fetch 10000 records from Source and goes into OLEDB Command having 4500 records and thats it..remains still......it is not going further and not even showing any error...the data flow remains green for all the time and does not stop .....Why this kinda weird thing is happening? Where do i make mistake?

Please give me suggestions friends....


Thanks,
Charmer
Post #1360810
Posted Thursday, September 20, 2012 3:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 13,520, Visits: 11,313
Your mistake is in using the OLE DB Command for doing updates, which is very, very slow.
Try writing the rows to a staging table and do an UPDATE using TSQL in an Execute SQL Task.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1361824
Posted Friday, September 21, 2012 2: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: Tuesday, October 28, 2014 8:16 AM
Points: 691, Visits: 766
Koen Verbeeck (9/20/2012)
Your mistake is in using the OLE DB Command for doing updates, which is very, very slow.
Try writing the rows to a staging table and do an UPDATE using TSQL in an Execute SQL Task.


Yes..Koen...If update does not work directly, i create a staging table and pushing the rows into the table and start updating from that table like you said...

but what causes slowing the directly updating?

Koen, May i know what could be the possible ways that i could have done mistakes while using OLEDB command component?


Thanks,
Charmer
Post #1362482
Posted Friday, September 21, 2012 2:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 13,520, Visits: 11,313
Could have been anything. Syntax is probably correct, otherwise it would have errored out.
Maybe a deadlock or something?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1362483
Posted Friday, September 21, 2012 6:50 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: Tuesday, October 28, 2014 8:16 AM
Points: 691, Visits: 766
Koen Verbeeck (9/21/2012)
Could have been anything. Syntax is probably correct, otherwise it would have errored out.
Maybe a deadlock or something?


I am the only one who is using this table..i can say this database even...so i believe dead locking is not going to be the reason...but it is having 11 million records to be updated...Koen, is that one of the reason?


Thanks,
Charmer
Post #1362623
Posted Friday, September 21, 2012 6:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 13,520, Visits: 11,313
Charmer (9/21/2012)
Koen Verbeeck (9/21/2012)
Could have been anything. Syntax is probably correct, otherwise it would have errored out.
Maybe a deadlock or something?


I am the only one who is using this table..i can say this database even...so i believe dead locking is not going to be the reason...but it is having 11 million records to be updated...Koen, is that one of the reason?


11 million records? Dump the OLE DB command and use a staging table. Unless you like waiting for a few hours for your package to finish.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1362625
Posted Friday, September 21, 2012 9:16 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: Tuesday, October 28, 2014 8:16 AM
Points: 691, Visits: 766
Koen Verbeeck (9/21/2012)
Charmer (9/21/2012)
Koen Verbeeck (9/21/2012)
Could have been anything. Syntax is probably correct, otherwise it would have errored out.
Maybe a deadlock or something?


I am the only one who is using this table..i can say this database even...so i believe dead locking is not going to be the reason...but it is having 11 million records to be updated...Koen, is that one of the reason?


11 million records? Dump the OLE DB command and use a staging table. Unless you like waiting for a few hours for your package to finish.


Even the staging table will be having 10 million records....so Koen, could you tell me how useful it will be? i mean for updating.....


Thanks,
Charmer
Post #1362773
Posted Saturday, September 22, 2012 3:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 13,520, Visits: 11,313
Charmer (9/21/2012)
Koen Verbeeck (9/21/2012)
Charmer (9/21/2012)
Koen Verbeeck (9/21/2012)
Could have been anything. Syntax is probably correct, otherwise it would have errored out.
Maybe a deadlock or something?


I am the only one who is using this table..i can say this database even...so i believe dead locking is not going to be the reason...but it is having 11 million records to be updated...Koen, is that one of the reason?


11 million records? Dump the OLE DB command and use a staging table. Unless you like waiting for a few hours for your package to finish.


Even the staging table will be having 10 million records....so Koen, could you tell me how useful it will be? i mean for updating.....


Very useful.
Try updating 11 million rows row by row resulting in 11 million different updates and thus 11 million records in the transaction log, or try updating 11 million records in one single transaction/update.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1363085
Posted Tuesday, September 25, 2012 2:25 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: Tuesday, October 28, 2014 8:16 AM
Points: 691, Visits: 766
Koen Verbeeck (9/22/2012)
Charmer (9/21/2012)
Koen Verbeeck (9/21/2012)
Charmer (9/21/2012)
Koen Verbeeck (9/21/2012)
Could have been anything. Syntax is probably correct, otherwise it would have errored out.
Maybe a deadlock or something?


I am the only one who is using this table..i can say this database even...so i believe dead locking is not going to be the reason...but it is having 11 million records to be updated...Koen, is that one of the reason?


11 million records? Dump the OLE DB command and use a staging table. Unless you like waiting for a few hours for your package to finish.


Even the staging table will be having 10 million records....so Koen, could you tell me how useful it will be? i mean for updating.....


Very useful.
Try updating 11 million rows row by row resulting in 11 million different updates and thus 11 million records in the transaction log, or try updating 11 million records in one single transaction/update.


As far as i know row by row can be done only by OLEDB comand component. If it is not going well, then what is the other solution, Koen?

What i do here is, pushing all records into Staging table and using a Execute SQL task to update all the rows in single transaction....well, the way i do is good or any other better way? Could you tell me, Koen?


Thanks,
Charmer
Post #1363851
Posted Tuesday, September 25, 2012 2:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 13,520, Visits: 11,313
Charmer (9/25/2012)


What i do here is, pushing all records into Staging table and using a Execute SQL task to update all the rows in single transaction....well, the way i do is good or any other better way? Could you tell me, Koen?


This is the way to go.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1363852
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse