How to Tune SQL with OR statements?

Tosska Chat Box

It is common that the performance is not good if a SQL statement with OR conditions. Let’s have an example show you how to tune those SQL statements in certain situations. Here is an example SQL that extract records from EMPLOYEE table if (emp_grade < 1050 or emp_id<730000). Emp_grade and emp_id are indexed and they are not null field.

select * from employee
where emp_grade < 1050 or emp_id<730000

You can see MySQL SQL Optimizer use an Index Merge of emps_grade_inx and employee_pk to process the SQL, the performance is not good as expected since the result set is quite big for sort_union operation. It takes more than 40 seconds to finish the data retrieval. Let me rewrite the OR condition into the following UNION ALL statement, please make sure the emp_grade and emp_id are not null column, otherwise it may generate error result. The rewrite is simple that the first part extract data with emp_grade<1050, the second part of the UNION ALL retrieve records that satisfied with emp_id<730000, but it is not retrieved in the first part of the UNION ALL.

select    *
from       employee
where    emp_grade < 1050
union all
select    *
from       employee
where    not ( emp_grade < 1050 )
                  and emp_id < 730000

Here the following is the query plan of this SQL, it takes 12.46 seconds to finish. The query shows two “Index Range Scan” of EMPLOYEE_PK and EMPS_GRADE_INX to the employee table.

This kind of rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 3 times faster than the original SQL. There are some other rewrites shown in this screen with comparable results too.

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

How to Tune SQL with COUNT(*) statements ?

sql performance monitoring

It is common that we used to count the number of records in a table.  You may encounter unexpected performance degradation in certain situations. Here is an example SQL that count number of records from EMPLOYEE table. There are number of indexes are built such as emp_id, emp_dept, emp_grade, emp_hire_date and etc….

SELECT COUNT(*)
   FROM EMPLOYEE;

You can see MySQL SQL Optimizer use a Full Index Scan of EMP_HIRE_DATE index, the performance is bad since unnecessary random reads is needed and it takes 3 minutes and 6 seconds to count a 3 million records in my computer.  I want to make use of Index Range Scan for specific index, let me rewrite the above SQL into the following syntax. If you know EMP_GRADE is indexed and it is not a nullable column, you can add a dummy condition EMP_GRADE>=’’. It fools MySQL SQL optimizer to consider using EMP_GRADE range index to retrieve the records and it is successfully generate a new plan in the following:

select    COUNT(*)
from       EMPLOYEE
where    EMP_GRADE >=  ‘ ‘

Here the following is the query plan of this SQL, it takes 2.6 seconds to finish. The query shows an “Index Range Scan” of employee table.

This kind of rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is almost 71 times faster than the original SQL. There are some other rewrites shown in this screen with comparable results too.

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

How to Tune substr(emp_name,5,4) SQL Statement?

sql performance monitoring

There may be some business requirements that need to compare certain part of a column as a data retrieval criteria. Here is an example SQL that retrieves data from EMPLOYEE table employee’s name with a string pattern “Acco” start from 5 character of the emp_name.

select    *
  from    employee
where    substr(emp_name,5,4)=‘Acco’

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 the emp_name is indexed field. Let me add a “Force Index(emp_name_inx)“ hints 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.

select    *
from       employee force index(emp_name_inx)
where    substr(emp_name,5,4) = ‘Acco’
                 and emp_name >= ‘ ‘

Here is the query plan of the rewritten SQL and it is running faster. The new query plan shows that an Index Range Scan is used now.

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

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

How to Tune “Order by Limit N” SQL Statement?

sql performance monitoring

There may be some business requirements that need to retrieve the first N number of rows from a join tables. Some people may encounter unexpected performance problem.

Here is an example SQL that retrieves first 1000 row from a join tables of Employee and Department in the order of dpt_id.  Where emp_dept and dpt_id columns are both indexed.

select *
     from employee ,department
where emp_dept=dpt_id
order by dpt_id
limit 1000

Here the following are the query plans of this SQL, it takes 1 mins and 8 seconds to finish. The query shows a nested loop from “Full Table Scan Employee” to “Unique Key Lookup Department” table to extract all records. An “Order by” operation of dpt_id of Department table is executed followed from this join result.

You can see the most expensive step is to extract all Employee data with Department data and then an Order by dpt_id  of Department table. Let’s see if we rewrite the original SQL text into the syntax that “order by dpt_id” is changed to “order by emp_dept”. It is a semantically eqvialent rewrite of original SQL statement since “emp_dept = dpt_id”.

select       *
from          employee,
                     department
where       emp_dept = dpt_id
order by    emp_dept
limit            1000

Here is the query plan of the rewritten SQL with less cost and run much faster. The new query plan shows that no “Use temporary; Using filesort” in Tabular Explain, it means the Nested Loop operation will be stop at the Limit 1000 records is done.

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

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

How to Tune SQL with LIKE ‘%Mary%’ comparison?

sql performance monitoring

The LIKE is a logical operator that determines if a character string matches a specified pattern. A pattern may include regular characters and wildcard characters. The LIKE operator is used in the WHERE clause of the SELECT, UPDATE, and DELETE statements to filter rows based on pattern matching.

Here is an example SQL that retrieves data from EMPLOYEE table employee’s name with a string pattern like “Mary%”. If the emp_name is indexed, the following SQL will utilize Index Range Scan of the emp_name and the speed of the SQL will be fine.

select *
from employee
where emp_name like ‘Mary%’;

If user is looking for emp_name with pattern like ‘%Mary%’, MySQL SQL Optimizer cannot user emp_name index to speed up the process, full table scan is normally be used and the performance will be bad too.

select *
from employee
where emp_name like ‘Mary%’;

Here the following are the query plan of this SQL, it takes 18.8 seconds to finish. The query shows a “Full Table Scan” of employee table.

You can see that this SQL cannot utilize index scan even the emp_name is indexed. Let me add a “Force Index(emp_name_inx)“ hints 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 always greater or equal to a smallest empty character.

select *
from   employee force index(emp_name_inx)
where  emp_name like ‘%Mary%’
       and emp_name >= ‘ ‘;

Here is the query plan of the rewritten SQL and it is running faster. The new query plan shows that an Index Range Scan is used now.

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

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

Don’t Overlook Oracle Database and SQL Performance: Here’s why

oracle database and sql

Being a DBA is not always a fun job, thanks to certain time-consuming tasks that it entails. One of these is to ensure optimal Oracle database and SQL performance. Typically, it is done by spending a lot of time tuning the long list of SQL statements and software code in order to improve efficiency and enhance access. However, SQL is just one aspect that is related to the performance of database systems.  

Database Administrators also need to invest their time in enhancing the design, physical structure, and specifications of the database objects. These objects are the tables, indices, and the information stored over several files. In the case of data inefficiency, it becomes necessary to observe and modify the actual construction and composition of database objects on a consistent basis. This is because any amount of SQL performance tuning is bound to fall short in a database that is improperly organized or poorly constructed.  

Optimizing Oracle Database and SQL: 5 Important Techniques 

The DBA has to be aware of all the specifications that the database management systems consist of as this knowledge will enable them to use the right techniques to optimize database constructs.

A majority of the most popular DBMSs are compatible with all the methods we have mentioned below, though they may be used differently depending on the database. Let’s take a look:

  1. Indexing: An essential aspect of the Oracle database and SQL performance tuning process is by selecting the right indices and alternatives in order to enable efficient queries. 
  2. Clustering: This involves implementing the physical pattern of data on the disk so that it is clustered on the same page whenever accessed in a particular order. 
  3. Compressing: Data is compressed by decreasing storage requirements, thereby allowing more of it to be stored in a smaller amount of space. This also reduces storage expenses and enhances access if you can add a larger number of rows per page.
  4. Freeing Up Space: Assigning extra room for data growth allows new data to be added to its table easily without leaving the table disorganized.
  5. Partitioning: This entails the segregation of one database table into various sections that are saved in several files. This can be done in multiple ways; by partitioning one file in the same computer, partitioning using shared-disk clustering or by shared-nothing partitioning, depending on the DBMS in question.
  6. File Organizing and Placement: Allocating data from both – database systems and data files – to the correct places is a big step in organizing data and improving Oracle and SQL database performance.
  7. Checking the Page Size: The size of the block or the page determines how efficiently data can be stored and accessed, which is why it is vital to use the suitable page size. The smaller the size of the page, the fewer rows per page, which increases sequential data access requirements.  
  8. Interleaving: Merging all the data from several tables in a sequence into a file helps enhance join performance. However, this method seems to have become less popular than it used to be.
  9. Reorganizing Database Objects: Eliminating the defects from the database by reorganizing and arranging database objects is a well-used technique in SQL performance tuning. In fact, it enormously increases performance, especially if the data was previously fragmented, disorganized or scattered in some way.
  10. Denormalization: This method is considered as a last resort attempt in case the database is unable to perform optimally with a completely normalized implementation. This is because it differs from the logical design. 

All of these techniques are useful and should be considered when the DBA creates a plan for tuning and monitoring the database. Each aspect may not necessarily be applicable to every database object but it must be analyzed for its applicability all the same. Moreover, techniques that are not applicable during initial implementation may turn out to be useful as the application undergoes changes over time in various aspects like data volume, usage, and database characteristics.