We are in the middle of deploying a new data warehouse using SQL Server 2014. One of my data sources is Oracle, and unfortunately, the recommended Attunity component for quick access to data is not yet available for SSIS 2014.
I tried to avoid using OLEDB because it requires installing certain Oracle client tools, which caused me a lot of disappointments earlier and with Attunity material supposedly in work ( MS promised that they will arrive in August already ), I don’t want to go through again test.
Therefore, I use ADO.NET. All that is considered, performance is acceptable at the moment, with the exception of one specific table.
There are a bunch of varchar columns in this particular table in Oracle, and I came to the conclusion that this is because of the width of the selected row, which this table runs especially slowly. To prove this, instead of selecting all the columns as they exist in Oracle (which is the source package I created), I truncated all the widths to the maximum length of the actually stored values ( CAST(column AS varchar(46)). This reduced the launch time of the same package to 17 minutes (even lower than what I would call acceptable, and this is not what I would put into production, because it will open the world of future pain, but it will prove the width of the columns, of course, is a factor).
I increased the network packet size in SQL Server, but that didn't seem to help much. I could not find a good way to change the package size on the ADO.NET connector for Oracle (SQL Server has this option). I tried to see if I added the Packet size=32000;connection string for the Oracle connector, but it just threw an error, indicating that it simply would not be accepted. The same goes for FetchSize.
, , , varchar MERGE JOIN, . , 30 ( CAST ). .
( MERGE JOIN, , SQL- ), , , - .
- Oracle ADO.NET, ? , - , , , , , Attunity ?
!