Import Slow

Question

Import Slow

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

tai kim SSC Rookie Points: 42 More actions · Answer 1

Joe,

In my case, I have tested the select statement on it's own in Query Analyser and found insignificant change in performance (I put a sample of our code in a previous post). I've tested the select within TOAD (with a direct connection) as a comparison and it was much faster (minutes not hours).

As for your suggestions:

1. The default recovery model is "FULL" but I will look into changing this during the load.

2. I'm not looking at my DTS packages yet but I will look into these when I get to them.

3. Since the select test didn't show any improvement, this is probably not the case.

3. Again, since the select test didn't show any improvement, this is probably not the case.

4. I'll look at this but we have a pretty robust SQL server w/ quad xeon processors, 4G of ram.

We have not customized or reconfigured MDAC or the Oracle 8.1.7 client either.

tai kim SSC Rookie Points: 42 More actions · Answer 2

VERY bad. The sample code i posted retrieves just under half a million records and it takes almost 8 hours. Definitely bad compared to the less than 2 minutes your code takes for the same number of records.

David Lester Hall of Fame Points: 3647 More actions · Answer 3

Hi All,

Nightly I have dozens of DTS imports from an Oracle DB to SQL2000. I experienced the same performance issues when using a linked server for any tables over a certain row count, in my case about 500k.

I tried using an Oracle DB Connection (ODBC Driver for Oracle) and a SQL connection, with a transform between them. This raised the through put to 1.5 million rows in about 8 min. Perhaps not the fastest possible, but certainly better than hours.

(In total we import about 36.5 million rows over a 3 hour period using this method.)

Hope it helps.

corythewiz2002 Valued Member Points: 52 More actions · Answer 4

Please provide more info how you are importing 1/2 million records from ORACLE to SQL Server 2005 using linked server.

I have an ORACLE Linked Server and am using SSIS to run a stored procedure to do a SELECT FROM (oracle) and INSERT into SQL ...

It keeps taking longer and longer and is up to 2 hours just for 1 table of a million records with just 4 fields.

Thanks in advance

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Cory, why in the world would you post a question to a forum thread that had it's last post over 2 YEARS ago?? :w00t:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

Heh... probably the same as me... found something slightly interesting and didn't read the date... 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)