QUARY PERFORMANCE TUNING

SQL Tuning or SQL Optimization

Sql Statements are used to retrieve data from the database. We can get same results by writing different sql queries. But use of the best query is important when performance is considered. So you need to sql query tuning based on the requirement. Here is the list of queries which we use reqularly and how these sql queries can be optimized for better performance.

SQL Tuning/SQL Optimization Techniques:

1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.

For Example: Write the query as

SELECT id, first_name, last_name, age, subject FROM student_details;

Instead of:

SELECT * FROM student_details;

2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes. 
For Example: Write the query as

SELECT subject, count(subject) 
FROM student_details 
WHERE subject != 'Science' 
AND subject != 'Maths' 
GROUP BY subject;

Instead of:

SELECT subject, count(subject) 
FROM student_details 
GROUP BY subject 
HAVING subject!= 'Vancouver' AND subject!= 'Toronto';

3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query. 
For Example: Write the query as

SELECT name 
FROM employee 
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age) 
FROM employee_details) 
AND dept = 'Electronics'; 

Instead of:

SELECT name 
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details) 
AND age = (SELECT MAX(age) FROM employee_details) 
AND emp_dept = 'Electronics';

4) Use operator EXISTS, IN and table joins appropriately in your query. 
a) Usually IN has the slowest performance  comaped to exist.
b) IN is efficient when most of the filter criteria is in the sub-query. 
c) EXISTS is efficient when most of the filter criteria is in the main query.

For Example: Write the query as

Select * from product p 
where EXISTS (select * from order_items o 
where o.product_id = p.product_id)

Instead of:

Select * from product p 
where product_id IN 
(select product_id from order_items

5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship. 
For Example: Write the query as

SELECT d.dept_id, d.dept 
FROM dept d 
WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);

Instead of:

SELECT DISTINCT d.dept_id, d.dept 
FROM dept d,employee e 
WHERE e.dept = e.dept;

6) Try to use UNION ALL in place of UNION. 
For Example: Write the query as

SELECT id, first_name 
FROM student_details_class10 
UNION ALL 
SELECT id, first_name 
FROM sports_team;

Instead of:

SELECT id, first_name, subject 
FROM student_details_class10 
UNION 
SELECT id, first_name 
FROM sports_team;

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

SELECT id, first_name, age FROM student_details WHERE age > 10;

Instead of:

SELECT id, first_name, age FROM student_details WHERE age != 10;

Write the query as

SELECT id, first_name, age 
FROM student_details 
WHERE first_name LIKE 'Chan%';

Instead of:

SELECT id, first_name, age 
FROM student_details 
WHERE SUBSTR(first_name,1,3) = 'Cha';

Write the query as

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

Instead of:

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

Write the query as

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

Instead of:

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

Write the query as

SELECT id, name, salary 
FROM employee 
WHERE dept = 'Electronics' 
AND location = 'Bangalore';

Instead of:

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

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

Write the query as

SELECT id, name, salary 
FROM employee 
WHERE salary < 25000;

Instead of:

SELECT id, name, salary 
FROM employee 
WHERE salary + 10000 < 35000;

Write the query as

SELECT id, first_name, age 
FROM student_details 
WHERE age > 10;

Instead of:

SELECT id, first_name, age 
FROM student_details 
WHERE age NOT = 10;

8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause.
For Example: Write the query as

SELECT id FROM employee 
WHERE name LIKE 'Ramesh%' 
and location = 'Bangalore';

Instead of:

SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee 
WHERE name LIKE 'Ramesh%';

9) To store large binary objects, first place them in the file system and add the file path in the database.

10) To write queries which provide efficient performance follow the general SQL standard rules.

a) Use single case for all SQL verbs
b) Begin all SQL verbs on a new line
c) Separate all words with a single space 
d) Right or left aligning verbs within the initial SQL verb

Beginner SQL Tutorial Tips

Here is a few SQL Tips that may save you time and trouble with your SQL table work, SQL command statements ...

Ideally, you want to get best results when writing SQL queries, minimizing errors and getting the best query performance when needed. Here is a small list of queries tips that may help you in your SQL queries work and that can optimized SQL for better performance.

SQL Tutorial Tips:

SQL SELECT Tip 1

SQL SELECT only the columns needed, avoid using SELECT *. First, for each column that you do not need every SQL Server performs additional work to retrieve and return them to the client, and the second volume of data exchanged between the client and SQL Server increases unnecessary. Reference: SQL SELECT Statement page.

SQL SELECT Tip 2

SQL SELECT only the rows needed. The less rows retrieved, the faster the SQL query will run.

SQL SELECT Tip 3

Prune SQL SELECT lists. Every column that is SELECTed consumes resources for processing. There are several areas that can be examined to determine if column selection is really necessary.

Example:

WHERE (COL8 = ‘X’)

If a SQL SELECT contains a predicate where a column is equal to one value, that column should not have to be retrieved for each row, the value will always be ‘X’.

SQL TABLE Tip 4

When you create a new table always create a unique clustered index belong to it, possibly it is a numeric type.

SQL JOIN Tip 5

Use SQL JOIN instead of subqueries. As a programmer, subqueries are something that you can be tempted to use and abuse. Subqueries, as show below, can be very useful:

SELECT a.id, 
(SELECT MAX(created) 
FROM posts 
WHERE author_id = a.id) 
AS latest_post FROM authors a

Although subqueries are useful, they often can be replaced by a join, which is definitely faster to execute.

SELECT a.id, MAX(p.created) AS latest_post 
FROM authors a 
INNER JOIN posts p 
ON (a.id = p.author_id) 
GROUP BY a.id

SQL OR Tip

The following example use the OR statement to get the result:

SELECT * FROM a, b WHERE a.p = b.q OR a.x = b.y;

The UNION statement allows you to combine the result sets of 2 or more select queries. The following example will return the same result that the above query gets, but it will be faster

SELECT * FROM a, b WHERE a.p = b.q
UNION
SELECT * FROM a, b WHERE a.x = b.y;

SQL Clustered Index Tip

Keep your clustered index small. One thing you need to consider when determining where to put your clustered index is how big the key for that index will be. The problem here is that the key to the clustered index is also used as the key for every non-clustered index in the table. So if you have a large clustered index on a table with a decent number of rows, the size could blow out significantly. In the case where there is no clustered index on a table, this could be just as bad, because it will use the row pointer, which is 8 bytes per row.

SQL Optimizing Tip

8. Avoid cursors. A bit of a no-brainer. Cursors are less performing because every FETCH statement executed is equivalent to another SQL SELECT Statement execution that returns a single row. The optimizer can’t optimize a CURSOR statement, instead optimizing the queries within each execution of the cursor loop, which is undesirable. Given that most CURSOR statements can be re-written using set logic, they should generally be avoided.

SQL Computer Columns Tip

9. Use computed columns Computed columns are derived from other columns in a table. By creating and indexing a computed column, you can turn what would otherwise be a scan into a seek. For example, if you needed to calculate SalesPrice and you had a Quantity and UnitPrice column, multiplying them in the SQL inline would cause a table scan as it multiplied the two columns together for every single row. Create a computed column called SalesPrice, then index it, and the query optimiser will no longer need to retrieve the UnitPrice and Quantity data and do a calculation – it’s already done.

SQL Tip

10. Pattern matching: there is always the nasty LIKE and NOT LIKE, but Postgres supports regular expressions too.

SQL Tip: ORDER BY

11. ORDER BY USING: lets you specify an arbitrary sort function. Not quite sure how to use this yet? You can also do stuff like ORDER BY col1 + col2, which is cool.

SELECT col1, col2 FROM table_test
ORDER BY col1 + col2;