sql server data warehouse: SSIS Performance Issue -- sql server data warehouse

SSIS Performance Issue

Frank
12/5/2006 8:00:23 PM

sql server data warehouse:

Hi Experts,

We used SSIS as ETL tool to design our first data mart. One of our SSIS
package is trying to load data from one Oracle DB to another Oracle DB
(no any data transformations, and using Native OLEDB\MS OLEDB Provider
for Oracle). The total rows is around 3 millions. But it took forever
to run it. The average loading speed is 5000 rows/minutes.

The configuration of my workstation is 4cpu, 2.8GHz, 1GB RAM. Even I
run the package in the server, which is Inter Xeron CPU, 3.2 GHz, 3.75
RAM, the it is still incredibly slow. And I've tried to set
DefaultBufferSize to 70MB, and reduce DefaultBufferMazRows to 5000. But
it 's not changed. I am wondering which parameter should I have to
adjust to improve the performance. Thank you so much for your time.

Frank

Re: SSIS Performance Issue

Jeje
2/10/2007 12:15:45 PM

does your drivers support a fastload option?
if not, then 1 row at a time is inserted and the speed depend on your RDBMS
server and this produce a lot of transactions. (you'll exeucte 3millions of
insert commands...)

A fast load option like SQL Server support can load 100 000rows/sec or more.

Try to use another driver if there is no fast load option available.
I don't know how easy is this in oracle but you also can do this:
dump the source into a flat file and use Oracle internal fast loading
commands to load this flat file into the destination table.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server data warehouse group.