Sql-server – Use a view to join tables or a stored procedure for a better execution plan

execution-planperformancequery-performancesql serverstored-proceduresview

First let me state I am a C# developer, not a SQL Server DBA, so please excuse my ignorance on SQL Server database query execution plans.

What I want to know is for a stored procedure which the results are pulled from two or more tables using primary and foreign keys to reference them, is it better practice to:

Have a view which performs the joins and contains flattened data for ALL corresponding records from the two (or more) tables and let the stored proc select from and filter that view
Let the stored proc do the join and the filtering.

I ask because in a C# world we would separate out the concerns, so one object or function would be responsible for the joining (in this case the view) and another object or function would be responsible for the filtering (in this case the stored proc).

Which option from above will perform better?
Which option from above is considered best practice?

In C# separation of concerns is generally considered more important than performance until it is proven that performance has become a serious issue with the code. But I don't know who things stack up in the SQL world!

Best Answer

Which option from above will perform better?

Best case, both will produce exactly the same execution plan, with the same runtime performance.

This can require some careful design and some fairly advanced skills, as Rob Farley mentions in his answer. Rob also has a blog post describing the core issue, and it is also discussed in one of his chapters from the SQL Server MVP Deep Dives Volume 1 book.

Which option from above is considered best practice?

This is likely more a matter of opinion that an established best practice either way, but experienced database professionals tend to limit their use of views to scenarios where they offer real and significant benefits. The more complex a view becomes, (especially if views start to become stacked or layered) the harder it is to avoid undesirable side-effects and the more likely it becomes to encounter optimizer limitations.

The query may be easier to read and maintain if it references a view, but "separation of concerns" is not something you will find database professionals discussing when choosing between a view or writing the underlying query out in a stored procedure. There are many established practices and patterns in programming that do not translate well to database work.

Always bear in mind that a (non-indexed/materialized) view is simply a stored query definition. It is expanded into the referencing query exactly as if copy-and-pasted in place manually before query compilation and optimization begins.

You did not ask for a comprehensive critique of the benefits and drawbacks of using views (which would likely be too broad a question anyway) so I will not attempt to offer one. My final word of caution is that even simple views that optimize well today (perhaps making use of Rob's idea) can easily be modified (often in an innocuous-looking way) so that queries referencing them suddenly encounter an optimizer limitation and a performance cliff.

Related Solutions

How to Get the Execution Plan for a View in MySQL

This is what I first tried:

mysql> explain view_name;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| field1  | varchar(3) | YES  |     | NULL    |       |
| field2  | varchar(3) | YES  |     | NULL    |       |
| field3  | bigint(21) | NO   |     | 0       |       |
| field4  | bigint(21) | NO   |     | 0       |       |
+---------+------------+------+-----+---------+-------+

Obviously this doesn't work -- it's the same as doing describe view_name.

However, select * from view_name seems to work:

mysql> explain select * from view_name;
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | PRIMARY     | <derived5> | ALL  | NULL          | NULL | NULL    | NULL |   18 |                                 |
|  1 | PRIMARY     | <derived3> | ALL  | NULL          | NULL | NULL    | NULL |  105 | Using where; Using join buffer  |
|  5 | DERIVED     | <derived6> | ALL  | NULL          | NULL | NULL    | NULL |   68 | Using temporary; Using filesort |
|  6 | DERIVED     | basetable  | ALL  | NULL          | NULL | NULL    | NULL |  928 | Using temporary; Using filesort |
|  3 | DERIVED     | <derived4> | ALL  | NULL          | NULL | NULL    | NULL |  386 | Using temporary; Using filesort |
|  4 | DERIVED     | basetable  | ALL  | NULL          | NULL | NULL    | NULL |  928 | Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+

MySQL – How to Decide Which Execution Plan is Better

Multiplying the rows is invalid for several reasons:

Many times, the rows examined are an approximation (based on statistics, not accurate), good for query plan selection, but not for performance calculation
The total number of rows examined on a nested loop join (A, B) is not rows_examined_on_table_A * rows_examined_on_table_B, but rows_examined_on_table_A + rows_returned_from_table_A * rows_examined_on_table_B. Where clauses can make a huge difference on that, although it is true that the mentioned calculations is many times used as a broad approximation, assuming the indexes are being created properly and the main causes of filtering out results.
Modern MySQL versions do not use always a nested loop join approach for executing joins and subqueries. Check 5.6 subquery optimizations and other optimization documents on the same manual. Additionally, some of the new optimization techniques do not modify the predicted examined rows, which at some times can be way lower than the one printed, even if it has been calculated exactly.

In particular, on your first query, you are hitting a well know MySQL bug? limitation? in which an IN subquery is identified as a DEPENDENT SUBQUERY, even if it really isn't, forcing the outmost query to be executed without an index (full table scan) in order to test all possible values of the first table. That is usually an indicator that it is a bad query. It seems not to bee too bad in this case, as the table is small, but it is usually an indication of bad performance.

The other thing that should bring your attention is the Using temporary; Using filesort. Filtering is not the only thing where you should focus, as these extra pieces of information are telling you that a large sorting has to be done using a temporary table (that may or may not end up on disk, but at least has to be materialized). That is another indicator of potential bad performance, that in some cases can be avoided with the right indexes.

I will not tell you which is the right query to use (partially, because I do not know all the variables: indexes, tables structure, etc., and in most cases it will depend on the particular hardware/resources available), but I will tell you the tools to decide:

Profile the query- obtain the post execution times and how much of it it is being invested in what. You can use SHOW PROFILES up to 5.5, and the performance_schema starting with 5.6.
As time can be sometimes variable (for example, depending on other queries being executed at the same time, depending on the buffer pool contents) Obtain post-execution statistics with SHOW SESSION STATUS. In particular:
```
FLUSH SESSION STATUS;
SELECT ... ;
SHOW STATUS like 'Hand%';
```
will give you the exact number of handler calls done (approximately, the number of rows read and written for that particular query- although that is not 100% accurate, as it depends on the particular engine implementation).

You may also want to monitor other status variables, like the created temporary tables, created temporary tables on disk and sort passes/sorted rows.

All of these will give you post-execution, exact, time-independent parameters to evaluate the performance of a query. Percona even has a patch for the slow log to output that information on the logs instead of using performance_schema.

With those extra pieces of information you will be able to evaluate more objectively which query is better, and not relying exclusively on EXPLAIN, which only provides limited pre-execution information.

Best Answer

Related Solutions

How to Get the Execution Plan for a View in MySQL

MySQL – How to Decide Which Execution Plan is Better

Related Question