Thread: ssis takes long to run
02-11-10, 06:28 #1Registered User
- Join Date
- Feb 2010
Unanswered: ssis takes long to run
I want to replicate 50 tables from Oracle to SQL Server 2005 then have an daily incremental extract. These tables are really large, some of them have 500M records. I'm using SSIS but it takes very long to pull all the data from just one table, it takes 3-4 hours for the 500M records. My question is how could I speed it up? It's really a pain in the ass to wait for the SSIS to run and finish. Sometimes I'm not even sure if the SSIS package is freezed up as it takes really long.
02-14-10, 15:41 #2Registered User
- Join Date
- Jan 2010
Indexes generally speed up selects - make sure you've got indexes that work with your initial select statements.
Indexes usually slow down the INSERTs so you could look at removing the indexes on the output tables until the end and then recreate them. There's no hard and fast rule for this and it's down to trial and error.
SSIS has to pull through all of the data that you are reading, and then writing, out to the final tables by using its own work tables. If you're not writing out every column then make sure that you only select the data that you need (don't do select *).
If you're going to be doing incremental updates when this is all replicated, then maybe you can do incremental replication if this is still taking too long - presumably you have dates and/or id columns to work with so you can control all of this.
If possible, get your replication process to output status/progress information to a database table that you can query whilst the replication is taking place - then you can see if/how things are progressing - this may not be possible if you're just selecting a whole table and then inserting the data again. At least you should output row counts and status information to a table for each SSIS step so you can see the progress.
02-15-10, 08:16 #3Annie's Dog Walker
Provided Answers: 6
- Join Date
- Nov 2004
- on the wrong server
adjust your batch size to a smaller number of rows. watch your space\growth on the transaction log.If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry. Earnest Hemingway, A Farewell To Arms.