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.
See more SQL Server Performance Optimization examples here.
