Recently, I worked with a customer that had complex views referencing tables that had more than 200 columns. Some of the most expensive queries were accessing only 10 to 15 columns even though the view definitions on the wide tables used SELECT *. Most people know to avoid SELECT * because it can chew up I/O, memory, and network bandwidth. Network traffic is affected when you're reading more columns than you need; I/O and memory pressure are also affected when you retrieve 100 columns and use only 10. Less obvious is that complex views, especially ones that include SELECT * references to a wide table, might create significant compilation-time problems for you. I was shocked when I measured the performance cost in a few real-world situations. In some cases, I reduced compile times for complex queries by 4 to 6 seconds simply by creating new views that referenced only the necessary columns.
I tested some of these queries on a test server that I controlled and that wasn't under stress (apart from the queries I was running). I carefully controlled the tests to ensure that the only changing variable was that the first benchmark used a view that contained SELECT * (pointing to a table with more than 200 columns) and the second benchmark used a view that named only a few necessary columns. I repeatedly demonstrated that the wide views were taking 4 to 6 seconds longer to compile. I expected the optimizer to determine a greater plan cost when selecting the entire view, but I was amazed to see the difference was so great.
I've been doing performance tuning for well over a decade. One of the most important lessons I've learned is that there are too many best practices to keep up with. Is that good? No. Is it true? Yes. Inevitably, we disregard certain best practices for performance until we have an eye-opening experience that brings the performance issue painfully to our attention. Seeing is believing when it comes to deciding that a particular performance best practice is something you need to worry about. I've seen the light, and I'll never forget that wide views can cause significant compile-time degradation. I haven't produced a generic benchmark to measure this behavior in other environments, so I can't say whether my test behavior is normal. It's not common to see SELECT * against wide tables. But, I'm adding this information to my bag of tricks for investigating troublesome, slow-performing queries.
