Tuesday Wednesday again! This month’s topic, hosted by Adam Machanic, is “Crap Code”. We’ve all seen it, and we’ve all written it. Even me. Here’s one of my stories.
Once upon a time, not so long ago, I didn’t know much about T-SQL. I knew the difference between SELECT and DELETE, but the nuances of OVER() and the capabilities of CTEs were unknown to me. At the time, I was splitting my time between help desk calls and writing reports for a small company. The systems were old, but solid.
Most of my report requests came from the accounting department. The accounting system had a main table: DETAILS. This table had about 20 columns; most of them CHAR of varying lengths. This is where all our financial transaction data was stored.
As I started writing reports and becoming more familiar with T-SQL, execution time and plans, and our systems, I realized something: a lot of the “summary” reports were very slow. I couldn’t figure it out. Weren’t these simply aggregated reports, which were summing totals?
They were not.
The worst offender was a view, of a view, of a view, of a view, of a view. Each one summed or counted or averaged something different.
Now, I wouldn’t be writing this post if I could say, “The moment I realized this, I pulled out my cape, began re-writing all the queries, and saved the day.”
I did not.
I assumed they had been written that way for a reason, and they could not or should not be fixed. I continued to build reports based on them. I even wrote a couple other views based on them.
As time passed, and more data was added to the table, the report performance got worse. I knew something had to be done to fix it. At the same time, I was learning new T-SQL tricks. Eventually, I re-wrote these queries with a combination of stored procedures, functions, CTEs, and letting the reports do some of the aggregation.
Results were still accurate. Performance improved. Users were happier. I still had my sanity, and I’d learned new things.
What I Learned, and Am Teaching You
First, nested views are usually terrible for performance.
Second, keep learning. Read blogs, read books, and attend presentations. You never know when something new you read or hear will solve a long-standing problem. It is good to have as many tools in your T-SQL tool box as possible.
Third, question everything. Ask “why?” If there is a business reason for the decision, it should be explained to you clearly. If it is something another DBA or developer built, they should be able to tell you why and walk you through their reasoning. If there is no business reason, or you’re told “just because” or “that’s the way it’s always been”, feel free to challenge it. Come up with a better way to do it.
We all make mistakes. The best thing you can do is learn from them.