[ SQL performance Killers ] — VIEWS
An important part of my daily work is to help developers, database administrators, devops, sysadmins and project managers to identify (using Nazar.io) and fix bad SQL code. And the idea with the [SQL performance Killers] series is to share some practical sql tuning examples.
Continuing the [SQL performance killers series post #1, post #2 and post #3], in this fourth post I’ll go through how database VIEWS are processed and its impact on application’s performance.
Basically, VIEWS are processed using either MERGE or TEMPTABLE algorithms.
When the VIEW is processed with the MERGE algorithm, the optimizer rewrites the query by merging the view code with code from the query. In most cases, the resulting query plan will be the same as if the query had accessed the tables directly, without using a view. In this case, the use of views should not cause any performance impact.
The problem is when the TEMPTABLE algorithm is used to process the VIEW. When using the TEMPTABLE algorithm the optimizer first executes the view code and creates a temporary table with the resultset from the view and just then it executes the query on the temporary table.
Suppose you have an address table with approximately 30 million rows and you create a view vw_address_brazil with addresses only from Brazil.
Then you write a query to count how many addresses from Brazil are in the state of PERNAMBUCO using the view vw_address_brazil.
For the above query, the optimizer uses the MERGE algorithm and the query will have the same execution time and execution plan as if it had accessed the address table directly, without using a view.
Now, lets create a view vw_address_country_state with the total number of addresses from each state in each country.
Then we will use the view vw_address_country_state to query the total number of addresses from Brazil which are in the state of PERNAMBUCO.
This time the optimizer used the TEMPTABLE algorithm, causing the query to be executed in 8 seconds compared to the 0.03 seconds using the MERGE algorithm. The reason of all this slowness is that this time the database had to first execute the code from the view ( calculating the total number of addresses from each state in each country ) and saving it on a temporary table and only then apply the filter country = ‘Brazil’ and state = ‘PE’.
The MERGE algorithm could not be used because of the "count(*)" in the code of the view. In most relational databases, if the view contains any of the following constructs, the MERGE algorithm cannot be used:
- Aggregate functions (SUM(), MIN(), MAX(), COUNT()…)
- DISTINCT
- GROUP BY
- HAVING
- LIMIT
- UNION or UNION ALL
- Subquery in the select list
- Set operations (Iintersect, Minus…)
- Outer joins
“80% of the performance issues are caused by improperly coded database applications.” – Craig S. Mullins
