Menu Request Demo

SQL Server Data Acquisition using RED and Native Support for Microsoft SQL Server Integration Services

Date:
10 December 2010
Author:
Jason Laws

The other day I ran some comparative tests using different options to load data into a SQL Server data warehouse from an Oracle database using WhereScape RED.  Here’s a summary of what I did and what I found:

1. The new WhereScape RED load option Integration Services load is the fastest way to move data into SQL Server from another relational database.

2. Native ODBC loads are the second fastest way to move data into SQL Server.  Linked servers are significantly slower than other options.

3. Whether the architecture is 32-bit or 64-bit makes no difference loading data into a load table in SQL Server 2008 from another database.

Details follow, apologies if this looks like a high school science report!

 
 

 

Aim:

The objectives were to:

1. Show the new WhereScape RED load option:  Integration Services load.  This uses the new Native Support for Microsoft SQL Server Integration Services.

2. Determine the fastest way to move data from a relational database into SQL Server 2008 database using WhereScape RED.

3. Quantify any difference between 32-bit and 64-bit platform performance.

Method:

A series of tests were run on two identical machines, one 32-bit and one 64-bit.  The two machines were configured as follows:

32-bit Server:

- Windows Server 2003 R2 Enterprise Edition Service Pack 2

- AMD Athlon II X4 635 Processor 2.91 GHz

- 6GB of RAM (PAE)

64-bit Server:

- Windows Server 2003 R2 Enterprise x64 Edition Service Pack 2

- AMD Athlon II X4 635 Processor 2.91 GHz

- 6GB of RAM

Both machines were built using vm-ware 6.5 and tests were performed using vm-ware server on identical host machines.  The 64-bit virtual machine ran on a 64-bit host.  The 32-bit virtual machine ran on a 32-bit host.  Both had 2 virtual CPUs assigned to them (i.e.: 2 cores of the host machines CPU).

SQL Server 2008 R1 was used as the data warehouse database, the 32-bit Enterprise and 64-bit Enterprise versions being used respectively.

The source system was in an Oracle 11.2.0.1.0 database – Enterprise Edition.  Again 32-bit and 64-bit versions being used as appropriate.

SQL Server and Oracle were installed on the same server.

There were some additional set up complexities on the 64-bit machine.  Specifically:

- An oracle 32-bit client was also required (in addition to the 64-bit client installed with the database)

- A 64-bit oledb provider from oracle had to be installed as Microsoft does not supply a 64-bit version of MSDAORA (they do supply a 32-bit version of MSDAORA with 32-bit SQL Server).

The testing involved a large table being loaded into SQL Server from Oracle.  The source table contained approximately 13.6 million rows and used about 2Gb of space in Oracle.  It contained most common data types.

The following tests were performed:

- 32-bit machine:  Integration Services load in RED

- 32-bit machine:  Native ODBC load - 32-bit MS Driver

- 32-bit machine:  Native ODBC load - 32-bit ORA Driver

- 32-bit machine:  Native ODBC load - 32-bit ORA Driver and RAM Disk  ***1

- 32-bit machine:  Linked Server in RED – 32-bit – 4 part names

- 32-bit machine:  Linked Server in RED – 32-bit – open query

- 32-bit machine:  Linked Server in a Query Wind – 32-bit – 4 part names

- 64-bit machine:  Integration Services load in RED

- 64-bit machine:  Native ODBC load - 32-bit MS Driver

- 64-bit machine:  Native ODBC load - 32-bit ORA Driver

- 64-bit machine:  Native ODBC load - 64-bit ORA Driver  ***2

- 64-bit machine:  Native ODBC load - 32-bit ORA Driver and RAM Disk  ***1

- 64-bit machine:  Linked Server in RED – 64-bit – 4 part names

- 64-bit machine:  Linked Server in RED – 64-bit – open query

- 64-bit machine:  Linked Server in a Query Wind – 64-bit – 4 part names

Notes:

***1:  A RAM disk was used in one Native ODBC load test to check for IO constraints.

***2:  This test was run using a specially compiled 64-bit version of the RED native load extract utility.

Results:

 
 

 

Conclusions:

1. The new WhereScape RED load option Integration Services load is the fastest way to move data into SQL Server from another relational database.

2. Native ODBC loads are the second fastest way to move data into SQL Server.  Linked servers are significantly slower than other options.

3. Whether the architecture is 32-bit or 64-bit makes no difference loading data into a load table in SQL Server 2008 from another database.

CATEGORIES:

Comments 0

Leave a Comment

No comments.