"Jeje" <willgart@hotmail.com> wrote in message
news:OcLKPJmbHHA.4476@TK2MSFTNGP03.phx.gbl...
> 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
> >
> >

"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...
>> 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
>> >
>> >
>