copying data oracle2sql

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ramunas Balukonis - 23 Mar 2007 16:10 GMT

Hi,
I discovered that copying data from oracle using "openquery" with linked
server is sevaral time slower that using "dtsexecui" (dts import/export
wzard). I used the same query for both methods. Does anybode has expirience
solving slowly openquery behaviour? My linked server has "allow in process"
enabled.
Seems dts import/export wizzard doesn't use rollback segment? May I could
rewrite my query?

select *
from openquery (sapr3_prd, '
select MATNR, WERKS, LGORT, LFGJA, LFMON, LABST
, UMLME ,INSME, EINME, SPEME, RETME, VKLAB
, VKUML
from sapr3.mardh
where mandt = ''600''
')

Ramunas

Reply to this Message

Jeje - 24 Mar 2007 23:18 GMT

openquery is not designed to move large amount of data.
the import/export wizard use the bulk insert feature of SQL Server which
result on the highest performance you can for a loading.

"Seems dts import/export wizzard doesn't use rollback segment?"
what do you mean by this?
you can import data in 1 transaction or multiple transactions

> Hi,
> I discovered that copying data from oracle using "openquery" with linked
[quoted text clipped - 17 lines]
>
> Ramunas

Reply to this Message

Ramunas Balukonis - 26 Mar 2007 06:57 GMT

you think the bottleneck is the insert part into sql, not select from
oracle? i think different - sql server is able to select data from oracle in
different way.
the openquery from oracle always fails with message: "ora-01555 snapshot too
old: rollback segment number XX with name YYY to small". my idea is to use
fresh table and load data in the same transaction like:

begin tran
truncate table MyTable
insert into MyTable
select * from openquery (sapr3_prd, '.....')
if @@error <> 0
rollback tran
else
commit tran

is it possible to achieve this with dtsexecui? it's strange but dtsexecui
never get this error, so my idea this is because import/export wizard
doesn't use rollback segment in oracle. you said that possible to import
data in one transaction, what you mean? because when I use dtsexecui, and
try to load 100 mln of rows and if import fails after 80 mln, so these 80
mln of rows will be added to the table.

> openquery is not designed to move large amount of data.
> the import/export wizard use the bulk insert feature of SQL Server which
[quoted text clipped - 25 lines]
> >
> > Ramunas

Reply to this Message

Jeje - 27 Mar 2007 00:36 GMT

no, the insert into sql server is not the problem.
the problem is the loading part from the Oracle driver, and the overhead of
the openquery syntax.

using a bulk insert command through an SSIS package will provide far better
performance.

> you think the bottleneck is the insert part into sql, not select from
> oracle? i think different - sql server is able to select data from oracle
[quoted text clipped - 52 lines]
>> >
>> > Ramunas

Reply to this Message