Which is Faster Simple Query or VIEW in SQL?

In SQL, a view is a virtual table that can be created and accessed when required similar to the tables in a database. A virtual table or view is a saved query that can be frequently accessed and does not require any storage in a database. It is created and used to reduce the complexity of subqueries.

Consider a situation where you want to retrieve the records in a table related to your work from a huge data, and you can’t perform operations or manipulate the original table data. Virtual tables (VIEWS) are used. You can filter out the records from the table using SQL queries and work on the view table. This way, the data integrity is maintained and you can easily query the view table.

Creating VIEW Table

The syntax of the view table is the same as the creation of any other table in SQL. Still, as it is a result of another query, an original table must exist from where the data or records can be inserted into the view table. Let’s see how the view table is created,

Syntax:

CREATE VIEW view_name AS

SELECT column_1,column_2,…..

FROM table_name;

The view can be created from any number of columns in a table and can be used along with the where condition.

Let’s Setup an Environment

Let’s use the below employee table to learn how the view table can be used.

employee table

Example 1: Creating a View to Combine Employee Names

In the example below, we show how to create a view table using SQL to combine employees’ first and last names into a single field that represents their full name. This view table allows you to easily and easily visualize employee names for your query and analysis.

CREATE VIEW employees_view AS
SELECT EmpId, CONCAT(FirstName, ' ', LastName) AS EmpName, Emp_sal
FROM employees;

Output:

Employees view table

Explanation: In the above output, we retrieved the records from the employee table. Now we can use this view table to to retrieve records using built-in functions or perform operations like retrieving the highest salary from the table performing subqueries etc and this will not change the original table data i.e. employees table, thus maintaining data integrity and security.

Now, let’s say some employee records are added to the original table i.e., the employees table, and think of the view table, it need not be updated or created again as the views are virtual representations of the underlying ( original ) tables, they reflect the changes made to the original table.

Let us see how the changes are reflected by the view table if we insert a record into the original table.

Example 2: Updating the Original Table and Reflecting Changes in the View

Insert a record to the employees table,

INSERT INTO employees VALUES (6,' Jane',' William',60000);

Output:

insert into employees table

Now let’s check if the same results in the view table of employees,

updated view table of employees

Explanation: From the above result, we can see that the new record has been added to the view table and as we created the view table by using concat on the FirstName and LastName columns, the same is reflected in the view table of employees.

So, when you insert new records into the employees table, and if those records inserted satisfy the conditions specified in the employees_view table query, the records will be reflected automatically in the view table when you query the view table.

Similarly, if the existing records in the employees table i.e., the original table are updated or deleted, these manipulations will be reflected in the view table.

Is a View Faster Than a Simple Query in SQL?

Now, that you understand how the view works and the significance of the view table, let us know if querying the view table is faster than using a simple query or not.

View is not faster than a simple query in SQL and it also depends on various factors sometimes views are slightly slower than simple queries in SQL because views are stored queries that are queried on the underlying tables, and do not affect performance or improve the performance of the queries.

Views are created to avoid the use of complex subqueries and to maintain data integrity and security.

Conclusion

To practically prove the above conclusion, let us use the SHOW PROFILES statement in MariaDB, which displays the information about the resource usage for statements executed in the current session. To enable profiling, set it to 1 and the SHOW PROFILES statement shows the recent queries executed in the server.

SET PROFILING = 1;

SHOW PROFILES;

Output:

show profiles to view query duration

From the above output, you can see that the Duration column specifies the execution time for each query and the duration of the simple query and the view table query are almost the same we can also observe that the execution time for the view query is slightly higher than the execution time for the simple query.