11 Tips to Boost SQL Query Optimization

When enterprises and companies are having trouble with SQL query optimization, they frequently turn to performance tuning tools and optimization strategies. This will not only help in analyzing and speeding up queries, but also in resolving performance issues, troubleshooting bad performance, and avoiding or minimizing the impact on SQL Server databases.

Data accessibility, availability, and performance are critical to corporate success. For database experts, performance tuning and SQL query optimization are difficult but critical tasks. They need the use of extended events, performance monitor, execution plans, statistics, and indexes to examine diverse sets of data.

To boost system performance, application owners may request that system resources (CPU and memory) be increased. However, you may not require these additional resources, which may come at a cost.

We'll go through a few SQL query optimization best practices to use when writing SQL queries in this article.

1. Select the Appropriate Data Type
2. Joins and Table Variables
3. Use Conditional WHERE Clause
4. Use IN and EXISTS Clauses
5. Use the Command SET NOCOUNT ON
6. Missing Indexes
7. Use-Wildcards
8. Use-TOP
9. Fully Qualify Database Object Names
10. Minimize Large Write Operations
11. Secure Your Code

Fundamentals of SQL Query Optimization

SQL query optimization is the process of determining the most efficient and effective methods and approaches for improving query performance using system resources and performance indicators. The goal of query tuning is to find a solution to reduce query response time, minimize excessive resource usage, and identify poor query performance.

Query processing, in the context of SQL query optimization, determines how to extract data from SQL servers more quickly by examining the query's execution phases, optimization strategies, and other information.

11 Tips for Improving Query Performance

Monitoring metrics can be used to assess query runtime, identify performance bottlenecks, and demonstrate how they can be addressed. They include, for example:

• Execution Plan
  During query execution, a SQL query optimization steps through the query, scanning indexes to retrieve data and providing a full overview of metrics.
• Input/Output Statistics
  Identifies the number of logical and physical reading operations performed during query execution, which helps users in detecting cache/memory capacity concerns.
• Buffer Cache
  Used to minimize the server's memory use.
• Latency
  It's used to figure out how long queries or actions take.
• Indexes
  On the SQL Server, it's used to speed up reading operations.
• Memory-Optimized Tables
  Table data is stored in memory to speed up reading and writing processes.

Now we'll go through some of the greatest SQL query optimization tips to use when creating queries in an SQL server.

#1 Select the Appropriate Data Type

In SQL, each table column has an associated data type. Integers, dates, varchars, boolean, text, and other data types are available. It is critical to select the appropriate data type when building. Numeric numbers should be used, dates should be dates, and so forth. This is critical for indexing purposes.

Take a look at the following example:

SELECT empName, empRole FROM emp WHERE empID = 98422;

The above query retrieves the employee ID and name for the 98422.

What if the empID data type is a string? When employing indexing, you may run into problems because it will take ages to scan what should be a simple document.

#2 Joins and Table Variables

You need something more than a basic select statement when you have sophisticated queries like retrieving customer orders together with their names and order dates. We're getting data from the customer and order tables in this scenario. Here's where joins come in handy.

Inner, full, left outer, and right outer joins are available in SQL.

Consider the following example of a join:

SELECT Orders.orderID, cust.custName, Orders.orderDate
FROM Orders
INNER JOIN Cust ON Orders.custID = cust.id;

Table variables are temporary data storage variables with all the attributes of local variables. Table variables should not be used in joins because SQL treats them as a single row. Table variables, despite their speed, do not perform well in joins.

#3 Use Conditional WHERE Clause

Subsetting is done with conditional WHERE clauses. Let's pretend you're in this situation:

-if SEQ_VAR in (1, 2, 3,4) & diff(DATE_VAR2, DATE_VAR1)≥ 0
— elif SEQ_VAR in (5, 6,7,8) & diff(DATE_VAR2, DATE_VAR1) ≥1
— else diff(DATE_VAR2, DATE_VAR1) ≥4

It would look like this if you used the conditional WHERE clause:

SELECT
DAT.ID_VAR,
DAT.SEQ_VAR,
DAT.NUM_VAR,
DATE_VAR1,
DATE_VAR2,
TRUNC(DATE_VAR2) - TRUNC(DATE_VAR1) AS LAG_IN_DATES
FROM
CURRENT_TABLE      DAT
WHERE
(TRUNC(DATE_VAR2) - TRUNC(DATE_VAR1)) >= CASE WHEN SEQ_VAR IN (1,2,3,4) THEN 0 WHEN SEQ_VAR IN (5,6,7,8) THEN 1 ELSE 2 END
ORDER BY ID_VAR, SEQ_VAR

#4 Use IN and EXISTS Clauses

When creating SQL queries, you should avoid utilizing the IN-operator clause. For example, in the query below, we first checked for the product id in the [Industry].[TransactionHistory]) field, then searched the [Industry].[emp] table for the associated entries.

Select * from [Industry].[emp] p
where empid IN
(select empid from [Company2019].[Industry].[TransactionHistory]);
Go

The IN clause has been replaced by an EXISTS clause in the query below.

Select * from [Industry].[emp] p
where EXISTS
(select empid from [Company2019].[Industry].[TransactionHistory])

#5 Use the Command SET NOCOUNT ON

Use SET NOCOUNT ON when performing INSERT, SELECT, DELETE, and UPDATE operations. Since SQL always gives the affected number of rows for such procedures, it might hinder speed when you have complex queries with a lot of joins.

SQL will not count the impacted rows if you use SET NOCOUNT ON, which will increase performance.

The notification indicating the number of rows affected is not displayed in the following example.

USE Company2019;
GO
SET NOCOUNT OFF;
GO
-- Display the count message
SELECT TOP(10)LastName
FROM emp.employee
WHERE LastName LIKE 'A%';
GO
-- To disable the count message, set NOCOUNT to ON
SET NOCOUNT ON;
GO
SELECT TOP(10) LastName
FROM emp.employee
WHERE LastName LIKE 'A%';
GO
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO

#6 Missing Indexes

When we run a SQL query and look for the actual execution plan in SQL Server Management Studio (SSMS), we occasionally get a suggestion for an index that could help us enhance our SQL query.

You can also inspect the details of missing indexes in your environment using the dynamic management views.

• sys.dm_db_missing_index_details
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_groups
• sys.dm_db_missing_index_columns

DBAs usually design indexes based on SSMS's recommendations. For the time being, it may help SQL query optimization. However, you should not make the index solely based on those suggestions. It may have an impact on the performance of other queries and slow down your INSERT and UPDATE operations.

#7 Use Wildcards

Wildcards can be used at the beginning or end of words and sentences to function as placeholders. You can utilize wildcards in the SELECT statement at the end of a phrase to make data retrieval more efficient and faster. Consider the following scenario:

SELECT
p.empID
,p.FirstName
,p.LastName
,p.Title
FROM emp.employee p
WHERE p.FirstName LIKE 'Jo%';

As a result, the query will return a list of employees whose First Name satisfies the criterion, i.e., their First Name begins with 'Jo.'

#8 Use TOP

Set a limit on the number of records returned from the database with the SELECT TOP command. You can use this command to fetch numerous rows as a sample to ensure that your query returns the desired result.

Take, for example, the query from the previous section and set a result set limit of 10 records.

SELECT TOP 10
p.empID,
p.FirstName,
p.LastName,
p.Title
FROM emp.employee p
WHERE p.FirstName LIKE 'Jo%';

#9 Fully Qualify Database Object Names

The use of fully qualified database object names is intended to eliminate ambiguity. The following is an example of a fully qualified object name:

DATABASE.SCHEMA.OBJECTNAME.

It's critical to declare what you want to access when you have access to numerous databases, schemas, and tables. This isn't required until you're working with huge databases with various users and schemas, but it's a good practice to follow.

As a result, rather of utilizing a statement like:

SELECT * FROM emp

Use these words:

SELECT * FROM dbo.emp

#10 Minimize Large Write Operations

When updating and altering data, adding indexes or checking constraints to queries, processing triggers, and so on, writing, modifying, deleting, or importing huge volumes of data may influence SQL query performance and even cause the table to be blocked.

Furthermore, writing a large amount of data increases the size of log files. As a result, while big write operations may not be a significant performance issue, you should be aware of the repercussions and be prepared in the event of unexpected behavior.

#11 Secure Your Code

Databases store a wide range of data, making them great targets for hackers. SQL injections, in which a user enters a SQL query instead of a username, retrieves or modifies your database, are a common attack.

SQL injections can take the form of:

empIdInText = getRequestString("empID");
textSQL = "SELECT * FROM emp WHERE empID = " + empIdInText;

Assuming you have this, empIdInText will retrieve the emp's input. Here's where things can go wrong:

SELECT * FROM emp WHERE empID = 988 OR 1=1;

It will retrieve all of the data from the emp database because 1=1 is always true.

You may protect your database from SQL injections by utilizing parameterized statements, input validations, and input sanitization, among other things. The DBMS determines how you secure your database. To build secure code, you'll need to understand your database management system and its security issues.

Finally!!!

We've gone through some of the most essential SQL query optimization techniques that we've learned over the years and that have shown to be effective. There are a variety of other query optimization techniques available, such as using where instead of having, using pagination, and so on.

You could profit from faster query performance, fewer production troubles, and cost savings by minimizing resources if you faithfully follow the query optimization procedures outlined above.

SQL query optimization is a never-ending journey. You may increase performance by implementing best practices and making minor adjustments.  We hope this article will be beneficial to you in avoiding any potential performance concerns.

Monitor Your SQL Performance Atatus

Atatus provides you an in-depth perspective of your SQL performance by uncovering slow SQL queries that occur within your requests, as well as transaction traces, to give you actionable insights.

With normalized queries, you can see a list of all slow SQL calls to see which tables and operations have the most impact, know exactly which function was used and when it was performed, and see if your modifications improve performance over time.

Atatus can be beneficial to your business, which provides a comprehensive view of your application, including how it works, where performance bottlenecks exist, which users are most impacted, and which errors break your code for your frontend, backend, and infrastructure.

Try your 14-day free trial of Atatus.