July 25, 2013 at 4:29 am
Hi
I am retrieving data from 3 tables. Total no of rows will be 19 million.
After getting this, I am inserting into staging table in SSIS dataflow task.
It is taking 35 minutes to finish and doing MERGE on target table.
It is again taking 20 minutes. How can I reduce this time?
If I run the query in SSMS, its taking just 1.5 minutes to insert into #temp table.
July 25, 2013 at 4:41 am
When you run the package, it has to load all of the data through the network into memory and then back into disk.
When you run it directly in SQL Server, you have very different circumstances, so you can't really compare those two.
Anyway, 20 minutes for 19 million rows isn't that bad. Is there anything special you do in the package, or is it just a straight load?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 25, 2013 at 5:13 am
Hi,
Thanks for the reply. It is a straight load.
We have very big datawarehouse. So, we want to reduce the time it runs.
July 25, 2013 at 5:26 am
Make the read as fast as possible (add indexes if possible, doesn't really matter if you read the entire table).
Disable indexes and constraint on the destination table if there are any.
Make sure the destination database is in single recovery model and that the database and log files are big enough so they don't have to autogrow.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply