How to optimize the queries

How to optimize the queries:

Order of the cost of the operators (Operators at the top will produce results faster than those listed at the bottom)
= >, >=, <, <= LIKE<>
• Out of these two queries, the second one will run much faster than the first if the column column_name has index on it.

SELECT column_name
FROM table_name
WHERE LOWER (column_name) = 'name'

SELECT column_name
FROM table_name
WHERE column_name = 'NAME' or column_name = 'name'

• In where condition,
WHERE SUBSTRING (firstname, 1, 1) = 'm'
WHERE firstname like 'm%'
Both of these WHERE clauses produce the same result, but the first one is non-sargable (it uses a function) and will run slow, while the second one is sargable, and will run much faster.

• In the following statements, they are in the order decreasing the cost.
Avoid using “NOT IN” Clause because, SQL Server optimizer has to use a nested table scan to perform this activity
Use EXISTS or NOT EXISTS.
Use IN.
Perform a LEFT OUTER JOIN and check for a NULL condition.

• When you need to use the LIKE Operator,
Use LIKE 'm%'
Than LIKE '%m'
If the leading character in a LIKE clause is a wildcard, the Query Optimizer will not be able to use an index, and a table scan must be run, reducing performance and taking more time.

The more leading characters you can use in the LIKE clause, the more likely the Query Optimizer will find and use a suitable index
• When you have a choice of using the IN or the BETWEEN clauses in your Transact-SQL, you will generally want to use the BETWEEN clause, as it is much more efficient. For example:

SELECT customer_number, customer_name
FROM customer
WHERE customer_number in (1000, 1001, 1002, 1003, 1004)

Is much less efficient than this:

SELECT customer_number, customer_name
FROM customer
WHERE customer_number BETWEEN 1000 and 1004

• Where possible, avoid string concatenation in SQL code, as it is not a fast process, contributing to overall slower performance of your application

• If you have a WHERE clause that includes expressions connected by two or more AND operators,
SQL Server will evaluate them from left to right in the order they are written.
This assumes that no parenthesis have been used to change the order of execution.
Because of this, you may want to consider one of the following when using AND:

•Locate the least likely true AND expression first. This way, if the AND expression is false, the clause will end immediately, saving time.
•If both parts of an AND expression are equally likely being false, put the least complex AND expression first. This way, if it is false, less work will have to be done to evaluate the expression.

• SELECT employeeID, firstname, lastname
FROM names
WHERE dept = 'prod' or city = 'Orlando' or division = 'food'

If we have index on dept, but not on city and division, this index will not be useful in this query.
So, instead of using the above query we can use the following query.

SELECT employeeID, firstname, lastname
FROM names
WHERE dept = 'prod'
UNION ALL
SELECT employeeID, firstname, lastname
FROM names
WHERE city = 'Orlando'
UNION ALL
SELECT employeeID, firstname, lastname
FROM names
WHERE division = 'food'
• If your SELECT statement contains a HAVING clause, write your query so that the WHERE clause does most of the work (removing undesired rows) instead of the HAVING clause do the work of removing undesired rows.

• If we are joining some tables, always use the table which is having much data at the top and the table which is having less data at the bottom. And also, use the hard coded values at the bottom. Because, the execution starts from the bottom to top it should filter out all the hard coded values and Joins at the bottom itself which does not allow searching the Heavy table to FULL ACCESS.

• Virtual Indexes: Using virtual indexes we can decrease time of the execution of the query.
Syntax:
If the table has full access (We can check Table access in Explain plan) then we can create the virtual index on these table aliases. Say table_1 and table_2 are the two tables which have full access. Create the VI on these 2 like in the following way.

Select /* + INDEX (table_alias_1) INDEX (table_alias_2) */From ….Where ….

But it increases the cost to execute the query…which is a big disadvantage because if we use the query in reports or any other tools or in packages then it will take more time because in all the above cases cost is crucial.

The advantage of using virtual indexes is that we can see the output of the query quickly.