Wednesday, June 24, 2009

Use view to improve query performance


Recently I came across two such scenarios where a large dataset (1,000,000 + rows) was being retrieved using a complex set of joins in stored procedure, we did all the regular stuff
1. Checked Indexes - made sure they are on the right columns and verified the fill factor
2. Re indexed the database for better performance
3. Checked and removed the non essential joins
4. Verified join types

we did have some improvement in performance, but nothing like we had with using views instead of complex select query, we were able to get performance levels increased by 10 t0 40 times in specific cases.

Now the question is why?
I read the following while searching on another topic and it struck me, a view is an optimized query object, SQL server itself chooses the best execution plan and then when we do a filter on the view the nested loops are decreased considerably as only one select is being filtered rather that all the tables in the join..... It really helps

From the Database Management System (DBMS) perspective, a view is a description of the data (a form of metadata). When a typical view is created, the metadata is defined by encapsulating a SELECT statement that defines a result set to be represented as a virtual table. When a view is referenced in the FROM clause of another query, this metadata is retrieved from the system catalog and expanded in place of the view's reference. After view expansion, the SQL Server query optimizer compiles a single execution plan for the executing query. The query optimizer searches though a set of possible execution plans for a query, and chooses the lowest-cost plan it can find, based on estimates of the actual time it will take to execute each query plan.

No comments: