Posted on

How to Tune SQL Statement with LCASE function on index field?

Some business requirements may need to compare the lower case of an indexed column to a given string as a data retrieval criterion.

Here is an example SQL that retrieves records from the EMPLOYEE table employee if the lower case of the name is equal to the string ‘richard’.

select  *
  from employee
 where LCASE(emp_name)=‘richard’

Here the following are the query plans of this SQL, it takes 17 seconds to finish. The query shows a “Full Table Scan Employee”  

You can see that this SQL cannot utilize index scan even if the emp_name is an indexed field. Let me add a “Force Index(emp_name_inx)“hint to the SQL and hope it can help MySQL SQL optimizer to use index scan, but it fails to enable the index scan anyway, so I add one more dummy condition “emp_name >= ””, it is an always true condition that emp_name should be greater or equal to a smallest empty character, it is used to increase the cost of not using emp_name_inx index. There is another condition added “emp_name is null” to correct this condition if emp_name is a null value.

select  *
from   employee force index(EMPS_NAME_INX)
where  LCASE(emp_name) = ‘richard’
     and ( emp_name >= ”
        or emp_name is null )

Here is the query plan of the rewritten SQL and it is running much faster. The new query plan shows that an Index Scan is used now and takes 2.79 seconds only.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 6 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

Posted on

How to use ROWID to improve an UPDATE statement for Oracle?

Here the following is an Update SQL with a subquery that updates the EMPLOYEE table if the emp_dept satisfies the records returned from a subquery.

update  employee
   set  emp_name = ‘testing’
 where  emp_dept IN (select dpt_id
            from department
          where dpt_name like ‘A%’)
and emp_grade>2000

You can see Oracle uses a Hash join of the DEPARTMENT table and EMPLOYEE table to execute the update process. This query plan takes 1.96 seconds to complete and no index is used even though emp_dept, dpt_id, and emp_grade are indexed columns. It looks like the most expansive operation is the Table Access Full scan of the EMPLOYEE table.

Let’s rewrite the SQL into the following syntax to eliminate EMPLOYEE’s Table Access Full operation from the query plan.  The new subquery with the italic Bold text is used to force the EMPLOYEE to extract records with emp_dept in the DEPARTMENT table with the dpt_name like ‘A%’. The ROWID returned from the EMPLOYEE(subquery) is to make sure a more efficient table ROWID access to the outer EMPLOYEE table.

UPDATE  employee
SET   emp_name=‘testing’
WHERE   ROWID IN (SELECT  ROWID
          FROM   employee
          WHERE  emp_dept IN (SELECT  dpt_id
                      FROM   department
                      WHERE  dpt_name LIKE‘A%’))
     AND emp_grade > 2000

You can see the final query plan with this syntax has a better cost without full table access to the EMPLOYEE table. The new syntax takes 0.9 seconds and it is more than 2 times faster than the original syntax.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert Pro for Oracle automatically, there is another SQL rewrite with similar performance, but it is not suitable to discuss in this short article, maybe I can discuss it later in my blog.

https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/

Posted on

How to build indexes for multiple Max() functions for SQL Server?

For some SQL statements with multiple Max() functions in the select list and nothing in the Where clause, we have different methods to create new indexes to improve the SQL speed.

Here is an example SQL, it is to retrieve the maximum name and age from the employee table.
select   max(emp_name),
     max(emp_age)
from  employee

The following is the query plan that takes 9.27 seconds.

The SQL cannot be tuned by SQL syntax rewrite or hints injection, and the SSMS cannot recommend any index to improve the SQL.

For this kind of SQL that we can consider building a composite index or two individual indexes for emp_name and emp_age. A new composite of these two columns (emp_age, emp_name) can improve the SQL around 7 times. The following is the query plan shows that the new composite index is used, but it has to scan the entire index for these two stream aggregate operations before getting the max(emp_name) and max(emp_age).

How about if we build two individual indexes for emp_name and emp_age. The following is the result and query plan of these two indexes created. A Top operator selects the first row from each index and returns to the Stream Aggregate operation, and then a Nested Loops join the two maximum results together. It is 356 times much faster than the original SQL.

This kind of indexes recommendation can be achieved by Tosska SQL Tuning Expert Pro for SQL Server automatically:
Tosska SQL Tuning Expert Pro (TSES Pro™) for SQL Server – Tosska Technologies Limited

Posted on

Measures to Improve Performance of SQL Query?

oracle database performance tuning

That’s not bad enough that you call it SQL whereas your boss pronounces it ‘sequel’. But also, you now suffer from “Super Slow Query Syndrome,” and sometimes, your questions bomb without effect.

Don’t worry. We have your back. We recently had a powwow with a lot of caffeine to think about our favorite tips to fix queries. With the help of this article, we will dig into how we can resolve SQL queries and improve the performance of SQL queries with new tips and tricks, such as action plans, references, wild cards, and much more.

In fact, we have combined all our famous skills into one, so you can increase your SQL intelligence by six minutes flat.

The issues faced by the companies in SQL Server performance often lead to focusing on using tuning tools and development strategies. This will help to analyze and process queries faster and eliminate operational issues, troubleshoot poor performance, avoid any chaos, or reduce the impact on the SQL Server database.

What is SQL Query Optimization?

Optimizing SQL Query is the process of writing considerate SQL queries to improve database performance. During development, the amount of data accessed and tested is not much. Thus, it becomes easy for developers to get a prompt response to their raised questions. But the problem starts when the project becomes live and large data starts to flood the database. These kinds of situations reduce the resolving process and performance.

A request for data or information from a database is called a Query, and you need to write a pre-defined set of code that is understandable to the database. Structured Query Language (SQL) and other query languages recover or manage data from related databases.

There are different formats to write a query in the database, using various algorithms. A query that is incomplete or written poorly can lead to a lot of resource consumption, and also can take a lot of time in execution, which possibly causes a loss in services. A proper query can reduce implementation time and lead to better SQL results.

SQL query optimization’s main purpose is to reduce response time and improve query performance, Reduce CPU performance time for faster results and reduce the number of resources used to improve the output.

Ways to Improve SQL Query Performance

Avoiding unnecessary columns in the SELECT section

To improve MySQL functionality, it’s recommended to specify columns in the SELECT section, instead of using SELECT*. As irrelevant columns create more load in the database, it slows down the performance of the whole system.

Using internal joining, rather than external joining if possible

Use external joining only if necessary. Excessive use of it not only limits database performance but also limits MySQL query options, resulting in slower SQL statements.

Using DISTINCT and UNION only if necessary

By using UNION and DISTINCT operators while there are no major objective results in unwanted filtering and reduced SQL performance. To improve the performance and bring efficiency to the process we can always use UNION ALL, rather than UNION.

Using the ORDER BY clause

To get more clear results it is important to use the ORDER BY clause. It not only brings 

advantages for database admins but also increases performance in its execution.

SQL Query Performance Tuning: Best Practice

SQL Query tuning is one of the fastest ways to improve the performance of SQL Server. Set procedures and processes are used to improve the performance and resolve the database-related queries this is called Tuning the SQL server. SQL tuning includes several features, including identifying which queries are slower and utilizing them to work more efficiently. Multiple communication databases like MySQL and SQL Server will benefit from SQL tuning.

The Database Performance Analyzer can attempt to troubleshoot server performance issues in the system. But these measures are expensive, and they may not work to solve the problem of slow-moving queries. Tuning SQL functionality helps you to identify poorly written SQL queries and invalid indexing conditions. After doing so, you may find that you do not need to invest in hardware upgrades or technical details.

Tuning SQL functionality can be difficult, especially if done manually. Believe it or not, the slight changes can have major effects on SQL Server and database performance. Hence, there is a need for practical SQL Query performance tools.

To conclude, generally, the best practices of SQL Query performance Tuning include proper indexing that can be done by the Execution Plan tool in SQL Server. Additionally, avoiding coding loops and correlating SQL subqueries.

Tosska SQL Tuning Expert (TSE™) v4 is one of the best SQL tuning tool available in the market. It helps in tuning the SQL even without any source code.

Posted on

SQL Query Performance Tuning: A Look at Various Plan Formats

SQL query performance tuning

Database professionals are often familiar with the fundamental maintenance tasks for SQL Server. However, they may have to perform the optimization of SQL queries and keep an eye on query plans from time to time.

This may lead to confusion as to which type of query plan they should use. Here, we will cover the different types and formats of query plans and how to obtain them for SQL query performance tuning.

Query Plan Types and Formats for Efficient SQL Query Performance Tuning

To start with, there are two major kinds of query plans: the estimated and the actual execution plan. Users can obtain these in three distinct formats – text, graphical, and XML.

Keep in mind that users who wish to create these execution plans will require SHOWPLAN permission first. This is also true in the case of query plans and SQL tuning for Oracle.

Text-Based Query Plans

This type of plan can be procured using one of the three methods given below:

  • Set SHOWPLAN_Text – The query will not be run by SQL Server, but this method should fetch information about the manner in which queries run. In short, this statement will display information regarding the Estimated Execution plan.
  • Set SHOWPLAN_All – Again, SQL Server won’t run the query but it will fetch thorough details regarding query execution, i.e the way it executes them and which resources it uses for this purpose. You can also get more details about the Estimated Execution plan.
  • Set Statistics Profile – SQL Server will run the statement and display comprehensive data regarding query execution. This information includes the precise number of rows that were actually processed and all the resources that have been utilised for executing these queries. This command will also fetch details regarding the Actual Execution plan.

Graphical Query Plans

This format allows users a look at numerous sources of information and plenty of tooltips in SQL Server Management Studio.

Note that if you want to view the Estimated Execution Plan, you can do so once you press Ctrl+L in the query window. To view the Actual Execution Plan in the same results set, press Ctrl+M.

XML Based Query Plans

This type of query plan gives the most comprehensive details of the plan in the extremely portable XML format. You can obtain query plans in this format using two methods:

  • Set SHOWPLAN_XML – The query doesn’t run but returns detailed information about how the statements execute and the resources used for the query execution. SQL Server also displays a detailed XML document that has the Estimated Execution plan.
  • Set Statistics XML – SQL Server runs the statement and shows information regarding query execution in exhaustive detail. This includes information on the actual number of rows processed as well as the resources applied in the query execution. It fetches a properly created XML document consisting of the Actual Execution plan, helping in SQL query performance tuning.

How these Query Plan Formats Differ in Use

The graphical format is usually the simplest to read which is why beginners usually start with them. The best way to read graphical query plans is from right to left moving upwards from the bottom while following the arrows. Additionally, you can make things easier with the Zoom In and Zoom Out functions.

You can view further details from Graphical plans with the help of ToolTips. All you have to do is point your cursor at the icon you want to know more about. While this is an extremely useful feature, it can get complicated to view every detail on a complex query with the help of this tool.

This format also forms the link between the Text and the XML formats. This is because you can save Graphical Plans in XML format. If you’re trying to perform SQL tuning for Oracle, you may consider a SQL tuning tool depending on your requirements.

Text plans, on the other hand, are more difficult to read and lack simpler rules to understand them. However, they prove useful for those who have experience with execution plans and know what and how to look for.