The amount of data shown to a user needs to be restricted not only from a performance point of view, but also from a user productivity view point. Many users ask for wild card searches that return large amounts of data when what the user is looking for a single entity on which to operate. The manual sifting of data by means of next page is a painful experience, its like looking for a needle in a haystack.The other is that we give users a lot of fields on which he can search. In order to achieve this we take recourse to writing a challenging dynamic SQL query. In real life the search is carried out 99.99 % of time on two or three fields, does it make sense to achieve flexibility we sacrifice so much performance.

And never, ever, forget to have the changes in the specifications signed off when you remove/alter some of the users 'absolutely critical' (but of little value) functionality. Even more important if they have changed their mind on its importance part way through the process.

The approach that the customer is always right, is totally wrong. Make them think they are right whilst feeding them with your own ideas is the way to go. Programmers (together with consultants) know best. Make them think your ideas are their own and yes, make them sign of on them!

R. C. van Dijk (8/24/2009)The approach that the customer is always right, is totally wrong. Make them think they are right whilst feeding them with your own ideas is the way to go. Programmers (together with consultants) know best. Make them think your ideas are their own and yes, make them sign of on them!

"But that's what you suggested to me I needed, and it sounded good! That does it, your product is crap and I'm ripping it out and replacing it with this other product."

Not many companies have the luxury of dog fooding the software that has been developed to understand the users perspective specially from a usability perspective. We provide a whole set of features which are a pain to use. For example many applications do not allow a date to be entered directly, the user is forced to use a painful calender control. The user interface should be designed keeping in view the users familiarity with software usage. It may perhaps worthwhile to provide training rather than give fancy mechanisms for the user to input data.

Maybe "The Art of Theoretical Performance Issue Handling" would be a good title.

Data volume is not the only thing that can change over time (or from DEV environment to PROD). You may have the best query you can create, "in theory", and it can timeout in production where there are concurrent users, lock escalations, I/O bottlenecks, wrong color socks (my theory as to why something works one day and not the next).

I like your list of actions taken. Swearing does seem to help and in theoretical SQL it has been proven not to hurt.

I would add one thing: the click-to-eyeball test. The developer runs the query in Query Analyzer and it takes 1 second. A "really good" developer will run it a few times, clearing the cache each time to get the average of 1 second. At this point one must pronounce it "working good in theory".

Quite correct, we tend to “pluck the pond from under the duck” or better “go on a wild goose chase” when confronted with DB performance problems, instead of pondering the root cause. It is well worth it to consider the “other” factors, e.g. time changes as well as environmental adjustments, as potential causes before we fiddle the code. Uhm, theoretical prognosis, the prognosis theoretically.

Actually, the first thing I would do to debug it would be to have the stored proc run in the actual environment in sql query editor (assuming it's not a data modification proc) and have the actual execution plan included in the query results. If it is data modification, you need to run it in a similar backup of the prod database, so you have similar table sizes and statistics. 8 out of 10 times, one hugely expensive part of the query will jump out at you. This is the quickest way to find out where the bottleneck is, because until you identify that, any 'optimizations' you do will be wasted.

I totally agree that this is what should be done first! That is even one of the biggest reasons why we always want access to the production systems of our clients!However, taking (only) this approach causes you to miss out on any possible functional issues there might be (e.g. to much data being shown or processed by the client(s) ).Taking a step back and looking at the big picture forces you to look at all possible sides of the problem. Taking a techie approach is just not always the way to go.