copying data oracle2sql

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
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
"Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
news:1174662643.833343@.loger.vpmarket.int...
> 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
>
|||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.
"Jeje" <willgart@.hotmail.com> wrote in message
news:OcLKPJmbHHA.4476@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> 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
> "Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
> news:1174662643.833343@.loger.vpmarket.int...
could[vbcol=seagreen]
|||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.
"Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
news:1174888638.520904@.loger.vpmarket.int...
> 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.
>
>
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:OcLKPJmbHHA.4476@.TK2MSFTNGP03.phx.gbl...
> could
>