November 16, 2012 at 6:50 pm
Hi All,
I have an ssis package which loads 1.2 million records of data from oracle into SQL SERVER 2008R2(with 32 GB RAM) dataware house table once every week. (truncating and loading)
This process is taking 5 hours to complete the load of 1.2 million records.
If any body did the same thing,i wanted to know their average time to complete the load.
I am new to ssis. Please advice me. Is 5 hours an optimum time ??
Note: the destination table is de-normalized and no indexes are there on it because it is in the dataware house.
November 17, 2012 at 9:22 am
tinnusqlserver (11/16/2012)
I have an ssis package which loads 1.2 million records of data from oracle into SQL SERVER 2008R2(with 32 GB RAM) dataware house table once every week. (truncating and loading)This process is taking 5 hours to complete the load of 1.2 million records.
If any body did the same thing,i wanted to know their average time to complete the load.
I am new to ssis. Please advice me. Is 5 hours an optimum time ??
Note: the destination table is de-normalized and no indexes are there on it because it is in the dataware house.
There's really too many variables here to compare your system load with someone else's. What is the network throughput? What's the load on the source and destination server at time of transfer? What's your disk speed?
The real question to ask is does the 5 hours fit into your load window? If you have all weekend to load your data, no problem. If you need round-the-clock up-time, you'll have to investigate different loading strategies rather than complete truncate and reload.
Rob
November 17, 2012 at 1:21 pm
Additional questions?
- is SQL Server Standard or Enterprise, 32 bit or 64 bit
- what version of Oracle
- which source and destination connectors are you using
Microsoft Connectors for Oracle by Attunity
OLEDB
ADO.Net
ODBC
- what is the record length in bytes
- what transforms are being performed in the SSIS package if any
- what is packet size of Source and Destination connections in Connection Manager
We currently have several SSIS packages transferring between 600+million to a 14+ million records from Oracle 10g to SQL Server 2008 R2 Enterprise. Load times range between 1 hour for 14+ million to 12 hours for 600+ million records (depending on server load) utilizing Microsoft Connectors for Oracle by Attunity.
references
http://msdn.microsoft.com/en-us/library/ee470675(v=sql.100).aspx
http://www.robkerr.com/2010/03/oracle-driver-configuration-for-ssis.html
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply