SQL Performance Tuning

SQL performance tuning is a process of optimizing SQL queries to ensure they execute as fast as possible. There are many factors that affect the performance of SQL queries, such as the number of tables queried, the size and number of columns in tables, and indexes on tables.

SQL is a critical component of many database systems and is a well-known language for querying, updating and managing data.
SQL performance tuning is an important topic to master as it can help with scalability and speed of queries.
In this section, we will cover different ways SQL tuning.

All of us always wants a fast response on the data retrieval process. So we need to design a good database that provides best performance during data manipulation which results into the best performance of an application.

However, there is no straightforward way to define the best performance but we can choose multiple ways to improving SQL query performance, which falls under various categories like creation of Indexes, usage of joins, and rewrite a subquery to use JOIN, etc.

As a developer, we know any SQL query can be written in multiple ways but we should follow best practices/ techniques to achieve better query performance. Some of them are highlighted below :

1. Use EXISTS instead of IN to check existence of data.
   2. Avoid * in SELECT statement. Give the name of columns which you require.
   3. Choose appropriate Data Type. E.g. To store strings use varchar in place of text data type. Use text data type, whenever you need to store large data (more than 8000 characters).
   4. Avoid nchar and nvarchar if possible since both the data types takes just double memory as char and varchar.
   5. Avoid NULL in fixed-length field. In case of requirement of NULL, use variable-length (varchar) field that takes less space for NULL.
   6. Avoid Having Clause. Having clause is required if you further wish to filter the result of an aggregations.
   7. Create Clustered and Non-Clustered Indexes.
   8. Keep clustered index small since the fields used in clustered index may also used in non-clustered index.
   9. Most selective columns should be placed leftmost in the key of a non-clustered index.
   10. Drop unused Indexes.
   11. Better to create indexes on columns that have integer values instead of characters. Integer values use less overhead than character values.
   12. Use joins instead of sub-queries.
   13. Use WHERE expressions to limit the size of result tables that are created with joins.
   14. Use TABLOCKX while inserting into a table and TABLOCK while merging.
   15. Use WITH (NOLOCK) while querying the data from any table.
   16. Use SET NOCOUNT ON and use TRY- CATCH to avoid deadlock condition.
   17. Avoid Cursors since cursor are very slow in performance.
   18. Use Table variable in place of Temp table. Use of Temp tables required interaction with TempDb database which is a time taking task.
   19. Use UNION ALL in place of UNION if possible.
   20. Use Schema name before SQL objects name.
   21. Use Stored Procedure for frequently used data and more complex queries.
   22. Keep transaction as small as possible since transaction lock the processing tables data and may results into deadlocks.
   23. Avoid prefix “sp_” with user defined stored procedure name because SQL server first search the user defined procedure in the master database and after that in the current session database.
   24. Avoid use of Non-correlated Scalar Sub Query. Use this query as a separate query instead of part of the main query and store the output in a variable, which can be referred to in the main query or later part of the batch.
   25. Avoid Multi-statement Table Valued Functions (TVFs). Multi-statement TVFs are more costly than inline TVFs.
   
   As we have seen above , In SQL Server , there are several steps you can take to improve the performance of your queries and database as a whole. There are many steps that will be applicable for other RDBMS as well like ORACLE.

Here are some general tips for performance tuning in Oracle SQL:

1. Use EXPLAIN PLAN or AUTOTRACE to analyze the execution plan of your queries and identify any issues with the way they are being executed.
2. Use the INDEX hint to specify which index should be used by the optimizer for a particular query.
3. Consider using hints such as FULL or INDEX_FFS to force the optimizer to use a full table scan or an index fast full scan, respectively.
4. Use the OPTIMIZER_MODE hint to specify a particular optimization mode for a query.
5. Consider using materialized views to pre-calculate and store frequently-accessed data, which can improve the performance of queries that access that data.
6. Use the DBMS_STATS package to analyze and gather statistics about your database and its objects, which can help the optimizer make more informed decisions when creating execution plans.
7. Consider using partitioning to break large tables into smaller, more manageable pieces, which can improve the performance of queries that access those tables.
8. Use the SQL Tuning Advisor to identify and fix any potential performance issues with your SQL statements.
9. These are just a few of the many techniques you can use to improve the performance of your Oracle SQL queries and database. It’s important to keep in mind that the specific steps you’ll need to take will depend on your specific database and workload.
10. It’s always a good idea to consult with a database administrator or other experts if you’re having performance issues.

Other Interesting Articles :

Many ways to learn GCP for Free with Google Cloud over the holidays

Understanding 𝗖𝗢𝗥𝗦-𝗖𝗿𝗼𝘀𝘀-𝗢𝗿𝗶𝗴𝗶𝗻 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲 𝗦𝗵𝗮𝗿𝗶𝗻𝗴

Linux Commands for Cloud Learning

Happy Reading…..Happy Learning!