ADO faster than OLE!?

Question

ADO faster than OLE!?

Jeremy

SSC Enthusiast

Points: 116
More actions
September 3, 2019 at 8:43 pm

#3677005

I am running an SSIS job extracting data from an Oracle database and loading it into a SQL 2016 database. I am using the 64 bit Oracle 18.0 client. Everything I have read says the OLE should be significantly faster than ADO. However I am finding the opposite. I created a simple job that loads about 50 million integer primary keys (no other data, just the integer values). Using the ADO adapter, the job takes about 10 minutes. I stopped the OLE job after 90 minutes and it had only completed about 30 million rows. All of the other settings were held constant. Does anyone have any thoughts on why OLE is running so slowly? At this point, I am considering refactoring all of my jobs to use ADO.
Thanks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

I have no thoughts on why OLEDB may be running slower than ADODB, but that has always been my experience. I don't have the link for it but I also read an article a several years ago that said that ADODB lost favor for use but then came right back because of the difference in performance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Ian Rennie SSC Enthusiast Points: 100 More actions · Answer 2

Try the Attunity drivers: https://docs.microsoft.com/en-us/sql/integration-services/data-flow/oracle-connector?view=sql-server-2017

Since installing and using these the performance of our SSIS job has improved dramatically

Jeremy SSC Enthusiast Points: 116 More actions · Answer 3

It looks like Attunity is only supported on enterprise edition. Sadly, this is running on standard edition.

I switched my connectors to ADO and the performance has improved dramatically. I'm still not sure why OLE was so slow, but it is a back-burner curiosity at this point.