SQL Optimization

SQL Statements are used to fetch data from the database. We can retrieve the same results by writing different SQL queries. But the use of the best query is important when performance is considered. So you need to SQL query optimization based on the requirement. optimizing the SQL queries becomes important way to maximize the throughput of a system .SQL database optimization techniques can be an extremely difficult task, in particular for large-scale data wherever a minute variation can result or impact badly on the performance.  Here below is the list of queries which we use regularly and how these SQL queries can be optimized for better performance.

SQL Optimization Techniques:

1. SQL query becomes faster by using the names of the actual column in SELECT statement instead of  '*'.

> For Example: Write query as

    SELECT id,name, age, subject FROM student;

> Instead of:

    SELECT * FROM student;

2. HAVING clause just like a filter which is used to filter the rows after all the rows are selected. never use HAVING clause for any other purposes.

> For Example: Write the query as

    SELECT count(subject) 
    FROM student 
    WHERE subject != 'English' 
    AND subject != 'Science' 
    GROUP BY subject;

> Instead of:

    SELECT count(subject) 
    FROM student 
    GROUP BY subject 
    HAVING subject!= 'English' AND subject!= 'Math';

3. In Many Cases, you may have more than one subqueries in your main query. always minimize the number of subquery block in your query.
 
> For Example: Write the query as

    SELECT name 
    FROM employee 
    WHERE (experience, age ) = (SELECT MAX (experience), MAX (age) 
    FROM employee) 
    AND dept = 'IT'; 

> Instead of:

    SELECT name 
    FROM employee
    WHERE experience = (SELECT MAX(experience) FROM employee) 
    AND age = (SELECT MAX(age) FROM employee) 
    AND dept = 'IT';

4. Limit the size of the Working Data Set:-

Verify the tables used in the SELECT to check if any possible filters can be applied to the WHERE statement. As during the extend of time, the SQL query grows, the solution would be to look or specify the query to check only the limited or monthly data.

5. Be careful while using conditions in the WHERE clause.
 
> For Example: Write the query as

    SELECT id, name, age FROM student
    WHERE age > 10;

> Instead of:

    SELECT id,name, age FROM student 
    WHERE age != 10;

> Write the query as

    SELECT id, name, age 
    FROM student_details 
    WHERE name LIKE 'Deep%';

> Instead of:

    SELECT id, name, age 
    FROM student_details 
    WHERE SUBSTR(name,1,3) = 'Deep';

> Write the query as

    SELECT id, name, age 
    FROM student
    WHERE name LIKE NVL ( :name, '%');

>Instead of:

    SELECT id, name, age 
    FROM student 
    WHERE name = NVL ( :name, name);

> Write the query as

    SELECT product_id, product_name 
    FROM product_Detail
    WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)

> Instead of:

    SELECT product_id, product_name 
    FROM product_Detail 
    WHERE unit_price >= MAX(unit_price) 
    and unit_price <= MIN(unit_price)

> Write the query as

    SELECT id, name, salary 
    FROM employee 
    WHERE dept = 'IT' 
    AND location = 'Lucknow';

> Instead of:

    SELECT id, name, salary 
    FROM employee 
    WHERE dept || location= 'ITLucknow';

Use non-column expression on one side of the query because it will be processed earlier.