Skip to content

SQL Server Analysis Services and Oracle OLE DB

November 2, 2010

I recently had that experience which seems to be all to common when working with SQL Server Analysis Services and Oracle, namely “why is this running so slowly?” I am talking about the process of reading data (partition processing) from Oracle using the Oracle OLE DB Provider. I am sure others have been there. Oracle is running on a 32-proc SunFire server, SQL is running on an 8-core, 32GB DL585 G2, they are connected by 1Gb LAN and both machines are idling; barely breaking a sweat. Throughput while processing a single partition (ProcessData and examining Rows Read/sec counter) is between 10,000 and 15,000 rows per second.

If you are experiencing the same or similar issue, then you may wish to experiment with different settings for the FetchSize parameter on the Oracle OLE DB connection string e.g. Data Source=ORACLE.WORLD;User ID=User;FetchSize=100000;. The default setting is FetchSize=100. This means that the Oracle OLE DB provider will request and fetch 100 rows of data. SQL Server will then read 100 rows of data. Once all 100 rows have been read, the Oracle provider will request another 100 rows of data. This will continue untill all rows are read.

It seems that there is an extremely large overhead (and very poor pipelining i.e. non-existent pipelining) when using the Oracle OLE DB provider like this. The SQL Server provider for example does not do this and immediately starts to buffer more rows as they are read (i.e. it implements pipelining).

On the machine I was developing on, I achieved a maximum throughput of 50,000 rows/sec on a single thread when specifying a FetchSize=1000000. That is 1 million rows. Processing multiple partitions in parallel will give me a much higher overall throughput. I recommend that anyone using Oracle OLE DB as a data source for Analysis Services experiments with different values for FetchSize to find the value that optimizes throughput.

I am about to try the DataDirect provider to see how much better this is than the Oracle OLE DB provider for the same scenario. I will post a follow-up with the results of that test.

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: