December 19, 2010 at 10:43 am
Hi Team sqlservercentral,
We have an ETL process running in which,around 3 million records will be Transformed and loaded in to respective tables for BO purpose.A week back,this process was consuming 3 hours to complete the task.But since a week,the time frame has increase to 5 hours.
I am really not able to find the bottleneck !
I would like to know the possibilities of improving this issue.
Looking forward,
Thanks
December 20, 2010 at 1:07 am
Cannot really explain the increase in time.
I can give you some tips on increasing loadtimes.
When you're inserting, you should use the fastload option when you choose you table to load into.
Basically, instead of performing a single insert statement per row, you use a bulk-insert.
And you should set your buffer size (= Maximum Insert Commit) to 2147483647. (sql2008 does this automatically)
When you're updating, just make sure you have your indexes right. Check this by creating an execution plan of the update statement in SSMS.
Ideally there should not be any "Table Scan" or "Index Scan" operations. Instead there should be "Index Seek" operations for every table in your join or where clause.
Hth
Steven
December 20, 2010 at 1:45 am
Also check the I/O to the disks and network congestion.
Do you use the tempdb alot?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply