Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2003
    Posts
    15

    Question Unanswered: Sql import/push to Oracle faster

    Looking for a faster method of moving data from SQL to Oracle.

    I'm attempting to push a sql table into an oracle table (sql server 2000 oracle 7, 8, and 9). I have no problem doing this with either 'Oracle Provider for OLE DB' or the 'Microsoft OLE DB Provider for Oracle'. None of my data is being transformed so its a straight import. With the hardware I'm using it takes nearly 3 seconds to import 1000 rows. While this isn't too bad, I need to import upwards of 4 million rows and this results in unacceptable time results.

    I do have an oracle script that imports the csv files of the tables, but I'm looking for an all inclusive sql solution.

    Does anyone know of another method in SQL that I can use to push the data faster?

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Perhaps you could provide a bit more info:

    1. What's your network bandwidth?
    2. What kind of table are you importing to? Are there indexes on the table?
    3. What are you using for the import process (DTS? Linked server?)
    4. How much memory is on the SQL box? the Oracle?
    5. What's the storage solution like on the Oracle box? Are the log files separate from the data files? Are they multiplexed? Is the instance in archive log mode?

    This may be more a question for the Oracle forum.

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Apr 2003
    Posts
    15
    Not going to be too much help, but here is what I know.

    1. I have no clue about the bandwidth. Large corporation so I'd assume decent. I'm also on the same network as the oracle box.
    2. Nothing fancy table, around 15 columns with one index.
    3. DTS import (called through command line)
    4. SQL: CPU 2500 MHz RAM: 1024MB
    Oracle - not sure
    5. I'm not sure about this one, but I do know log and data files are seperate.

    I have spoken to the Oracle DBAs and have enough privs to accomplish this task and they have no limits on my bandwidth (at least thats what they tell me).

    I'll go ahead and post on the oracle forum. Basically I'm looking for the fastest method to appen/migrate a sql table to an oracle table (with the same table definitions).

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm offering some thoughts based on my experience, these may or may not apply in your circumstances so please don't treat them as gospel.

    The first thing I'd question is your SQL Server configuration. Your SQL Server would be considered seriously short of RAM in my environment. I can only think of one SQL Server (out of nearly 100) that has less than 4 Gb of RAM in our environment. This obviously isn't affecting Oracle's ability to consume rows, but I still see it as a "weak point" in your process.

    Your transport speeds and the mention of CSV files makes me think you are doing some variety of "two step" in the transfer. Don't do that!

    The fastest way that I've found to move data from SQL Server 2000 to Oracle 8i or later is using a linked server and an INSERT statement into an Oracle table from the SQL Server. This basically coughs up data in raw form, and flings it across the network with as little "middleware" as possible in the process. I would expect something in excess of 10,000 rows per second for a fifteen column table with no transformations needed.

    -PatP

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hmmm, as far as I know there is nothing faster in either Horacle or SQL Server than their respective bulk load facilities (SQL*Loader and BCP API respectively). If you compare the performance between an INSERT against a linked server and SQL*Loader, - you would be amazed

    In short, - BCP out into a text file and SQL*Load into Horacle...That's the fastest way!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by flick
    Not going to be too much help, but here is what I know.

    1. I have no clue about the bandwidth. Large corporation so I'd assume decent. I'm also on the same network as the oracle box.
    2. Nothing fancy table, around 15 columns with one index.
    3. DTS import (called through command line)
    4. SQL: CPU 2500 MHz RAM: 1024MB
    Oracle - not sure
    5. I'm not sure about this one, but I do know log and data files are seperate.

    I have spoken to the Oracle DBAs and have enough privs to accomplish this task and they have no limits on my bandwidth (at least thats what they tell me).

    I'll go ahead and post on the oracle forum. Basically I'm looking for the fastest method to appen/migrate a sql table to an oracle table (with the same table definitions).
    I did word the question for #2 very well. Let me retry it: is the table to which you are importing data in Oracle an OLTP table (ie, is it being accessed by users while the data is being written)? Are there materialized views written against this table? Are there lots of indexes, constraints and/or foreign key relationships? Are there calculated default values? Are there triggers?

    All of these could impact your performance.

    Put a trace on the both the SQL box and the Oracle box and see what that does/does not show.

    Regards,

    hmscott
    Last edited by hmscott; 01-11-06 at 12:23.
    Have you hugged your backup today?

  7. #7
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    I do this all the time from Mainframe to ORacle
    Sql to Oracle
    DB2 to Oracle
    Sybase to Oracle.

    And using link servers is OK for small amounts of data my quess less then 50,000

    Or I agree with rdjabarov get the file to a flat file from the source system
    and use SQLLDR.

    I use mainframe SQLLDR and SQLLDR in UNIX.

    can load 1 million record in less then a minute.

    Just my preference

  8. #8
    Join Date
    Apr 2003
    Posts
    15
    The table that I'm trying to load to in Oracle is actually only used by me (so I can lock it when loading). There are no views that are pointed at the table. The table only has one index and one constraint. No foreign keys. There are default values, but the most complex one is the current date. Also, no triggers. Like I said, this is a very basic and boring table.

    I am aware that my sql server is severely underpowered and that is the main reason for me to improve performance everywhere that I can. There is no chance for an upgrade to the sql server at this time either so getting more memory also isn't an option.

    I'll go ahead and try using the SQLLDR (bcp out sqlldr in). I know this question should probably be in the oracle Forum, but how complex (or possible) is it to call the SQLLDR from the sql server and load the data without going into oracle?

    I tried the linked servers and it is actually twice as slow as using the DTS to call a BCP load. I'm assuming this is partially due to the slowness of the sql server and\or oracle server.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •