SQL (Structured Query Language) performance tuning can be an incredibly challenging task, especially when working with large-scale data where even the smallest change can have a dramatic performance effect, positively or negatively. In order to get the exact data we’re looking for we need to provide the appropriate query.
So, in this blog post, we are going to discuss the top 10 SQL query optimization techniques to get precise data from the database.
#1 Define the requirements
Frame the optimal requirements before starting to write the query. This will help refine the query to avoid fetching unwanted data from the table.
#2 SELECT fields, rather than using SELECT*
Use the SELECT statement optimally, instead of always fetching all data from the table. Fetch only the necessary data from the table, thereby avoiding the costs of transferring unwanted data and processing it.
Inefficient
1
SELECT * FROM Business
Efficient
1
SELECT Name, Phone, Address, CompanyZip FROM Business
This query is much simpler, and only pulls the required details from the table.
#3 Avoid DISTINCT in SELECT query
SELECT DISTINCT is a simple way of removing duplicates from a database. SELECT DISTINCT works to generate distinct outcomes by using the **GROUP BY **clause, which groups all the fields in the query. However, a large amount of processing power is required to do this. So, avoid DISTINCT in SELECT queries.
Inefficient
1
SELECT DISTINCT FName, LName, Country FROM Customers
Multiple people in the same country might have the same first and last name.
Efficient
1
SELECT ID, FName, LName, Country, State, City, Zip FROM Customers
Unduplicated records are returned without using SELECT DISTINCT by adding more fields.
#4 Indexing
Indexing in SQL Server helps retrieve data more quickly from a table, thereby giving a tremendous boost to SQL query performance. Allow effective use of clustered and non-clustered indexes. Understand the query’s intent and choose the right form for your scenario.
Use a covering index to reduce the time needed for the execution of commonly used statements. Indexes occupy disk space. The more indexes you have, the greater the space used on the disk. In SQL Server, a clustered index requires no additional disk space, but any non-clustered index needs additional disk space as it is stored separately from the list.
#5 To check the existence of records, use EXISTS() rather than COUNT()
Both EXISTS() and COUNT() methods can be used to check the existence of a record entry in the table. The EXISTS() method is more effective as it exits processing as soon as it finds the first entry of the record in the table. The COUNT() method would scan the entire table to return the number of records in the table that match the provided constraint.
Inefficient
1
2
IF (SELECT COUNT(Id) FROM Business WHERE Name like ‘ABC%’) > 0
PRINT ‘YES’
Efficient
1
2
IF EXISTS (SELECT Id, Name FROM Business WHERE Name like ‘ABC%’)
PRINT ‘YES’
#6 Limit your working data set size
The less data retrieved, the faster the query will run. Instead of adding too many client-side filters, filter the data at the server as much as possible. This limits the data sent on the wire, and you will be able to see the results much more quickly.
#sql #tips and tricks #database
