T-SQL Tuesday

T-SQL Tuesday #21: Piling it on…

T-SQL Wednesday Alternate Logo

T-SQL Wednesday

Adam Mechanic hit it on the head: we must do better in our coding practices. We should follow Dairy Queen in not stopping at good enough. In today’s T-SQL Wednesday, I’m going to lay bare a bit of code that I’m not proud of, and I’ll tell you why you ought to beware as well.

Weaving a Tangled Web

The last project I completed at the foundation was a rush job. Even if I hadn’t put in notice, it was going to be a rush job. And in my rush to finish the job, I wrote a nested view. A nested view is a view that references another view. And possibly another. And another. If you aren’t too careful, you could keep layering views that rely on others.

You inquire, “What’s the big deal?” SQL Server is a smart piece of software, and each time a view is sent to the optimizer, it is resolved for its component parts. Each view must resolve before moving on to the next view. If the views are substantial – millions of rows – SQL Server will ramp up its CPU cycles and performance will tank as the optimizer works through each layer of the nested query. The query can grow out of hand quickly.

Even if you weren’t querying millions of rows with each view, troubleshooting nested views is a pain. If you return unexpected results, it’s difficult to figure out where the issue may lie when there are so many views to consider. Each layer is a chance to go wrong.

Untangle the Web

As Adam stated, we must do better. There are options to unnest views and optimize your query. Your fix, of course, depends on the situation. These are a few of the basic options available.

  • Stored Procedure: In my case, I nested a single view. Because I was returning and transforming a few hundred rows to store in a new table, I could have easily re-written my view-on-view action into a stored procedure, stored the results in a temp table, and then queried that temp table instead.
  • Functions: If you are performing a number of data transformations, you may see better performance by turning those nested views into functions.
  • New View: If you have many levels of nested views, it would be worth the time to take a look at what each view returns and its purpose. It’s probable that the nested query can be written so that it references the base tables directly.
Standard
  • http://blogs.lessthandot.com Jes Borland

    Awesome – great minds think alike this month!

    • Matt

      Yep yep! I haven’t had a chance to read through all the posts yet, I wonder how many other folks have written about this?