This blog is the fifth in a
series about misconceptions surrounding SQL tuning that are quite common.
Around 15 years ago, when I was a DBA both for Oracle and Sybase, a lot of SQL came from my colleagues which I needed to tune. Initially, I enjoyed the satisfaction of tuning a SQL statement that ran in a few minutes down to a few seconds. But once SQL tuning became my daily job, it wasn’t fun anymore. I started to search for any tool in the market which could help, but I found that most of the SQL tuning tools only displayed the execution plan. Some provided best practices or recommendations, but these kind of tools actually did not help me to solve numerous SQL tuning problems, since most of the time the problematic SQL syntax was okay and all the indexes were properly built. The major problem for SQL tuning is how to help the database optimizer choose a better execution plan. To be frank, Oracle and Sybase do a good job of picking a good execution plan most of time. I think there will not be more than 5% of the SQL that needs our attention, normally most human mistakes normally can be eliminated during unit test or stress test. For example, a Cartesian join SQL which is missing the basic relationship between two tables in a production database is a very odd case which I have never seen in my life in last 20 years working experience with hundreds of programmers. But it is interesting that this case is always used as a demo case in many SQL tuning products. I don’t know what company would let this kind of SQL statement be executed in a production database without even one unit test on their programs, and then think that it needs to pay hundreds of dollars for a SQL tuning tool to solve this problem. I think anyone in the company who knows SQL coding could fix this problem within a minute!
It is common that a lot of best practices in today’s SQL tuning tools available in the market are built for demonstration only. There are not many cases where these so-called best practices can be applied in a real life situation.
Some Best Practices Discussion
I will discuss some common best practices that I learn from SQL tuning products, market experts, or Oracle manuals.
Best Practices Fallacy - Use UNION ALL instead of UNION
It is quite common to see tuning tools keep recommending to users to use UNION ALL instead of UNION as a best practice. Once those tools detect a UNION key word in a SQL statement, it will give you advice to change the UNION to the UNION ALL operation. I have often wondered if those tool designers really understand the risk of giving this recommendation. Do they really understand their target users’ knowledge? They are two different, although similar, operations and naturally these two operations have different meanings. The result will be equal only in a certain data set. UNION eliminates all duplicated records from a SELECT list. But UNION ALL returns all records without the elimination process, so it runs faster than UNION. If their users are knowledgeable enough and know without a doubt that UNION ALL is the same as UNION in their environment, they may not need these kinds of tools to help them to change UNION to UNION ALL. But the problem is that those users who don’t have enough knowledge to distinguish the difference between these two operations and trust that the recommendation is good in all circumstances, the result may be disastrous, since the problem sometimes will not be discovered in a development database with a small data set during unit test, but it will definitely create problems in their production database. You can imagine what a nightmare it could cause for an online trading system with a million transactions per day.
Best Practices Fallacy - Rewriting the SQL in PL/SQL code
Rewriting the PL/SQL code instead of tuning a SQL statement is one of the best practices that I am always against, unless you really understand what you are doing and know that the database SQL optimizer cannot achieve your performance expectation. In no other case do I think that a query can only be improved by rewriting the PL/SQL, and that it cannot be improved by normal SQL tuning techniques such as Oracle optimizer hints application, new indexes, a materialized view or SQL syntax rewrite. It is a misconception with people who have some sort of experience with rewriting their problematic SQL with PL/SQL code and have gained some performance improvement. The point is that what they are doing is just creating a new “data process flow” or “execution plan” to only process their SQL, when actually, most of time, they can achieve the performance improvement by using normal SQL tuning techniques. Furthermore, the PL/SQL code will introduce extra PL/SQL process and looping overheads to the database engine. Let’s use the following simple join SQL statement for discussion:
Original SQL
SELECT *
FROM employee,department
WHERE emp_dept=dpt_id
AND dpt_id<‘ACC’
If you place the above SQL into PL/SQL, probably you will start with an Open Cursor for Department with a filter dpt_id<‘ACC’ and then loop the following SQL until the end of the Department cursor.
SQL used in PL/SQL
SELECT *
FROM employee
WHERE emp_dept= var_dpt_id
Basically, this PL/SQL code is a typical Nested Loop operation with a driving path from Department to Employee. If Oracle failed to use a Nested Loop for the original SQL and a Nested Loop is a better plan in your environment, you will gain performance improvement for this PL/SQL, but actually, the same improvement can be more easily achieved by adding the USE_NL hint to the original SQL. I wonder how many PL/SQL programmers can use PL/SQL code to achieve Hash Join or Sort Merge Join; if these Hash or Sort Merge Join is better for the SQL. Why not let Oracle to do that for you; it is the reason we pay for the RDBMS.
Factoring Out Subqueries with Temporary Tables or WITH Clause
It is quite common that people will tell you to factor out subqueries with a Temp table or a WITH clause like the following SQL:
Original SQL
SELECT emp_id
FROM employee,
department
WHERE emp_id IN (SELECT emp_id
FROM emp_small
WHERE emp_dept < 'D')
AND dpt_manager IN (SELECT emp_id
FROM emp_small
WHERE emp_dept < 'D')
AND dpt_id = emp_dept
We can extract these two subqueries and transform them to a WITH clause like the following:
Best Practice SQL
WITH temp_emp AS (SELECT emp_id empid
FROM emp_small
WHERE emp_dept < 'D')
SELECT emp_id
FROM employee,
department
WHERE emp_id IN (SELECT empid
FROM temp_emp)
AND dpt_manager IN (SELECT empid
FROM temp_emp)
AND dpt_id = emp_dept
Most people may think that this SQL looks very nice and should be encouraged as a best practice to any programmer and should not use the Original SQL syntax anymore, based on Original SQL syntax, you may interpret that the subquery (SELECT emp_id empid FROM emp_small WHERE emp_dept < 'D') should not be processed twice, so the second “Best Practice SQL” should run faster than the “Original SQL”. But the fact is that the “Original SQL” is almost 50 times faster than the “Best Practice SQL” since using a Temp table or WITH clause may save you time by not executing the factored subquery multiple times. But in reality, the Temp table or WITH clause introduces a temp table or an internal temp table (please refer to the plan in the following screen shot) into the execution plan, and this prevents the database SQL optimizer from merging those subqueries into the main query and limits the number of plans the SQL optimizer can generate during optimization. I am not saying you should or should not use this best practice, all that I am saying is that this example is used to show you that every environment is unique, sometimes this Best Practice is good, but sometimes it is bad. There is no common best practice that always works to improve performance.
Actually, SQL tuning is not using “SQL best practices” that follow generalized rules. It is the techniques that improve the SQL speed in a given hardware environment and a unique database configuration with a specific data distribution. Sometimes the best performance results may violate all the best practices that you have heard about, since your environment is always unique from everyone else’s.
The true best practice is “Don’t adapt those so-called best practices blindly!”
