Case study – Stored procedure 181x faster

Here is another significant improvement.

Problem summary

Slow procedure killing SQL Server performance.

The client was experiencing severe issues with the application stability.

What we done

To improve it, we created an indexed view to improve the store procedure performance.

Total improvement

Sometimes changes can make TSQL run 20,000% faster, sometimes 50%, other times slicing 20% off the cost is a great help.

Tuning results vary. They vary based on the query’s complexity, how much tuning has gone into it already, etc.

But we rarely cannot make T-SQL perform faster.

Technical Background

SQL Server Views can be a great helpful tool for database developers. It provides a backward compatible interface to emulate a table whose schema has changed (simplicity), business logic consistency, and security.

Indexed Views (also called Materialized Views) is a view that has a unique clustered index on it. In the right situations, they can drastically improve the performance of queries.

But, as with all features in SQL Server, there are some limitations to indexed views. Here a few of them:

  • This view is created with the WITH SCHEMABINDING option. So, you can not modify the underlying columns and tables;
  • It can not reference tables in other databases and others views;
  • User-defined functions referenced in the view must be created by using the WITH SCHEMABINDING option.

Indexed views are a nice feature, but you have to specify the WITH (NOEXPAND) hint to get it used.  Remember to be cautious when using any hint.

Check out more case studies here!

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS