CODEWITHSUNDEEP
sql-serveroracle-databaseetldata-migration
BI_Dev
BI_Dev

Reputation: 11

Most efficient way to load large table+data from Oracle11G into SQL Server 2012 (I have a linked server setup)

I have two databases, one is SQL Server 2012 and the other one is an Oracle 11G database.

I need to copy a large table with 100 million records from Oracle into the SQL Server database.

What is the most efficient way to copy those 100 million records into a SQL server?

I have a linked server setup in my SQL Server which is pointing at the Oracle Database.

Upvotes: 0

Views: 238

Answers (1)

Venkataraman R
Venkataraman R

Reputation: 12959

You can think of below things, before planning data movement.

  1. CREATE TABLE with the right datatype definition, so that you will avoid IO issues, whild loading data. Define the minimum datatype to suit your needs. Do data profiling in the ORACLE side to decide how the columns are defined, what is the maximum length for columns, what is actually being used in ORACLE side. e.g., If the column is age, then tinyint is enough(supports 1- 255).

  2. The target SQL Server table should not have any PK or FK constraints enabled. Disable all constraints, before loading data. You can also think of dropping and recreating PK, FK.

  3. The target SQL Server tables should not have any indexes enabled. Disable all indexes, before loading data. You can also think of dropping and recreating indexes.
  4. Do the insertion in batches. Having a huge INSERT statement as a single batch, can fail due to many reasons : Network problem suddenly, sudden huge log writing, sudden memory footprint increase etc. If you are having a timestamp column or unique identifier column in ORACLE, apply filter and get data in batches. Have the @Id as configurable. 
DECLARE Id INTEGER

SELECT * FROM OracleTable WHERE Id < 1000000 -- first batch
SELECT * FROM oracleTable WHERE Id > 1000000 AND Id < 2000000

  1. Try to go for 64 bit ORACLE drivers for linked server. They will give better performance, compared to 32 bit drivers. Oracle 64 bit driver linked server

  2. Schedule this operation in less obtrusive time to get more resources available for you in the SQL Server side.

  3. Do a small testing with small amount of data to check whether everything is working fine, before going for full fledged approach, to avoid intermittent issues.

  4. See whether you have parallelism enabled in the SQL Server side, to take advantage of parallel insert. Max degree of parallelism

Upvotes: 1

Related Questions