Quickly fetch data from View or Table?

Question

hi we have a stored procedure that is assigned on a daily basis that retrieves records from a table that has huge data after filtering. my question is: if I create a view in a table and extract data from the view, will it be faster or slower?

Answer 1

The standard view, it should not make any difference, since the internal SQL is simply expanded in the query. Note that this also applies to the built-in tabular values ​​of user-defined functions (think of a "parameterized view").

However, if you make this an indexed view , you will see a performance improvement.

Answer 2

Just remember that a view is just a select statement (indexed views are different). If you have:

SELECT * FROM TABLE 

And this is in the procedure, if you put the same in the view, and then do:

 SELECT * FROM VIEW 

Within the procedure, there is absolutely no difference between the two. But, if things get complicated, so you join a lot of tables, then it really depends on how they are accessed.

For example, if you create a view that refers to 6 tables, and then you write a query that just needs to pull data from 3 of these tables, you can use a process called simplification that happens during the optimization process, and you Look at the plan, which refers only to 3 tables. However you cannot. If not, then a query that you write against three tables will work faster than a plan against a view that accesses more than 3 tables.

If you start nested views with views that invoke views or join views, you can see very serious performance degradation.

In general, if you work with stored procedures, I would suggest that you simply write your queries directly to the tables. This will not hurt performance at all, and it can help you avoid problems with nested views and simplify planning.

Answer 3

just complementing @AdaTheDev's answer:

 the same applies with table-valued user defined functions 

which is true for built-in table functions, but not 100% true for a multi-valued table function. This second type of function will use a lot more resources (memory) than the first

Presenting an index can help, but keep in mind that it can significantly increase the size of your storage.