My goal was to conduct some performance tests to determine the difference using Oracle vs. SQL Server 2005 as data feeds for SSIS transformations.
I created an identical table in both Oracle and SQL Server. The DDL is this:
CREATE TABLE EXTREME_DATA_TEST
I loaded the table with 9.5 million rows of unique data. I created the below job in SSIS to process the rows. The job simply reads the data through and OLE DB connection and writes it out to a flat file with no transformations.
I then fired up the Oracle 10g instance on my personal laptop. Yes it's a high performance laptop but I don't expect near the performance of a EMC disk farm. I started the job and let it run 10 seconds. The following screen shot is after 10 seconds.
My second test was to run the same job connecting to SQL Server 2005. Remember that the SQL Server 2005 table has exactly the same data. Notice the drastic difference in data speeds.
Did you notice what I noticed? SQL Server 2005 is pulling data at 10x the speed of the Oracle OLE DB connection. For further tests, I created a non-unique bitmap index in Oracle 10g on the order_dt_key column. This had no apparent effect on the reading speed.
All I can say is that I am certainly leaning towards doing more ETL development in SQL Server 2005.