Returning large dataset from Oracle to SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Returning large dataset from Oracle to SQL Server

Author

Topic

strauss_jon
Starting Member

23 Posts

Posted - 2010-07-27 : 09:06:26

Hi there

Does anyone know the fastest way to return a relatively large dataset from Oracle 11g to SQL Server 2008? I am currently using OPENQUERY (pass through) via a linked server but this takes around 40 mins for 400000 records.

The 'pass through' bit should mean the Oracle remote server will do all the work/filtering/execution plan etc rather than Oracle returning the whole dataset and SQL Server doing the filtering so I would assume it would be quicker than this.

Is the best way to spool the Oracle output to a file and then bulk insert from SQL Server? Is there a more direct and faster alternative to OPENQUERY?

Cheers in advance, Jon

Kristen
Test

22859 Posts

Posted - 2010-07-27 : 11:08:21

I don't think there is a faster alternative to OPENQUERY.

I'm presuming you just have a simple statement at the MS SQL end?

INSERT INTO MyStagingTable
SELECT *
FROM OPENQUERY(MyOracleServer, 'SELECT .... FROM ORACLE_TABLE')

and no additional local joins, no foreign keys nor triggers on that table, nor anything else like that?

We pull about 40,000 rows from Oracle every few minutes and it doesn't take long (that's a bit abstract! but the query itself is a Stock Level query which I know Oracle itself coughs on checking Free & Reserved Stock and so on, so the fact that it maybe takes a minute to run is certainly not representative of the time to just transfer the data)

You could use SSIS to "import" the data (from an Oracle query I expect, although I haven't tried it) and this may have the benefit that it will handle the incoming data in "batches" so may be more efficient.

Using an exported file in some delimited format would definitely be fast to import. Ideally you will pre-order the file in (SQL's) clustered-index order, and then you can provide a "hint" that this is the case which will speed up the loading. There is a command line tool ("BCP") for this, or you can do it in SQL using "Bulk" commands - or SSIS

strauss_jon
Starting Member

23 Posts

Posted - 2010-07-28 : 04:19:34

Thanks Kristen

Yes, I was pretty much doing the same thing but created a view with the openquery and extracted the data from the view.

I will try the bcp method via SSIS and i'll post my results.

Thanks again...

Kristen
Test

22859 Posts

Posted - 2010-07-28 : 05:14:34

Something else wrong then if 400,000 records takes 40 minutes to "pull".

Network packet size ... NamedPipes instead of TCP/IP ... Goofy Name Resolution sending you round the houses?

Probably need to look at network traffic to see what the packets look like, and how quickly the arrive. Maybe there is latency between the SQL and Oracle boxes? (PING elapsed time may tell you the answer to that).

strauss_jon
Starting Member

23 Posts

Posted - 2010-07-29 : 06:22:27

Spooling to a file and bulk inserting combined take < 2 minutes! Do you know how to spool the file from SSIS? Definately the way forward...

I have an OLE DB connection with an Execute SQL task and I want to run a series of SQL as a script such as this test example below. Running it as an sql file is fine but the execute sql task doesn't like the set commands or the semi colons etc. I would have assumed this would act somehow as a pass through since it's connected to an oracle adapter. Obviously the sql server engine wouldn't like this natively but I didn't think this would matter in this case?

SET HEAD OFF
SET FEEDBACK OFF
SET TERMOUT OFF
set echo off
set newpage 0
set space 0
set pagesize 0
set feedback off
set trimspool on
set heading off
set linesize 12000
SPOOL /tmp/TESTING.txt
SELECT SYSDATE FROM DUAL;
spool off
EXIT

Any ideas?

strauss_jon
Starting Member

23 Posts

Posted - 2010-07-29 : 06:45:16

Along the same lines, is it possible to call a unix script from SSIS? This is also an option. Not much on the web about unix and ssis....

Thanks

strauss_jon
Starting Member

23 Posts

Posted - 2010-07-29 : 07:52:45

Don't worry, sorted it. Execute Batch Process executes batch file which initiates an sqlplus session which in turn calls the spool script.

Thanks for your help. Cheers, Jon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources