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
