CODEWITHSUNDEEP
sql-serveroracle-databaseperformancessisquery-optimization
Aditya Chouksey.
Aditya Chouksey.

Reputation: 11

Oracle VIEW data source in SSIS Dataflow

I have an oracle data source and I am fetching data from an oracle view with 0.7 million records. I am using oracle provide for OLEDB. For now, I have removed every other component in SSIS package and kept this dataflow only, but still it takes 1+ hour.

I do not have right to do any changes on source side. What can be done in this case to optimize the data read operation? Client is also reluctant to use any new connector like attunity.

Upvotes: 1

Views: 155

Answers (1)

Brian Leach
Brian Leach

Reputation: 2101

Every hour I copy 650K records from Oracle to SQL Server using a linked server in the SS database. It normally takes 1.5 to 2.5 minutes. My records are fairly narrow.

I too was having performance issues when inserting directly into the target table. My initial load times were over an hour and up to two hours. I was able to speed things up by inserting into a temp table on SS that has no constraints. I then use an insert select statement to move the holding table data into the final table. The insert is taking 16-20 seconds.

Upvotes: 0

Related Questions