Click to view Tosska SQL Tuning Expert (TSES™) for SQL Server 1.5.1 Release Notes
How to Tune SQL Statement with CASE Expression for SQL Server II?
We have discussed how to tune a CASE expression SQL with hardcoded literals in my last blog:
How to Tune SQL Statement with CASE Expression for SQL Server I?
SELECT *
FROM EMPLOYEE
WHERE
CASE
when emp_id < 1001000 then ‘Old Employee’
when emp_dept <‘B’ then ‘Old Department’
ELSE ‘Normal’
END = ‘old Employee’
If I change the hardcoded literal to a @var, what will be the performance of the last blog’s rewritten SQL?
SELECT *
FROM EMPLOYEE
WHERE
CASE
when emp_id < 1001000 then ‘Old Employee’
when emp_dept <‘B’ then ‘Old Department’
ELSE ‘Normal’
END = @var
I use the same method in my last blog to rewrite this SQL into the following multiple OR syntax, but the SQL Server optimizer change back to a full table scan of the EMPLOYEE table. It is because the SQL Server cannot do a good cardinality estimation of the variable of @var.
select *
from EMPLOYEE
where emp_id < 1005000
and ‘Old Employee’ = @var
or not ( emp_id < 1005000 )
and emp_dept < ‘B’
and ‘Old Department’ = @var
or not ( emp_id < 1005000 )
and not ( emp_dept < ‘B’ )
and ‘Normal’ = @var
We can rewrite the CASE expression into the following syntax with multiple UNION ALL statements, this syntax is more complicated than the rewrite with multiple OR conditions in my last blog. But it can make SQL Server improve the query plan to be more efficient.
select *
from EMPLOYEE
where emp_id < (select 1005000)
and ‘Old Employee’ = @var
union all
select *
from EMPLOYEE
where ( not ( emp_id < 1005000 )
and ‘Old Employee’ = @var
or @var is null )
and emp_id >= 1005000
and emp_dept < ‘B’
and ‘Old Department’ = @var
union all
select *
from EMPLOYEE
where ( not ( emp_id < 1005000 )
and ‘Old Employee’ = @var
or @var is null )
and ( not ( emp_id >= 1005000
and emp_dept < ‘B’
and ‘Old Department’ = @var )
or @var is null )
and emp_id >= 1005000
and emp_dept >= ‘B’
and ‘Normal’ = @var
Here is the query plan of the rewritten SQL and the speed is 0.448 seconds. It is 5 times better than the original syntax. People may think that there are two table scan operations of EMPLOYEE that will slow down the whole process, but actually, the corresponding filter operations will stop the table scan operations immediately due to the filter conditions ‘Normal’ = @var and ‘Old Department’ = @var will not be satisfied. This kind of query plan cannot be generated by SQL Server’s internal SQL optimizer, it means that you cannot use Hints injection to get this query plan.
This kind of rewrite can be achieved by Tosska SQL Tuning Expert for SQL Server automatically.
Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited
How to Tune SQL Statement with CASE Expression for SQL Server I?
Here the following is a simple SQL statement with a CASE expression syntax.
SELECT *
FROM EMPLOYEE
WHERE
CASE
when emp_id < 1001000 then ‘Old Employee’
when emp_dept <‘B’ then ‘Old Department’
ELSE ‘Normal’
END = ‘old Employee’
Here the following are the query plans of this SQL, it takes 2.23 seconds in a cold cache situation, which means data will be cached during the SQL is executing. The query shows a Full Table Scan of the EMPLOYEE table due to the CASE expression cannot utilize the emp_id index or emp_dept index.
We can rewrite the CASE expression into the following syntax with multiple OR conditions.
select *
from EMPLOYEE
where emp_id < 1005000
and ‘Old Employee’ = ‘Old Employee’
or not ( emp_id < 1005000 )
and emp_dept < ‘B’
and ‘Old Department’ = ‘Old Employee’
or not ( emp_id < 1005000 )
and not ( emp_dept < ‘B’ )
and ‘Normal’ = ‘Old Employee’
Here is the query plan of the rewritten SQL and the speed is 0.086 seconds. It is 25 times better than the original syntax. The new query plan shows an Index Seek of EMP_ID index.
This SQL rewrite is useful when the CASE expression is equal to a hardcoded literal, but if the literal “ =’Old Employee’ ” replaced by a variable “ = :var ”, this rewrite may not be useful, I will discuss it in my next blog.
This kind of rewrite can be achieved by Tosska SQL Tuning Expert for SQL Server automatically.
Expert (TSES™) for SQL Server® – Tosska Technologies Limited
The Importance of Query Optimization in SQL Server Plus Tips
The database is a vital part of a majority of IT systems as it contains that data that needs to be processed for utilization. SQL is the standard language for fetching data from the database using queries and query optimization in SQL Server helps maintain database performance.
The queries used to communicate with the database are almost always simple and quick. The most common operations include the four major data manipulation techniques (create, select, update, and delete) that execute at quick rates in most cases. However, as you already know, they aren’t the only tasks an administrator has to do in a database.
Why Experts Recommend Query Optimization in SQL
Operational databases may have a requirement to execute statements that take a long time. For instance, queries that need access to several tables or include tasks such as aggregation.
Although statements that operate on data warehouses aren’t time-critical, the ones running on operational databases are. They are necessary to fetch the requested information as quickly as possible – which makes SQL Server performance tuning important. One of the best examples of systems that need optimal SQL is one that has access to a geo server. A geo server contains millions of geographical information spread across countless tables.
Moreover, the database operations taking place on this data is often performance heavy. Such tasks may include the intersection of lands and calculations of area, the distance between two geographical locations, etc.
These involve complex geo operations that need a large amount of data present in multiple tables. If the SQL statement used for these operations isn’t efficient, it could take days or months to fetch the results. This is where the DBA will need query optimization in SQL Server to ensure the users don’t have to wait too long to get a result.
Some Quick Tips for Maintaining Optimal Database Query Performance
Take a look at some time-tested techniques of optimizing queries in SQL that not only save a lot of time and resources but are also useful for a wide range of databases:
- Check efficiency using LIMIT – Many times, the queries have to run on a huge scale of data. You would not want to wait for your statement to execute completely before finding out that you have used the wrong statement or it was inefficient. Therefore, limiting your statement to a smaller amount of information can help you check its validity and with SQL Server performance tuning. Once you’ve run the query and are satisfied with its efficiency, you can carry it out on the scale you want.
- Long statements can help – At times, you may have written statements that may seem simple to you as they didn’t take long to understand. However, they took too long to actually execute. In databases involving time-critical operations, the opposite would be preferable. Even though the queries will become complex and take a bit longer to understand, they will save a lot of time.
- Give preference to quicker data structures – This depends on the programming-based knowledge of the DBA. For example, integer comparisons are far quicker than string comparisons.
- Don’t be tempted to use IN operator – Checking the existence of certain data in a table may be important. But try not to use the IN operator as it slows down the result.
In Conclusion
As mentioned before, these tips are useful for a majority of databases out there. Since SQL is a declarative language, certain databases are likely to optimize for the cases we have talked about above.
However, test the tips before you try to apply them for query optimization in your database. In case some of these don’t work, you may want to consider using a tuning tool to make things easier. Get in touch with us to know how our tuning tool can simplify database query optimization for you.
How to Tune SQL Statement with CASE Expression for SQL Server I?
Here the following is a simple SQL statement with a CASE expression syntax.
SELECT *
FROM EMPLOYEE
WHERE
CASE
when emp_id < 1001000 then ‘Old Employee’
when emp_dept <‘B’ then ‘Old Department’
ELSE ‘Normal’
END = ‘old Employee’
Here the following are the query plans of this SQL, it takes 2.23 seconds in a cold cache situation, which means data will be cached during the SQL is executing. The query shows a Full Table Scan of the EMPLOYEE table due to the CASE expression cannot utilize the emp_id index or emp_dept index.
We can rewrite the CASE expression into the following syntax with multiple OR conditions.
select *
from EMPLOYEE
where emp_id < 1005000
and ‘Old Employee’ = ‘Old Employee’
or not ( emp_id < 1005000 )
and emp_dept < ‘B’
and ‘Old Department’ = ‘Old Employee’
or not ( emp_id < 1005000 )
and not ( emp_dept < ‘B’ )
and ‘Normal’ = ‘Old Employee’
Here is the query plan of the rewritten SQL and the speed is 0.086 seconds. It is 25 times better than the original syntax. The new query plan shows an Index Seek of EMP_ID index.
This SQL rewrite is useful when the CASE expression is equal to a hardcoded literal, but if the literal “ =’Old Employee’ ” replaced by a variable “ = :var ”, this rewrite may not be useful, I will discuss it in my next blog.
This kind of rewrite can be achieved by Tosska SQL Tuning Expert for SQL Server automatically.
Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited
How to Tune SQL Statement with OR conditions in a Subquery for SQL Server?
The following is an example that shows a SQL statement with an EXISTS subquery. The SQL counts the records from the EMPLOYEE table if the OR conditions are satisfied in the subquery of the DEPARTMENT table.
select countn(*) from employee a where
exists (select ‘x’ from department b
where a.emp_id=b.dpt_manager or a.emp_salary=b.dpt_avg_salary
)
Here the following is the query plan in the Tosska proprietary tree format, it takes 4 minutes and 29 seconds to finish.
The query plan shows a Nested Loops from EMPLOYEE to full table scan DEPARTMENT, it is the main problem of the entire query plan, the reason is the SQL Server cannot resolve this OR conditions ”a.emp_id=b.dpt_manager or a.emp_salary=b.dpt_avg_salary” by other join operations.
Let me rewrite the OR conditions in the subquery into a UNION ALL subquery in the following, the first part of the UNION ALL in the subquery represents the “a.emp_id=b.dpt_manager” condition, the second part represents the “a.emp_salary=b.dpt_avg_salary” condition but exclude the data that already satisfied with the first condition.
select count(*)
from employee a
where exists ( select ‘x’
from department b
where a.emp_id = b.dpt_manager
union all
select ‘x’
from department b
where ( not ( a.emp_id = b.dpt_manager )
or b.dpt_manager is null )
and a.emp_salary = b.dpt_avg_salary )
Here the following is the query plan of the rewritten SQL, it looks a little bit complex, but the performance is very good now, it takes only 0.447 seconds. There are two Hash Match joins that are used to replace the original Nested Loops from EMPLOYEE to full table scan DEPARTMENT.
Although the steps to the final rewrite is a little bit complicated, this kind of rewrites can be achieved by Tosska SQL Tuning Expert for SQL Server automatically, it shows that the rewrite is more than 600 times fastAlthough the steps to the final rewrite is a little bit complicated, this kind of rewrites can be achieved by Tosska SQL Tuning Expert for SQL Server automatically, it shows that the rewrite is more than 600 times faster than the original SQL.
Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited
Tosska SQL Tuning Expert (TSES™) for SQL Server 1.5.0 Release Notes
Tosska SQL Tuning Expert (TSES™) for SQL Server® – System Requirements
Before installing Tosska SQL Tuning Expert (TSES™) for SQL Server®, please make sure your system meets the following minimum hardware and software requirements:
| CPU | 1.8 GHz Processor |
| Memory | 2 GB of RAM minimum, 4 GB of RAM recommended |
| Hard Disk Space | 500 MB of disk space for 64-bit installation |
| Operating System | Microsoft Windows® 7 64-bit Microsoft Windows® 10 64-bit |
| .NET Framework | Microsoft .NET Framework 4.5.0 |
| Database Server | SQL Server database 2005 or higher |
How to Tune Bad Performance SET ROWCOUNT SQL Statements for SQL Server?
Some SQL statements will be running very slow after SET ROWCOUNT or TOP is used. SET ROWCOUNT and TOP are used to tell SQL Server to select a specific number of rows from the SQL statements instead of extracting all records. Not many people know that SQL Server will try to re-optimize your SQL statements after you adding SET ROWCOUNT or TOP, the result is normally good after re-optimization of your SQL statements that can generate query plans for retrieving the first few records as fast as possible.
Good Example for Query Re-optimization for SET ROWCOUNT
Here the following is an example that shows the SQL takes 6.78 seconds to retrieve 217500 rows from the database, the query plan shows a good plan with a Hash Match for two Table Scan of [DEPARTMENT] and [EMPLOYEE].
The following screen shows the new query plan is generated after the SET ROWCOUNT 1 is used, the query plan is changed from Hash Match to Nested Loops. Nested Loops operation normally provides faster first few records retrieval time but may not be good for overall records extraction in certain situations. It is good to see that SQL Server uses only 0.013 seconds to extract the first row for this SQL.
Bad Example for Query Re-optimization for SET ROWCOUNT
Let’s see a bad example that shows how SQL Server degrades a good query plan to a bad query plan after the SET ROWCOUNT 1 is used. Here the following is an example that shows the SQL takes 0.118 seconds to retrieve 1613 rows from the database, the query plan is a little bit complex but it is a good query plan to retrieve all 1613 rows.
The following screen shows the new query plan is generated after the SET ROWCOUNT 1 is used, the query plan is now changed to Nested Loops with two Table Scans. The new query plan takes 1.312 seconds to extract only the first record, it is even slower than the 0.118 seconds that is used to extract all 1613 rows from the database.
How to Solve This Problem?
We can use Hints injection or SQL syntax rewrite to influence SQL Server to get back the original plan or generate an even better query plan for the SET ROWCOUNT or TOP operation. The following Hints injection generated a good query plan that is almost 90 times better than the original SQL with SET ROWCOUNT 1.
Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited
How to Tune Cold Cache SQL Statements for SQL Server?
For SQL statements that are not executed frequently, so that the relevant data is no longer exists in the buffer cache, a cold cache will significantly affect the performance of a SQL statement. A good performance SQL for hot cache may not be performing well in a cold cache environment. Experience developers will tune their SQL running well for both environments.
Here the following is an example SQL:
select * from
EMPLOYEE A
where A.EMP_ID IN (SELECT B.EMP_ID from EMP_SUBSIDIARY B
where B.EMP_DEPT < ‘D’)
Here the following is the query plan in the Tosska proprietary tree format, it takes 8.024 seconds for the first execution with cache delay and it takes 3.7 seconds for the second execution without caching time.
According to the query plan, you may find that the most significant IO consumption is the Table Scan of [EMPLOYEE] table. To simulate the cold cache environment, we can use the DBCC DROPCLEANBUFFERS command to clear the data cache before each execution of rewritten or optimized SQL statement.
Let me add an optimizer hint OPTION(LOOP JOIN) to the SQL and try to change the query plan from a Hash Match to a Nested Loop join. So, the EMP_ID(EMPLOYEE_PK) and a RID Lookup to [EMPLOYEE] will be used instead of using Table Scan. I hope that the RID Lookup can select fewer data from hard disk with matched EMP_ID in both [EMPLOYEE] and [EMP_SUBSIDIARY].
select *
from EMPLOYEE A
where A.EMP_ID in (select B.EMP_ID
from EMP_SUBSIDIARY B
where B.EMP_DEPT < ‘D’) OPTION(LOOP JOIN)
Here the following is the query plan, the time is reduced from 8.024 seconds to 1.565 seconds with data cache overhead, and the physical reads are also dropped from 190,621 to 39,044. It shows a wrong IO estimation If you just rely on the SQL Server’s EstimateIO x EstimiateExecutions in the query plan.
There are other even better tuning solutions for this SQL with the A.I. SQL tuning tool in the following:
Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited
The following SQL with an optimizer hint generate a more complicated query plan with the best execution time of 0.7 seconds. The SQL is tuned by cold cache simulation that data will be flushed before each execution of SQL alternatives.
