Ultra-complex query execuation plans can be expected (and are quite the norm) in data-warehouse querying systems. But, this isn't example what you should expect to see in a production system ... (zoomed out to the max) ...

Marcus explains ...

A few weeks ago, a colleague had been giving me a demo of a web application that is used by suppliers and customers to despatch and track shipments, and I was appalled at the poor performance of the application (written by an American company who shall remain nameless, but which claims to be "the global leader in providing supply chain execution and optimization solutions"). I was further astounded to learn that timeouts on certain critical operations were *routine*!

I commented that I would be happy to take a look at it, and so, finding myself with some spare time this morning, I performed a trace using SQL Profiler while a timeout-generating action was executed. Paging through the trace, I found a stored procedure which took 505306 milliseconds - that's 8.5 minutes (!!!) - to execute, at 45% server utilisation.

Delving deeper I found that the cause was a view which delivers a resultset 382 fields wide, with 24 source tables! Then to add insult to injury, it's not just a plain SELECT, it's a SELECT DISTINCT, so the performance hit rises exponentially with the number of records!! As you can imagine, at this point my jaw hit the floor - it was the worst code I had ever seen (and I'm a regular TheDailyWTF reader)! Further investigation revealed many other views which were similarly offensive, but this seemed to be the worst of the lot.

So, I came up with a plan of action to target this view - clearly radical redevelopment is needed, but we're going to junk the app in 12 months time anyway, so addressing the biggest performance hit with minimal effort seemed the best approach, and so I thought that creating a new view which exposed only the minimum number of fields to the calling code (i.e. the ones it actually used) would be the way forward, and if successful the same approach could be used with other pieces of code which used it and other views. I discussed this with my manager, but it turns out that we don't actually have rights to modify the code, we only own a license to run it!!!

Incidentally, in the past, we did have a maintenance contract with them, but we were getting such poor value for money (it was so extortionate, and by now you should have an idea of the quality of their work) that we cancelled it some time ago, with a view to developing a new app ourselves.

For the time being (the project for the new app hasn't even been kicked off yet), the only way forward I can see is to purge as much data as possible to keep the execution time down. Does anyone have any other suggestions, bearing in mind that modifying the SQL DB or ASP code is out of the question?

I was curious, so I asked Marcus what the view looks like. Incase you're interested, it's here.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!