Best way to import large amounts of data from Oracle to SQL Server 2

[DanEvans]

I wanted to run this question past all of you smarties out there to see what you thought.

We are bringing in large amounts of data from an Oracle 8i server into our SQL Server (2000) in order to build a customized data mart. Our experiments show that having the data local is much faster than passthroughs, given the number of queries we run and the volume of data passing over our connection. Problem is, every morning for about two hours the lights dim and the fire truck shows up outside the building...i.e.. performance suffers for all other processes.

We are using DTS to bring over this data currently. Is there a faster method? Asking the Oracle DB owners to do something is out of the question...they serve a large group of production users and we are a reporting shop.

Looking for brainstorming, hopefully functionality within the context of SQL Server, but not necessarily. Thanks,
Dan

 

[SQLSister]

I suggest you get a good book on DTS and read about how to get better performance out of your DTS packages. One thing you might consider is the Bulk Insert task or setting your Data Transformation task to Fast Load. Bulk insert has limitations that the regular transformation task does not, so I can't really say if it will help. The DTS book I use is from SAMS called Microsoft SQL Server 2000 DTS by Timothy Peterson. I think Terry Broadbent mentioned a different book a few days ago (some time in the last week), maybe you can find the refernce.

 

[LeeDise]

DanEvans> "Asking the Oracle DB owners to do something is out of the question..."

Not an isolated problem, that... ;-)

I despise DTS. Way too hard to get meaningful feedback or error messages when something doesn't work right, which is most of the time if you're doing anything even slightly more sophisticated than copying a table with only twenty rows in it.

If I were in your shoes, I would probably write a SQL Server stored procedure that does the following for each SQL Server table you need to populate:

1. Submits (via xp_cmdshell) an Oracle query, via SQL*PLUS, for the data you need, and pipes the output to an OS file on your SQL Server box.

2. Truncates the destination table in SQL Server and drops the indexes.

3. Again, through xp_cmdshell, run BCP ("IN&quot and use the Oracle-created file as the input. You'll have to be careful that the Oracle query produces a file in the format that BCP is instructed to accept. Assuming no oddball characters in the data, it's fairly easy to set up loading up a bunch of data with tab-delimited columns. It's harder to set up format files for BCP to describe the input data, but that can be accomplished if necessary.

4. Re-define the indexes on the table.

All of this can be done inside your stored procedure, or you could use a Windows NT script. I don't know VB, but you could probably do it in VB.

I don't know if DTS is smart enough to drop indexes to a table prior to bulk copying into it... probably not, which would explain the slowness.

 

[DanEvans]

SqlSister and LeeDise--Thank you for the evident time you both put in answering this question. I looked into the process more closely (it was written by a coworker) and it is actually using DTS to pass a SQL query to the Oracle DB...it is not using BCP.

I am particularly intrigued by the xp_cmdshell option, since it offloads the overhead to the OS...something to look into. Do you know where I can find an example of using xp_cmdshell to open the Oracle (or any) utility and pass it commands. The help files don't seem to mention this. Thanks,
Dan

 

[tlbroadbent]

1) Once I took time to learn DTS, I won't go back to BCP. I find DTS can solve a lot of problems, makes importing data a snap and performs extremely well.

I like the book "Professional SQL Server 2000 DTS (Data Transformation Service)" by Mark Chaffin, Brian Knight, Todd Robinson.

2) You can create a step in DTS to DROP indexes.

3) The Data Transformation Task will perform very well if you set some options properly. SQLSister mentioned the "Use Fast Load" option. Make sure it is set ON. Set Table Lock ON.

For large imports, set the Insert Batch Size to a reasonable number such as 10000. This is important because one of the main performance issues when inserting SQL data is the growth of the transaction log. SQL Server retains all uncommitted transactions in the log until committed. When Insert batch Size is set to non-zero, SQL Server will commit transactions after every N inserts. This clears the log and reduces the need to grow the log file. This, in turn, reduces the I/O requirement and improves performance.

4) If you are *truncating the tables before inserting then drop indexes. However, if you are appending new rows, dropping and recreating indexes may take more time than inserting rows and then rebuilding indexes. You really have to test to see which works better for you.

*I hope you are truncating and not deleting. By default, DTS creates Delete statements. Deletes are much slower than truncates. Make sure you change any queries that delete all rows to Truncate Table. Note: only the sys admin, DBO DDL Admin or table owner can truncate a table.

5) You can get set a number of options to get error reporting from DTS packages. Admittedly, it is not as easy as I would like but you can get information about the data line that caused an error, progress reports. You can create exception logs for data transformation tasks, error logs for the entire package, etc. The real deficiency is the lack of reporting for an Execute SQL Task.

I admit that I have not done data transformations from Oracle. There may be other factors that are more important to consider. Perhaps, someone else can enlighten us. I do recommend that you don't abandon DTS until you've tried thoroughly analyzed your packages and implemented some of the recommended changes. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.