query tuning in sql - Tosska Technologies Limited

2023-07-25

The Overlooked Significance of Performance Deployment in Software Deployment

Performance deployment is a critical aspect of software deployment that is often undervalued. While it’s important to ensure that software is deployed correctly, it’s equally important to optimize its performance in the production environment. By recognizing the significance of performance deployment during the software deployment process, developers can ensure that their software performs well and meets the needs of its users. Focusing on performance deployment can help prevent performance issues and improve user satisfaction with the software.

The Missing Link of Performance Deployment Between Testing Database and Production Database
Despite extensive pre-deployment testing, there is still a chance of encountering performance problems in specific development environments during software performance deployment. The following issues may arise :

Inability to copy production data to the testing database.
Significant differences in hardware and software configuration between the testing and production databases.
Inability to test software in the production database due to security restrictions.
The utilization of DML SQL statements in the new software that may damage the data integrity of the production database.

It is not unusual for users to face performance issues or encounter application errors following a release of new application code.

Ensuring Performance Deployment with a Pre-Deployment Process
The subsequent instructions present a novel approach for guaranteeing performance reliability while deploying software. The idea is uncomplicated: since it is not feasible to run the new application code on the production database, why not obtain query plans for every SQL statement in the production database? This way, we can assess the performance of each SQL statement in the application code that is intended for deployment on the production database.

Suppose there are 10 SQL statements in the new application code that we need to identify in the testing database. In that case, we need to clear the shared pool and execute the new application in the testing database first to isolate these 10 statements. This process will enable us to capture and analyze the 10 SQL statements and obtain their query plan from the production database. The table below presents various potential outcomes resulting from the query plan comparison.

Observation	Possible reasons
Explain Plan error in the production database	The SQL statements requiring access to objects not present in the production database.
Query plan changes	Significant statistical differences between the testing and production databases, including differences in the database schema. These schema differences may involve missing or new partitions and other changes affecting the database’s structure and organization. *Benchmarking the SQL may be necessary due to the potential significant changes in performance.*
Unused indexes	Some indexes used in the testing database are not used in the production database. *Benchmarking the SQL may be necessary due to the potential significant changes in performance.*
New used indexes	Some indexes used in the production database are not used in the testing database. *Benchmarking the SQL may be necessary due to the potential significant changes in performance.*
Total cost changes	Changes in the overall query plan cost for the 10 SQL statements. If the production database has a larger data volume than the testing database, the cost change will be higher.

DBAO SQL Performance Tracker – YouTube
Tosska DB Ace Enterprise for Oracle – Tosska Technologies Limited

2022-09-06

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/

2022-07-052022-07-05

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/

2022-07-052022-07-05

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

2022-04-19

How to build indexes for slow first execution SQL – SQL Server?

You may suffer from SQL statements with a slow first execution time due to the long data cache process. The following SQL is simple that retrieves records from the EMPLOYEE table that if EMP_SALARY < 500000 and the result set is ordered by EMP_NAME.

Select emp_id,
emp_name,
emp_salary,
emp_address,
emp_telephone
from employee
where emp_salary < 500000
order by emp_name;

The following is the query plan that takes 9.51 seconds for the first execution and takes 0.99 seconds for the second execution without data cache.

The SQL cannot be tuned by SQL syntax rewrite or hints injection for both the first execution and the second execution, it is because SQL Server has selected the best query plan for this simple SQL statement. But the problem is that if the condition “where emp_salary < 500000” is changed; say from 500000 to 510000 or the EMPLOYEE data is flushed out from the memory, the execution time will then be prolonged up to 9.51 seconds.

Let’s see if we can build indexes to improve this situation. There is a common perception that a good index can help to improve both the first execution time and the second execution time. So, I use a tool to explore a lot of indexes configurations, but none of them can improve both executions’ performance. Here the following is the performance of the second execution with data cached for different indexes proposed by the tool. You can see the performance of “Index Set 1” is close to the original SQL performance with a little performance variation due to the system’s loading status and all other indexes sets are worse than the original SQL. Normally, we will give up the tuning of the SQL statement without even trying to see whether those recommended indexes are good for the first execution time.

I did a test for those recommended indexes to see whether they are helpful to improve the first execution time, it surprises me that the “Index Set 1” is tested with a significant improvement and improves the first execution time from 9.51 seconds to 0.65 seconds. It is a 14 times improvement that can make my database run more efficiently. So, you should be very careful to tune your SQL with new indexes that may not be good for your second execution with all data cached, but it may be very good for your first execution without data cached.

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

2022-04-19

How to index SQL with aggregate function SQL for Oracle?

Here the following is an example SQL shows you that select the maximum emp_address which is not indexed in the EMPLOYEE table with 3 million records, the emp_grade is an indexed column.

select max(emp_address) from employee a
where emp_grade<4000

As 80% of the EMPLOYEE table’s records will be retrieved to examine the maximum emp_address string. The query plan of this SQL shows a Table Access Full on EMPLOYEE table is reasonable.

How many ways to build an index to improve this SQL?
Although it is simple SQL, there are still 3 ways to build an index to improve this SQL, the following are the possible indexes that can be built for the SQL, the first one is a single column index and the 2 and 3 are the composite index with a different order.
1. EMP_ADDRESS
2. EMP_GRADE, EMP_ADDRESS
3. EMP_ADDRESS, EMP_GRADE

Most people may use the EMP_ADDRESS as the first choice to improve this SQL, let’s see what the query plan is if we build a virtual index for the EMP_ADDRESS column in the following, you can see the estimated cost is reduced by almost half, but this query plan is finally not being used after the physical index is built for benchmarking due to actual statistics is collected.

The following query shows the EMP_ADDRESS index is not used and the query plan is the same as the original SQL without any new index built.

Let’s try the second composite index (EMP_GRADE, EMP_ADDRESS), the new query plan shows an Index Fast Full Scan of this index, it is a reasonable plan which no table’s data is needed to retrieve. So, the execution time is reduced from 16.83 seconds to 3.89 seconds.

Let’s test the last composite index (EMP_ADDRESS, EMP_GRADE) that EMP_ADDRESS is placed as the first column in the composite index, it creates a new query plan that shows an extra FIRST ROW operation for the INDEX FULL SCAN (MIN/MAX), it highly reduces the execution time from 16.83 seconds to 0.08 seconds.

So, indexing sometimes is an art that needs you to pay more attention to it, some potential solutions may perform excess your expectation.

The best index solution is now more than 200 times better than the original SQL without index, this kind of index recommendation can be achieved by Tosska SQL Tuning Expert for Oracle automatically.

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

2022-02-102022-03-16

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.

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

2022-01-172022-03-16

How is the order of the columns in a composite index affecting a subquery performance for Oracle?

We know the order of the columns in a composite index will determine the usage of the index or not against a table. A query will use a composite index only if the where clause of the query has at least the leading/left-most columns of the index in it. But, it is far more complicated in correlated subquery situations. Let’s have an example SQL to elaborate the details in the following.

SELECT D.*
FROM department D
WHERE EXISTS (SELECT    Count(*)
FROM    employee E
WHERE     E.emp_id < 1050000
AND E.emp_dept = D.dpt_id
GROUP BY E.emp_dept
HAVING    Count(*) > 124)

Here the following is the query plan of the SQL, it takes 10 seconds to finish. We can see that the SQL can utilize E.emp_id and E.emp_dept indexes individually.

Let’s see if a new composite index can help to improve the SQL’s performance or not, as a rule of thumb, a higher selectivity column E.emp_id will be set as the first column in a composite index (E.emp_id, E.emp_dept).

The following is the query plan of a new composite index (E.emp_id, E.emp_dept) and the result performance is not good, it takes 11.8 seconds and it is even worse than the original query plan.

If we change the order of the columns in the composite index to (E.emp_dept, E.emp_id), the following query plan is generated and the speed is improved to 0.31 seconds.

The above two query plans are similar, the only difference is the “2” operation. The first composite index with first column E.emp_id uses an INDEX RANGE SCAN of the new composite index, but the second query plan uses an INDEX SKIP SCAN for the first column of E.emp_dept composite index. You can see there is an extra filter operation for E.emp_dept in the Predicate Information of INDEX RANGE SCAN of the index (E.emp_id, E.emp_dept). But the (E.emp_dept, E.emp_id) composite index use INDEX SKIP SCAN without extra operation to filter the E.emp_dept again.

So, you have to test the order of composite index very carefully for correlated subqueries, sometimes it will give you improvements that exceed your expectation.

This kind of index recommendation can be achieved by Tosska SQL Tuning Expert for Oracle automatically.

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

2021-11-262023-02-18

Creating an Index in Oracle, and the Best Way to Make Use of It

Indexes are among the most useful and underutilized components of SQL. The user can create an Oracle index and store values along with their location in it.

Similar to the index at the end of a book, an index enables the user to go straight to the data they are interested in. Indexes are most useful when a user has to find a few rows. Therefore, they can use an index in statements that return a handful of rows – after creating one, of course!

Simple Techniques to Create an Index in Oracle Database

Creating an index is a simple task in MySQL query optimization as you only need to know two things:

The columns that require indexing
The name you will give the index

Here’s how to create one:

create index <indexname> on <tablename> ( <col1>, <col2>, <col3>, … <coln> );

Eg. create index cars_colour_metallic on cars (colour);

However, there are a few things to know about indexes before you begin:

You can place several columns in a single index, which then becomes a composite or compound index.

For instance, in the above example, you could also add the types of cars in the index like this: create index cars_colour_metallic on cars (colour, type);

The order in which you set columns in the index affects its use by the optimizer.

Next, let’s take a look at two of the most important index types users create in Oracle.

Two Major Index Types – and When to Pick Each

There are several kinds of indexes in the Oracle database that can improve your SQL. However, one of the most significant decisions you’ll have to make is likely to involve choosing between B-trees and bitmaps.

Create Index Oracle: B-tree Versus Bitmap Indexes

B-trees:– Indexes are in balanced B-tree format by default, which means all the leaf nodes are located at the same depth. It takes equal effort (O(log n)) to access any value, and one leaf index entry contains one row of data.

Bitmap:- Bitmaps also store indexed values, but in a completely different manner as compared to B-trees. In it, one value entry is associated with a range of row values. A bitmap has a series of 1s (yes) and 0s (no) to indicate whether any of the range rows contains the value or not.

One major difference between these two index types is that a B-tree doesn’t include null indexed values; a bitmap does. A bitmap can, therefore, answer some statements during MySQL query optimization, such as targeted index searches in which the column has a null value.

Although this won’t work for a B-tree, the user can add a constant at the end of an index to turn it into a composite index.

Bitmaps are also helpful because compressing the bits is simpler, which is why a bitmap index is generally smaller as compared to a B-tree index with identical data.

Why You Need to Keep a Check on the Indexes You Create

With all the benefits an index provides, it is important to create as few of them as possible. This is because you may end up creating one for every specific requirement and forget about them over time. The same goes for other users who may come and go on your team. And no one will have a clue why Brad needed to create that six-column function-based nightmare.

Since you don’t know if the index in question is only used for year-end reporting or never used, you cannot drop an index whenever you want. This can result in awkward situations where a table contains more indexes than columns!

So, if you’re unsure between two excellent indexes and one “good enough” index, it is better to choose the latter. And don’t forget to test!

2021-11-262022-03-16

How to use ORDERED Hint to Tune a SQL with subquery for Oracle?

Here the following is the description of the ORDERED hint.

The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause.

If you omit the ORDERED hint from a SQL statement performing a join, then the optimizer chooses the order in which to join the tables. You might want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information lets you choose an inner and outer table better than the optimizer could.

We usually use an ORDERED hint to control the john order, but how this hint causes a SQL with a subquery. Let’s use the following SQL as an example to see how ORDERED hint works for a subquery.

SELECT *
FROM DEPARTMENT
where dpt_id
in (select emp_dept from employee
where emp_id >3300000)

Here the following is the query plan of the SQL, it takes 68.84 seconds to finish. The query shows a “TABLE ACCESS FULL” of the DEPARTMENT table and “NESTED LOOPS SEMI” to an “INDEX RANGE SCAN” of EMPLOYEE.

If you think it is not an effective plan, you may want to try to reorder the join path and see if an ORDERED hint is working or not in a subquery case like this:

SELECT /*+ ORDERED */ *
FROM department
WHERE dpt_id IN (SELECT emp_dept
FROM employee
WHERE emp_id > 3300000)

Here is the query plan of the hinted SQL and the speed is 3.44 seconds which is 20 times better than the original SQL. The new query plan shows the new join order that EMPLOYEE is retrieve first and then hash join DEPARTMENT later. You can see the ORDERED hint will order the subquery’s table first. This new order clauses a new data retrieval method from the EMPLOYEE table, it makes the overall performance much better than the original query plan.

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

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