5 strategies you need to make SQL faster

Photo by Caspar Camille Rubin on Unsplash

SQL is also called structured Query Language. You can pronounce it as Sequel. You can learn SQL quickly if you watch some videos of Tera Tom (He is the guru of SQL).

SQL is the language of data, and if you want to interact with data, you need to know it. These five strategies are useful to make SQL faster.

Strategy Number: 01

Use indexes in where clause of SQL. Let me elaborate more that the columns you are using in the WHERE clause should be already part of the Index columns of that database Table. Checkout PL SQL: How to Fix Errors.

SQL Query, Date of Birth is the index column:

SELECT *
FROM emp_sal_nonppi
WHERE dob <= 2017–08–01;

Strategy Number: 02

Some people use a DISTINCT clause to eliminate duplicates. The idea behind this is we do not want duplicates. The same you achieve it by GROUP BY.

GROUP BY to eliminate duplicates Sample SQL:

SELECT E.empno,
E.lastname
FROM emp E,
empprojact EP
WHERE E.empno = EP.empno
GROUP BY E.empno,
E.lastname;

Strategy Number: 03

WHERE clause in SQL is useful to filter the criteria you need. Some people use the column they use in the WHERE clause in the SELECT statement too. If you speak logically, that is not correct. Also, it causes to consume more CPU time.

SQL Sample to use Columns in SELECT correctly:

SELECT deptno,
deptname
FROM dept
WHERE ( admrdept = ‘E01’
OR deptno = ‘D01’ )
AND deptname = ‘MAIN BRANCH’;

Strategy Number: 04

There are occasions you need to get records from more than one Table. You can achieve this by joining two or more tables.

Left Join is much faster than Right join. If you are familiar with sets, you will know there are many kinds of joins. The SQL parser mainly converts all types of joins to the left outer joins. So to make your SQL faster, LEFT Join is preferable.

SQL that is recommended for better performance:

SELECT column_names
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

Strategy Number: 05

This idea is from SQL gurus that always use index columns in correlated-Subquery. In any SQL query, you can use normal subqueries and Correlated-subqueries. If you identify a subquery is referring to the columns of Outer query, then you can say that query is correlated-subquery. This is the only difference that differentiates.

SQL Correlated-subquery Sample:

The employee_id and payment_type both should be index columns; you referenced those in the correlated-subquery.

SQLSELECT last_name,
first_name
FROM employee e1
WHERE NOT EXISTS
(
SELECT ph.last_name
FROM payment_history ph
WHERE ph.employee_id = e1.employee_id
AND ph.payment_type = ‘award’;

References

--

--

--

Sr.Software Developer - Technology @srinimf.com

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Using Portainer with AWS Elastic Container Registry

READ/DOWNLOAD#) Object Oriented Software Engineering: A Use Case Driven Approach FULL BOOK PDF &…

Run an Azure Function on a DevOps agent as part of the build process

What is Istio? A dummies guide to service mesh

RummyCircle — Pioneer of online gaming in India

“Common Project Risks and Agile Mitigations, Part 2, Requirements” from an Old Blog

spring-boot-plus Everyone can develop projects independently, quickly and efficiently!

Who is the Collaborative Innovation Factory Worker Node?

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Srinimf

Srinimf

Sr.Software Developer - Technology @srinimf.com

More from Medium

Qlik Sense Data Architect Certification QSDA2021 Dumps

Get the Iteration of a Weekday in a Month on a Virtual Calendar

identity in sql and define SQL Server Identity

identity in sql and define SQL Server Identity , sql interview question

Guide to Hiring Development Team in Poland