Highly Effective SQL Tips For Software Engineers

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:

select

column_name,

count(1)::float/(select sum(1) from table_name) * 100

from table_name

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:

SELECT

  t.schemaname,

  t.tablename,

c.reltuples::bigint       

AS num_rows,

  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

FROM

  pg_tables t

  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

WHERE

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:

SELECT

  *

FROM USER

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

UNION

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:

SELECT

  p.BusinessEntityID

 ,p.FirstName

 ,p.LastName

 ,p.Title

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

 ,CardNo VARCHAR(128)

 ,ReversedCardNo AS REVERSE(CardNo) PERSISTED

)

GO

CREATE INDEX ByReversedCardNo ON dbo.Customer (ReversedCardNo)

GO

CREATE INDEX ByCardNo ON dbo.Customer (CardNo)

GO

INSERT INTO dbo.Customer (CardNo)

  SELECT

   NEWID()

  FROM master.dbo.spt_valuessv

SELECT TOP 100

  *

FROM Customer c

–searching for CardNo that end in 510c

SELECT

  *

FROM dbo.Customer

WHERE CardNo LIKE’ %510c’

SELECT

  *

FROM dbo.Customer

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.

Conclusion 

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.