Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Comparison of Oracle Drivers

By Haidong Ji,

Because Analysis Service is bundled with SQL Server for free, many companies built data warehouses and OLAP cubes on top of SQL Server. DBAs usually use DTS to get data from Oracle and dump it into SQL Server for DSS and OLAP analysis.

When you are ready to do data transformation from Oracle to Microsoft SQL Server, you may face a choice of a few data providers:

  • Microsoft ODBC driver for Oracle
  • Microsoft OLE DB provider for Oracle
  • Oracle provider for OLE DB, and Oracle in OraHomeXX (fill in your version number of your Oracle client). Depending on your setup, you may not see Oracle in OraHomeXX, which is OK, because it is the slowest driver, as you will see in the speed comparison below.
Of the remaining drivers, which one of these should you choose? Which one perform the best when doing data transformation from Oracle to SQL Server? To find out the answer, I did some performance benchmark testing using DTS. But first, let's define the difference between OLE DB and ODBC.

OLE DB and ODBC: what is the difference

OLE DB stands for Object Linking and Embedding database. It is a standard created by Microsoft based on COM. It is really just a set of Component Object Model interfaces, or COM interfaces, that provide applications with access to data stored in diverse sources. The OLE DB provider accepts an OLE DB API call to the interface and does what is necessary to process that request against the remote data source. This site http://www.microsoft.com/data/oledb/ has more information on OLE DB. It also provides OLE DB software development kit. This has a lot of information on the interfaces that OLE DB supports.

OLE DB is not intended to be a replacement for ODBC. ODBC, or Open DataBase Connectivity, is a well-established standard for connecting to relational databases. It has been extended a little bit to connect to Excel spreadsheets and text files, but for the most part, it is designed for connecting to relational databases. However, OLE DB can access relational databases as well as nonrelational databases. Typically, your data is stored all over the corporation. There is data in your mail servers, directory services, spreadsheets, and text files. OLE DB allows SQL Server to link to these nonrelational database systems. For instance, if you want to query, through SQL Server, the Active Directory on the domain controller, you couldn't do this with ODBC, because it's not a relational database. However, you could use an OLE DB provider to accomplish that.

In summary, the key difference between OLE DB and ODBC is that OLE DB can provide connection to data stored in non-relational format.

Oracle provider for OLE DB is the fastest

To find out which driver perform the best when transferring data from Oracle to SQL Server, I ran a few tests. I used Oracle9i Enterprise Edition Release 9.2.0.4.0, Oracle Client 9.2, SQL server 2000 Developer Edition, and MDAC 2.7 with the latest patch. Here are steps I took to get my test results:
  1. I created a view for a table on a test Oracle server, which has 1 million rows and 60 columns, which has decent data size. The view is created to work around the timestamp data type issue I discussed in a different article;
  2. A table on Microsoft SQL Server is created. This table has the same structure as the view;
  3. I created a DTS package that use one driver mentioned above;
  4. Before I run the package, I issue this command on Oracle server.
    alter system flush shared_pool;
    This command clears Oracle database buffer cache so we have a level playing field for testing;
  5. I then run the package, record the time it took to finish, truncate the table, reboot SQL Server machine;
  6. I repeat the above steps for all the other database drivers.
Below is my testing results:

1st Test 2nd Test 3rd Test
Microsoft ODBC driver for Oracle 6'51 6'51 6'50
Microsoft OLE DB provider for Oracle 7'35 7'28 7'23
Oracle provider for OLE DB 6'38 6'44 6'43
Oracle in OraHome92 26'14 26'21 26'28

From this table, it is easy to tell that Oracle provider for OLE DB is the fastest driver. In a real production system, the overall speed should be better than this, as my SQL Server system is based on a PC. It has just one physical drive. Therefore the log and data files are not placed on different drives.

Conclusion

From my own testing, it is clear that Oracle provider for OLE DB is the best driver to use when transforming data from Oracle to SQL Server using DTS. To make the transformation even faster, if possible, using a single transformation for all columns will result in a much quicker data transfer. When data is transformed column by column, you end up using a number of function calls for one row of data, whereas a single transformation only needs one call for a row of data. If data is transformed column by column, the number of COM function calls equals to the number of columns you are transforming. Obviously, the more calls you make to a COM component and scripting engine, the more overhead that will incur.
Total article views: 15860 | Views in the last 30 days: 7
 
Related Articles
FORUM

Oracle provider issue for SQL 2005

Oracle provider issue for SQL 2005

FORUM

Windows Server Enterprise 64 bit provider needed

Need Oracle OLEdb or ADO.NET provider for 64 bit server

FORUM

Linked Server with Oracle Database

Linked Server with Oracle Database

FORUM

Linking sql server 2008 with Oracle 11g

Data Providers for Oracle 11g and SQL Server 208

FORUM

Oracle Linked Server

Oracle Linked Server

Tags
dts    
miscellaneous    
programming    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones