SSIS 2005 taking very long time to datapump 2-5 million rows - Performance problem
-
Hi Everyone,
We have an ETL process which is executed by SSIS.
From the source database, when we load data which is around, 2-5 million rows everyday. (its done by a data pump step in SSIS)
Its taking long hours, 4-5 hours. (sometimes it goes even more)
Is this a normal behavior, or is there anything that we can do to kick speed, so that other jobs running on the same machine, will not be affected.
we are considering the below option.
Load data into a file from the source DB, and then load into datamart from the file.
Will that work more faster than direct data pump into datamart? or any other option?
Please give your suggestions.
Regards | Enbee
-
I dont think loading the data to a file and loading it back to the data mart is good idea. Since loading into file... and again back to datamart.. will take double the time as it is taking.
Although there may be some improved data transfer if you transfer it from a file... it is negated with the time loading into the file.
If you have any index created on table which you are going to load try dropping the index and moving the data.. of course recreating index after data load.
-
First you have to determine if your source pulling or destination pushing is slow. I would you suggest that you install Trash Destination Component and direct your source data to it. In this way you will test if the source is working with acceptable performance. If it works, then you have to concentrate on the destination.
What database is the destination data mart?
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Are you selecting your source table using a table from a drop down list? If so, try replacing it with an actual select statement. I ran into this issue and saw an improvement of hours to minutes.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply