This is a review which Terry – one of the dev guys here at Pixolüt – wrote which I thought was just great. This shows how important it is to have a good handle on many skills as a developer. Its no good to just be great at C# or Java – a great developer can get across the entire cross-section of the enterprise application and find the right place to expend effort in optimizing the system.
“We had a win this week, dramatically reducing the length of one of our overnight processes by adding a single index to one table in SQL Server.
Our customers had complained that the nightly outage for refreshing data had shifted so late that their screens were out of action around 8am. This was just when they most want them available, to catch the eyes of early morning commuters.
Checking the System Status messages showed that the import of data was completing shortly after 1am, but the rendering of that data was taking over 6 hours. A further check of log files for the rendering process revealed a likely problem area, a single function consistently taking one to one and a half minutes. Running with breakpoints in development let us drill down to 3 database calls that might be candidates for improvement.
In SQL Server, there’s a feature that displays the estimated execution plan for a query. Using this on the 3 calls and checking the “Estimated Subtree Cost” showed 0.006 for one, 0.01 for the next and 9.4 for the third, obviously the problem was here.
The problem query retrieves rows from a large table via a foreign key that was not part of any index. Adding a secondary index on that column reduced the estimated cost from 9.4 to 0.09.
The night after adding the index, the render improved from just over 6 hours to just under 2, the entire nightly refresh process completing well before 4am.”
