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 10 (of 10 total)

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

Phil Parkin SSC Guru Points: 246993 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?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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

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

Phil Parkin SSC Guru Points: 246993 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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Koen Verbeeck SSC Guru Points: 259125 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: 246993 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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Koen Verbeeck SSC Guru Points: 259125 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: 259125 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