It is true that cloud-native databases are becoming popular, but SQL remains the market standard for handling data, irrespective of what kind of technology you are using. Any domain of software engineering – web development, application building, data science, or data warehousing requires knowledge of SQL.
While you can learn SQL reasonably easily, the knowledge of optimization techniques sets you apart. The more optimized SQL queries you write, the better will be the performance of the database. The SQL tips we are about to share for software engineers are the ones our database engineers at Talent500 use.
Let’s get started.
1.Indexes in databases
Indexes are the essential features of an SQL query. Using the indexes, you can filter data based on your requirement. However, a suboptimal index query can result in slow output, which is why you must know how to optimize the query.
An index field is used as follows:
count(1)::float/(select sum(1) from table_name) * 100
group by column_name
order by 2 desc
This query could be more optimal, but it shows how the index in SQL is used to quickly access a value without parsing through the entire data tables.
As the size of the data tables increases, it is essential to check whether the indexes you created or that were automatically inherited are in use. It is a good practice to delete unused indexes to ensure data size doesn’t increase unnecessarily.
Here’s the query to check used indexes:
pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
psai.indexrelname AS index_name,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
CASE WHEN i.indisunique THEN ‘Y’ ELSE’ N’ END AS “unique”,
psai.idx_scan AS number_of_scans,
psai.idx_tup_read AS tuples_read,
psai.idx_tup_fetch AS tuples_fetched
LEFT JOIN pg_class c ON t.tablename = c.relname
LEFT JOIN pg_indexi ON c.oid = i.indrelid
LEFT JOIN pg_stat_all_indexespsai ON i.indexrelid = psai.indexrelid
t.schemaname NOT IN (‘pg_catalog’, ‘information_schema’)
ORDER BY 1, 2;
Any unused indexes must be deleted. If any mutations on the table are slow, you must check where the index takes up a lot of space.
2.Avoid using multiple OR in the FILTER predicate
You need to change your behavior if you combine two or more conditions in your SQL query with an OR operand. It is better not to use OR; instead, you can split the query into separate search expressions.
SQL cannot process OR with a single operation. It evaluates each component of the OR separately, which can result in poor performance. Let’s see an example:
WHERE Name = @P
OR login = @P;
SQL will process both statements separately, increasing the output time. We can optimize this query by using two SELECT queries and combing them with a UNION operator like this:
SELECT * FROM USER
WHERE Name = @P
SELECT * FROM USER
WHERE login = @P;
SQL will use indexes, and query processing will be much faster.
3.Use wildcards at the end of a phrase only
Wildcards in SQL queries serve as the placeholder for words and phrases. They make data retrieval more efficient and faster. However, it would help if you only used the wildcards in the SELECT statements at the end of the phrases. For example:
FROM Person.Person p
WHERE p.FirstName LIKE ‘And%’;
In this query, you will get a list of all the customers whose First Name matches your specified condition: if the first name starts with ‘And.’
This is a more straightforward query; however, when working with a large data set, you can use wildcards to retrieve data with the SELECT statement. In such a scenario, you cannot rely on simply using wildcards but creating a persisted computed column and running the REVERSE() function is a much more efficient approach.
Consider the following SQL query:
CREATE TABLE dbo.Customer (
id INT IDENTITY PRIMARY KEY
,ReversedCardNo AS REVERSE(CardNo) PERSISTED
CREATE INDEX ByReversedCardNo ON dbo.Customer (ReversedCardNo)
CREATE INDEX ByCardNo ON dbo.Customer (CardNo)
INSERT INTO dbo.Customer (CardNo)
SELECT TOP 100
FROM Customer c
–searching for CardNo that end in 510c
WHERE CardNo LIKE’ %510c’
WHERE ReversedCardNo LIKE REVERSE(‘%510c’)
Here, we are retrieving customers’ information using the last digits of their telephone numbers. A simple wildcard query might be extremely slow, but the back-searching gets easier when used at the end of the phrases using the REVERSE() function.
These techniques to fine-tune and optimize search queries will help you write better SQL queries. As a software engineer, you must know how to optimize SQL queries for improved performance of the databases.
Talent500 is a global team-building platform that Fortune 500 and fast-growing startups trust to hire talent. Sign up here to join our elite talent pool and discover career-redefining opportunities.