Want to update the table performance vice which transformation is best to use

Question

Want to update the table performance vice which transformation is best to use

sharmili.net

SSC-Addicted

Points: 451
More actions
January 10, 2014 at 1:08 am

#396085

Hi Everyone,
I want to update the table in a database, performance vice which transformation is best to use?
Thanks in advance.
sharmi

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

sharmili.net (1/10/2014)
Hi Everyone,
I want to update the table in a database, performance vice which transformation is best to use?
Thanks in advance.
sharmi

"Vice"? What is that?

sharmili.net SSC-Addicted Points: 451 More actions · Answer 2

sorry for typo..wise which means performance point of..

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

Inserts are best done using a dataflow.

Updates are best done as a set-based operation - meaning that the OLEDB command is NOT the way to go. One possibility is funneling all of your inserts to a staging area and then finally running a T-SQL UPDATE or MERGE from that to the target table.

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 4

Do not use the OLE DB Command Transformation.

Write the change rows to a staging table and then use an Execute SQL Task to issue an UPDATE statement.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sharmili.net SSC-Addicted Points: 451 More actions · Answer 5

sharmili.net

SSC-Addicted

Points: 451

January 10, 2014 at 1:48 am

#1679060

Thank you for your responses..

sharmili.net SSC-Addicted Points: 451 More actions · Answer 6

why should not use OLEDB cmd since its processing each row..or any other specific reason is there?

Phil Parkin SSC Guru Points: 247180 More actions · Answer 7

sharmili.net (1/10/2014)
why should not use OLEDB cmd since its processing each row..or any other specific reason is there?

That is the reason.

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 8

sharmili.net (1/10/2014)
why should not use OLEDB cmd since its processing each row..or any other specific reason is there?

Row by row == slow (very slow for large data sets)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sharmili.net SSC-Addicted Points: 451 More actions · Answer 9

Then what is the purpose of using OLE DB transformation as it causes performance issue when we have execute sql task.

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 10

sharmili.net (1/10/2014)
Then what is the purpose of using OLE DB transformation as it causes performance issue when we have execute sql task.

The performance is reasonable for small data sets (just a few rows, less than 100).

It's the same as a cursor in TSQL. It's damn slow, but sometimes you need a cursor.

(and there are other components in SSIS who are just not useful at all)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP