SQL Query Optimization Techniques

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.
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 

Retrieve output inserted.id along with extra column from other table after inserting into some table

DECLARE @emp AS TABLE (id INT , firstname VARCHAR(50), lastname VARCHAR(50))
DECLARE @emp_staff AS TABLE (staffId INT, empId INT, created DATETIME)
DECLARE @staff AS TABLE (id INT IDENTITY NOT NULL,  FirstName VARCHAR(100), LastName VARCHAR(100))

INSERT INTO @emp VALUES (22,'John', 'Thompson')
INSERT INTO @emp VALUES (23,'Ricky', 'Bismen')
INSERT INTO @emp VALUES (24,'Jas', 'Poleam')
INSERT INTO @emp VALUES (25,'Andy', 'Ting')

--// Instead of below code Merge is a correct way to getting output id with extra column

--INSERT INTO @staff (FirstName, LastName)
--OUTPUT INSERTED.id, e.id ,GETDATE()
--INTO @emp_staff
--(
-- staffId,
-- empId,
-- created
--)
--SELECT firstname, lastname
--FROM @emp e


MERGE INTO @staff USING (SELECT id, FirstName, LastName FROM @emp e WHERE e.id > 23) e ON 1 = 0
WHEN NOT MATCHED THEN 
INSERT (FirstName, LastName) VALUES (FirstName, LastName) 
OUTPUT  INSERTED.id, e.id ,GETDATE() 
INTO @emp_staff
(
staffId,
empId,
created
);

SELECT * FROM @emp
SELECT * FROM @staff

SELECT * FROM @emp_staff

This is to get a list of Running Transactions.

SELECT sqltext.TEXT,

 req.session_id,

 req.status,

 req.command,

 req.cpu_time,

 req.total_elapsed_time

 FROM sys.dm_exec_requests req

 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

 where DB_NAME(req.database_id) = ‘master’

Difference beween Rank(), Dense_Rank(), Row number()

DECLARE @OrderRanking AS TABLE

(

OrderID INT IDENTITY(1,1) NOT NULL,

CustomerID INT,

OrderTotal decimal(15,2)

)

INSERT into @OrderRanking (CustomerID, OrderTotal)

SELECT 1, 1000

UNION ALL

SELECT 1, 500

UNION ALL

SELECT 1, 650

UNION ALL

SELECT 1, 650

UNION ALL

SELECT 1, 3000

UNION ALL

SELECT 2, 1000

UNION ALL

SELECT 2, 2000

UNION ALL

SELECT 2, 500

UNION ALL

SELECT 2, 500

UNION ALL

SELECT 3, 500

UNION ALL

SELECT 3, 20

SELECT * FROM @OrderRanking

SELECT *,

ROW_NUMBER() OVER (ORDER BY OrderTotal DESC) AS RN,

ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS RNP,

RANK() OVER (ORDER BY OrderTotal DESC) AS R,

RANK() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS RP,

DENSE_RANK() OVER (ORDER BY OrderTotal DESC) AS DR,

DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS DRP

FROM @OrderRanking

Introduced in SQL Server 2008 : DateTime and DateTime2

DateTime and DateTime2

The datetime2 datatype was introduced in SQL Server 2008 along with the date and time datatypes.
Unlike the datetime datatype in SQL Server, the datetime2 datatype can store time value down to
microseconds and avoids the 3/1000 second rounding issue.
The precision with a datetime2 is upto 100 nanoseconds.

DECLARE @dt AS DATETIME
SET @dt = GETDATE()
SELECT @dt

DECLARE @dt2 AS DATETIME2
SET @dt2 = GETDATE()
SELECT @dt2

As you can see, when using the datetime datatype is rounded to increments of .000, .003, or .007 seconds.
However the datetime2 has a larger date range, a larger default fractional precision, and optional
user-specified precision. The precision scale is 0 to 7 digits, with an accuracy of 100 nanoseconds.
The default precision is 7 digits.

Moreover datetime2 supports a date range of 0001-01-01 through 9999-12-31 while the datetime type
only supports a date range of January 1, 1753, through December 31, 9999. The timerange as mentioned
earlier in case of datetime is 00:00:00 through 23:59:59.997
whereas in datetime2 is 00:00:00 through 23:59:59.9999999.

Stuff function in T SQL

The Stuff Function in SQL Server is used to deletes a sequence of characters from a source string and then inserts a string into another string.

Character_Expression: String on which you want to insert New_String using the SQL Server stuff function.

Starting_Position: From which index position, you want to start inserting the New_String characters.

Length: How many characters you want to delete from the Character_Expression. SQL Stuff Function will start at Starting_Position and delete the specified number of characters from the Character_Expression

New_String: New string you want to stuff inside the Character_Expression at Starting_Position

Syntax: SELECT STUFF (Character_Expression, Starting_Position, Length, New_String)

FROM [Source]

DECLARE @Character_Expression varchar(50)

SET @Character_Expression = 'Learn Server' 

-- Starting Position = 6 and End Position = 0

SELECT STUFF (@Character_Expression, 6, 0, ' SQL') AS 'SQL STUFF' 

-- Starting Position = 7 and End Position = 6

SELECT STUFF (@Character_Expression, 7, 6, 'SQL') AS 'SQL STUFF' 

-- Starting Position = 1 and End Position = 5

SELECT STUFF (@Character_Expression, 1, 5, '') AS 'SQL STUFF' 

-- Starting Position = 20 and End Position = 5

SELECT STUFF (@Character_Expression, 20, 5, 'SQL ') AS 'SQL STUFF'

One more use of STUFF to get comma separated values in a a rows as per given below example.

DECLARE @TblCity TABLE(CountryCode INT, City VARCHAR(50))

INSERT @TblCity(CountryCode, City)

VALUES

(1, 'Johannesburg'), 

(1, 'Cape Town'), --South Africa

(2, 'New York'), 

(2, 'Washington'), --USA

(3, 'Paris'),

(3, 'Nice'), --France

(4, 'Rome'), 

(4, 'Bologna'), --Itlay

(5, 'Athens'), 

(5, 'Volos') --Greece

---// Add the Following SELECT FOR XML PATH Query to concatenate the Cities belonging to the same country code onto one line:

--Concat

SELECT DISTINCT CountryCode,

STUFF((SELECT ',' + City

FROM @TblCity t1

WHERE t1.CountryCode = t2.CountryCode

FOR XML PATH('')), 1, 1,'') 

FROM @TblCity t2

Below is also an example to make comma separated values in a column.

But this STRING_AGG() function can be used in and above versions of SQL Server 2017

STRING_AGG is an aggregate function that takes all expressions from rows and concatenates them into a single string. Expression values are implicitly converted to string types and then concatenated. The implicit conversion to strings follows the existing rules for data type conversions.

SELECT CountryCode, City = STRING_AGG(City, ', ') WITHIN GROUP (ORDER BY CountryCode)

FROM @TblCity

GROUP BY CountryCode

As compare to FOR XML PATH query, STRING_AGG() function is much faster.. detail comparison given in below link.

https://www.mssqltips.com/sqlservertip/5542/using-for-xml-path-and-stringagg-to-denormalize-sql-server-data/

TSQL query to split full name into firstname and lastname

This script only works with combination of firstname+' '+lastname.
--firstname
SUBSTRING(fullname, 1, CHARINDEX(' ', fullname) - 1) as firstname
--lastname
SUBSTRING(fullname, CHARINDEX(' ', fullname) + 1, LEN(fullname)) as lastname