April 28, 2010 at 7:18 am
Table Employee - Production (50 million records)
Table Employee - Dev
Table EmpLookup - Dev - (10 million records)
Table Employee - Stage
1. I have to load a flat file every week into Dev.Employee
2. bring Production.Employee records into Stage.Employee
3. Update Stage.Employee from Dev.Employee (hoping there might be change in the existing records)
4. Do a lookup on Dev.EmpLookup to update a flag column in Stage.Employee
5. Push everything from stage to Production.
This process is done through SSIS but still sql scripts are used inside the package. The problem is that this process is taking nearly 3 days to do as the production table has 50million records and every flat has 10million records to update.
Would like to know if there is any better way to cut down the time.
April 28, 2010 at 7:54 am
A general approach to performance tuning is to first find out which part of the process is taking the longest and attack that first. Then repeat. Each bottleneck is attacked separately.
You first need to find out how much time each part of the process is taking. Also, how much of this process is record based (i.e. one record at a time, vs. set based)?
April 28, 2010 at 8:18 am
Check the TSQL to see the execution plans & tune as needed. Validate that the SSIS package is not performing scripts or operations that prevent the pipeline from acting in a batch fashion.
Like the last post said. Identify the slow points and address those first.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply