Performance Tuning

SQL Performance Tuning

Add missing indexes

General rules of thumb:

Put index on columns that are being used in where clauses
Put index on columns that you use to join on.
Try not to use more than 4-5 indexes on columns in the same table.
Don’t use indexes on columns with only few different values. The table would consume more space with the index and preform slower on insertion, but you won’t get significant better performance while querying

General concepts:

Any index that you use will make searches on those columns faster.
Any index that you add causing inserting to this table to be a bit more slower.
Any index that impact database performance by increasing disk space usage.
From the previous three. It is your responsibility to decide upon how many insertions and queries you do on tables to decide if to use index and on which columns or not.

Check for unused indexes

You may encounter a situation where indexes exist but are not being used. One of the reasons for that might be implicit data type conversion.

1
2


SELECT * FROM TestTable WHERE IntColumn = '1';
-- The IntColumn field has been indexed, but not used

Using the CAST() function that converts a value of any type into a specified datatype.

1

SELECT * FROM TestTable WHERE IntColumn = CAST(@char AS INT);

Filtered indexes can affect performance too. Suppose, we have an index on the Customer table.

1
2


CREATE UNIQUE NONCLUSTERED INDEX IX ON Customer (MembershipCode)
WHERE MembershipCode IS NOT NULL;

The index won’t work for the following query:

1
2
3


SELECT *
FROM Customer
WHERE MembershipCode = '258410';

To get use of the index, you’ll need to optimize the query in the following way:

1
2
3
4


SELECT *
FROM Customer
WHERE MembershipCode = '258410'
AND MembershipCode IS NOT NULL;

Avoid Using Functions In Predicates

The database doesn’t use an index if it has some function predefined in the column.

1

SELECT * FROM TABLE1 WHERE UPPER(COL1)='ABC'Copy

1

SELECT * FROM TestTable WHERE DATEPART(YEAR, SomeMyDate) = '2021';

In this case, implicit data type conversion will take place too, and the indexes won’t be used. To avoid this:

1
2
3


SELECT * 
FROM TestTable 
WHERE SomeDate >= '20210101'AND SomeDate < '20220101'

If there isn’t any way to avoid that function in SQL, you must create a new function-based index or generate custom columns in the database to improve performance.

Avoid Using a Wildcard (%) At the Beginning of a Predicate

The predicate LIKE ‘%abc’ causes a full table scan. For example:

1

SELECT * FROM TABLE1 WHERE COL1 LIKE '%ABC'Copy

In most cases, this wildcard usage brings major performance limitations.

Avoid using multiple OR in the FILTER predicate

Use wildcards at the end of a phrase only

Avoid too many JOINs

Use DISTINCT and UNION Only If Necessary

Using UNION and DISTINCT operators without any major purpose causes unwanted sorting and slowing down of SQL execution. Instead of UNION, using UNION ALL brings more efficiency to the process and improves MySQL performance more precisely.

**Use SELECT fields instead of SELECT ***

Instead of using ‘SELECT *’, always specify columns in the SELECT clause to improve MySQL performance. Because unnecessary columns cause additional load on the database, slowing down its performance as well whole systematic process.

Use TOP to sample query results

Run the query during off-peak hours

Minimize the usage of any query hint

Minimize large write operations

Create joins with INNER JOIN (not WHERE)

Use Inner Join Instead of Outer Join

Use outer join only when necessary. Using it needlessly not only limits database performance but also limits MySQL query optimization options, resulting in slower execution of SQL statements.

Where to Use “ORDER BY”

The query first needs to sort the data for producing the final result-set, causing a bit of complex operation in the SQL execution.

Optimizing MySQL Subqueries

The most important advice I can give you about subqueries is that you must prefer a join where possible, at least in current versions of MySQL.

My point here is that “prefer a join” is not future-proof advice.

Reference

Table of Contents

SQL Performance Tuning

Reference

Website Performance Optimization

Backend Performance Tuning

SQL Performance Tuning