SQL Server view query efficiency test

Time:2020-3-27
I.Test background
Develop a project, the database is SQL server. The data in the account table is one or two million, not big or small. In consideration of whether to use the view and worry about efficiency, Baidu has different opinions. Well, practice is the best proof, so let’s test it.
2、 Test environment
Win8 system, memory 8g, general disk, sqlserver2012.
3、 Table and view structure (since the company’s table is used, I have scratched out the fields to avoid disputes)
 
 
 
 

4、 Data size

The accounts data table and accounts info each have 4 million data, so there is no doubt that the view values are also 4 million data.

5、 Start testing
1. Will we use the index of the underlying table when querying views? Here I use userid to query 1000 pieces of data from where. The following two screenshots and tables are the results of query. We can see clearly that there is no difference in the efficiency between the basic table accountsinfo and the view values, so we can make sure that the view query will also use the index of the basic table.
 
 
2. From the above test, we can see that there is almost no difference between using index to query basic tables and views. What if we do not use index fields to query? Like like like. Here is the data record with “South 1999” character in my query field nickname. From the two results, we can clearly see that there is almost no difference between the two queries.
 
 

Six. test results

You can see that the view will not affect the query efficiency in SQL Server 2012, so we can use it with confidence.

by:https://blog.csdn.net/wanmdb/article/details/50358279