We had 200's of
packages in our project and I came across a package which was taking enormously
long time. I debugged the package checked the log. My doubt was that it was
indexes due to which the package is running very slow and was taking around 2-3
hours usually.
To know about indexes kindly read my article:
I tried everything. Removed indexes, kept indexes added NOLOCK but
still it was taking the same or sometimes more time than that. From the log it was
very much clear that the culprit is SCD.
I found one thing in the SCD and experimented with it
and luckily the performance was increased and to what extent. It was now able
to complete in 1 minute, I was amazed and my lead was very happy with my
findings.
The root cause of the slowness was because of Business
key which was not contained in any of the indexes and due to which when the Updates Output component was called it was taking
huge time to locate the row from the table.
Now once I added the Non Clustered Index on this Business
Key field it was running very efficiently. It was now 120 time faster.
