|
|
|
| Does anyone have any experience using DTS to move SQL to Oracle? I have a
very small table (4 columns of basic datatypes) with 1 million rows.
My first attempt using Microsoft OLE DB drivers for Oracle took 3 hours 51
minutes to process 1 million rows.
My second attempt used Oracle ODBC driver for Oracle and took 2 hours to
process 1 million rows.
Do any of you have any general comments or tips for speeding up this push to
Oracle? Does 2 hours for 1 million rows seem reasonable? Any input you
have would be greatly appreciated.
| |
|
| I used a dts package to do this sql2000 data to oracle 9i. Although I am
moving much less data than you it does seem a bit slow as well. You could
try to move the data using a query through access and see if it is still slow.
--
Paul G
Software engineer.
"TM" wrote:
> Does anyone have any experience using DTS to move SQL to Oracle? I have a
> very small table (4 columns of basic datatypes) with 1 million rows.
>
> My first attempt using Microsoft OLE DB drivers for Oracle took 3 hours 51
> minutes to process 1 million rows.
> My second attempt used Oracle ODBC driver for Oracle and took 2 hours to
> process 1 million rows.
>
> Do any of you have any general comments or tips for speeding up this push to
> Oracle? Does 2 hours for 1 million rows seem reasonable? Any input you
> have would be greatly appreciated.
>
>
>
>
| |
| Kevin3NF 2006-10-26, 12:12 am |
| I believe the issue is that DTS can't access any sort of "fast load"
functionality inside Oracle.
Does Oracle have any sort of DTS like utility to pull from SQL instead of
SQL pushing it?
--
Kevin Hill
3NF Consulting
www.3nf-inc.com
http://kevin3nf.blogspot.com
"Paul" <Paul@discussions.microsoft.com> wrote in message
news:47DA1FED-2AB2-4E59-93D3- B23D1945FCDC@microso
ft.com...[color=darkred]
>I used a dts package to do this sql2000 data to oracle 9i. Although I am
> moving much less data than you it does seem a bit slow as well. You could
> try to move the data using a query through access and see if it is still
> slow.
> --
> Paul G
> Software engineer.
>
>
> "TM" wrote:
>
| |
|
| I pushed up the Fetch Buffer but it seems like it really didn't make a
difference.
"Kevin3NF" <kevin@SPAMTRAP.3nf-inc.com> wrote in message
news:uOSkQUH%23GHA.3644@TK2MSFTNGP02.phx.gbl...
>I believe the issue is that DTS can't access any sort of "fast load"
>functionality inside Oracle.
>
> Does Oracle have any sort of DTS like utility to pull from SQL instead of
> SQL pushing it?
>
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com
> http://kevin3nf.blogspot.com
>
>
> "Paul" <Paul@discussions.microsoft.com> wrote in message
> news:47DA1FED-2AB2-4E59-93D3- B23D1945FCDC@microso
ft.com...
>
>
| |
| Jonathan Roberts 2006-10-26, 12:12 am |
|
"Kevin3NF" <kevin@SPAMTRAP.3nf-inc.com> wrote in message
news:uOSkQUH%23GHA.3644@TK2MSFTNGP02.phx.gbl...
> I believe the issue is that DTS can't access any sort of "fast load"
> functionality inside Oracle.
>
> Does Oracle have any sort of DTS like utility to pull from SQL instead of
> SQL pushing it?
>
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com
> http://kevin3nf.blogspot.com
I believe Oracle's SQL Loader utility would do this. It will import flat
text files. So, I suppose you could export from SQL into flat files using
DTS or similar and then use SQL Loader to get it into Oracle. There might
be a better utility to cut-out the flat file middleman, but I don't know of
one off-hand.
| |
|
| Ok. I'm having some luck tweaking the insert size and fetch buffer size.
The elapsed time is starting to come down. I'll report more findings. It's
very trial and error at this point. I was able to reduce the overall time
by 10% so far. Big woop I know, but hope springs!
| |
| Ulysses 2006-11-15, 7:14 pm |
| What values did you find produced the best results for insert size and fetch
buffer size.
"TM" wrote:
> Ok. I'm having some luck tweaking the insert size and fetch buffer size.
> The elapsed time is starting to come down. I'll report more findings. It's
> very trial and error at this point. I was able to reduce the overall time
> by 10% so far. Big woop I know, but hope springs!
>
>
>
|
|
|
|