simon.crick (10/7/2013)
So if it works 1% faster but is 10 times as complex, you still go for the "advanced" technique?
All I can say to that is "it depends". I purposely used the word "best" rather than "fastest" to differentiate between different techniques. The "best" solution doesn't necessarily have to be the most complicated (in point of fact, the less work that the optimizer has to do the better!), it is simply the solution that best fits what we're trying to achieve. My time is valuable, so if it will take a "long" time to implement a change for a "1%" performance improvement, then it's unlikely that I'd prioritise that anywhere near the top of my list of things to do.
Steve Jones - SSC Editor (10/7/2013)
I'm with Simon here. You stick with what works, what is simplist (KISS principle) and what you can get done quickly. You look for better ways to do things, but you also don't just implement something just because it's faster. Mostly because faster isn't always true. We don't operate on a linear scale, but rather a multi-dimensional one. The stuff that we do might work well for some queries/situations, and not others. It works at some data volumes, but not others.You look for better techniques and learn how to use them, but you carefully change what you know works well and is simple only when you have good reasons.
Simple techniques does not imply shoddy techniques. Basics are not wrong, they are just of less complexity.
I didn't say "use complicated methods", nor "use the fastest" (that could be said to have been implied from "best", but I find that IO statistics are more helpful than timings), I simply said "You go with the "best" technique (measurable by performance tests) for the job, regardless of complexity".
I have to test under a lot of different conditions before I can consider a solution. The clients that my company sells to are all using different hardware, with different data loads, different data distribution, different numbers of users concurrently accessing data etc etc, so I need to know that something that works in one place will work in another and if it doesn't then it doesn't get into production. "Work" doesn't just mean that the code has to be accurate and produce the same results from the same inputs, it means that it does this in a timely fashion. This may require more thought and more testing than some people when developing a solution, but it colours all of my thought processes when designing anything.