September 23, 2014 at 6:48 am
Hello,
I created an SSIS package that extracts data from an Oracle database. While the query runs quickly in Sql Developer and the explain plan appears to be minimal it just seems to hang. I have used ado.net and Oledb data sources and have not seen any improvements. I have noticed that the performance degrades significantly when I have unions in the query. I am wondering if the drivers are attempting to recreate the queries and if that may be other Oracle drivers available that could help improve performance.
Do you have any suggestions? Thanks Hector
September 25, 2014 at 12:10 am
Are you using the union operator in SSIS or in the sourcequery itself?
Sure that you retrieve all rows in sqldeveloper?
September 25, 2014 at 6:38 am
I am using the union within the query. We do not get a large result set. We were able to get around this by creating a view in Oracle but I don't want to have to go that route every time. Hector
September 25, 2014 at 6:49 am
Which provider are you using?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 25, 2014 at 7:37 am
Native OLE DB/Microsofe OLE DB Provider for Oracle
I have used the ado.net that did not show any improvements.
It seems like the driver is parsing the query differently. hs
September 25, 2014 at 7:44 am
Try using the Oracle OLE DB provider. That one is available in 64-bit as well.
If you have Enterprise edition, you could also try the Oracle drivers by Attunity, they are even faster.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply