SQL Server Performance

DTS Newbie - Loading A lot Oracle Data

Discussion in 'SQL Server DTS-Related Questions' started by chupaul, Jan 4, 2006.

  1. chupaul New Member

    Hi,

    I want to copy a subset of Oracle data to Sql Server 2000 staging table.
    There are about 22Million Rows in this table.
    I have a sql query to select the columns I want but the rowcount will be 20+Million.

    What is the best way to do this ?
    I may need to do this every month to resync Oracle Data into Sql Server.

    1. DTS job to pull the data from Oracle Server.
    2. Unload the Oracle data to a file. Copy the file to the Sql Server
    and then run DTS Task to load the file on the Sql Server

    3. Use some form of Sql Scripting to do this instead of DTS ...
    or do DTS already has a script that I can export and use as a sql script.

    Thanks, Paul
  2. simondm New Member

    I would use a DTS package. SQL can connect direct to Oracle and pull the data.

    Though if it's over a WAN you may consider exporting to a text file first and then importing to SQL. You could then compress the data over the link.

    Simon
  3. Madhivanan Moderator

    I think it is faster if you import data from text file. So do export data to text file from Oracle and import it from SQL Server

    Madhivanan

    Failing to plan is Planning to fail
  4. FrankKalis Moderator

  5. chupaul New Member

    Hi again,

    Ok, thanks for all the replies.

    1. DTS option
    I finally decided to use DTS with fetch buffer = 1000 and Batch = 5000 (commit rows).
    This copied the 20+ million in 10 hrs over our WAN overnight (acceptable - not 20 hrs ).
    I was concerned about TEMPDB and storage on the target Sql Server, but its fine.

    2.Export to a File
    The Oracle Group would not provide this option on short notice.

    3. Use a Linked Server
    Would a stmt such as:

    insert .... into MySqlTable
    select ..... from openquery(MyOracle, 'select ... from oracletable where ... ')

    Would there be issues with tempdb filling up with 20 million rows ?
    How would you make this query restartable ?

    Thanks, Paul


  6. simondm New Member

    You will probably find that this will time out over the defult timeout value for linked servers (which is 10 mins). You can change this value in the linked server properties though.

    I would strongly recommend batches rather than one statement. Something like:

    DECLARE @@ROWCOUNT int
    SET @RowCount = 1
    WHILE @RowCount > 0
    BEGIN
    INSERT INTO SQLTable SELECT TOP 1000 * FROM OracleTable WHERE ID > (SELECT MAX(ID) FROM SQLTable)
    SET @RowCount = @@ROWCOUNT
    END

    Though personally I would go the DTS option - just because it is easier to control batch sizes and if possible I would select TABLOCK on the table being inserted into (though you can still do this in the query above using WITH).

    As for your comment about the tempdb it going to be down to disk space. Preferably the tempdb would be on it's own disk too. Obviously this could have a negative impact on other server load whilst its running.

    Simon


  7. chupaul New Member

    Hi,

    Simon's comment:

    Ok, good points:

    1. I should lock the target table using WITH TABLOCK.

    2. Separate Hard Drive for TempDB:
    "Preferably the tempdb would be on it's own disk too. Obviously this could have a negative impact on other server load whilst its running".

    On our Test Server, the TempDB is on the same physical Hard Drive as the Data Files.

    What is the best way to move the TempDB db to another HardDrive ?

    Stop Test Sql Server
    Copy TempDB to hard drive
    Start Sql Server
    Change location to point to new harddrive
    Delete old TempDB db.

    Well, I beleve in Production Sql Server we will be using RAID 5 DiskArray, then is location of TEMPDB still an issue ?

    Many Thanks, Paul



Share This Page