How to improve Performance in SQL

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

Calling Web Service From SQL

How to invoke a Web Service from a Stored Procedure

Introduction

On one occasion an old friend, was planning some applications  using NET and sockets, and needed to call a WebServices from a stored procedure.
In this article I will share this experience so that other programmers can learn to call a WebService sending parameters from a Stored Procedure.

Step 1 

We must create our WebServices Project in Visual Studio.

Step 2 

Then proceed to create the methods that we use in our service, in my case believe 6 methods which are:  Greet (string Param1) and expects a String as the parameter, HelloWord() does not expect any parameters, and the Add, subtract, and Divide proliferation (Num1 int, int num2) want  two integers as parameters.

Step 3 

Proceed to create our stored procedure that will have all the code to invoke the Web Services we have just created, as in all my articles database I use is AdventureWorks, which is the basis of test data that brings SQL Server.

Step 4 

Then publish the web service on our IIS Web Server

Step 5 

We proceed to our Store Procedure Coding the data of our Web Service.

Step 6 

In the procedure to create, we pass a parameter which is the parameter that is waiting Saludar() method, if they want to use the other methods, should create another parameter because as I mentioned above the other methods are expecting 2 parameters integer type.
Something that is also very important when using the sp_OAMethod Stored Procedure this awaiting the POST or GETmethod, default almost always send POST, but if we sent this method, we can not display the XML reading generated by our Web Service, so we must use the GET method.

Step 7 

Proceed to test our Web Service through your browser, type the address of the IIS where the published our Web Service. http://localhost/WebServices/Service1.asmx in the same show all the methods that  we created in our Web Service.

Step 8 

Select the method that we use and invoke from our Stored Procedure, Saludar(). Then proceed to write the parameter you want to happen to the Web Service,  after this we click on Invoke.

Step 9  

After that it will open another page in your browser, the information contained in XML and the parameter to write.

Step 10   

We proceed to execute the stored procedure we just created to invoke our web service.

Step 11    

After running our procedure and send our parameters we get the same result we got when we run it through your browser.

Results    

Look at the comparison and noticed that the same result.

SQL Server Functions

Different Types of SQL Server Functions

Function is a database object in Sql Server. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can’t use function to Insert, Update, Delete records in the database table(s). For more about stored procedure and function refer the article Difference between Stored Procedure and Function

Types of Function

1. System Defined Function

   These functions are defined by Sql Server for different purpose. We have two types of system defined function in Sql Server

   1. Scalar Function

      Scalar functions operates on a single value and returns a single value. Below is the list of some useful Sql Server Scalar functions.
      System Scalar Function

      Scalar Function

      Description

      abs(-10.67)

      This returns absolute number of the given number means 10.67.

      rand(10)

      This will generate random number of 10 characters.

      round(17.56719,3)

      This will round off the given number to 3 places of decimal means 17.567

      upper('dotnet')

      This will returns upper case of given string means 'DOTNET'

      lower('DOTNET')

      This will returns lower case of given string means 'dotnet'

      ltrim(' dotnet')

      This will remove the spaces from left hand side of 'dotnet' string.

      convert(int, 15.56)

      This will convert the given float value to integer means 15.

   2. Aggregate Function

      Aggregate functions operates on a collection of values and returns a single value. Below is the list of some useful Sql Server Aggregate functions.
      System Aggregate Function

      Aggregate Function

      Description

      max()

      This returns maximum value from a collection of values.

      min()

      This returns minimum value from a collection of values.

      avg()

      This returns average of all values in a collection.

      count()

      This returns no of counts from a collection of values.

2. User Defined Function

   These functions are created by user in system database or in user defined database. We three types of user defined functions.

   1. Scalar Function

      User defined scalar function also returns single value as a result of actions perform by function. We return any datatype value from function.

      
       --Create a table 
      CREATE TABLE Employee
      (
       EmpID int PRIMARY KEY,
       FirstName varchar(50) NULL,
       LastName varchar(50) NULL,
       Salary int NULL,
       Address varchar(100) NULL,
      )
      --Insert Data
      Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(1,'Mohan','Chauahn',22000,'Delhi');
      Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(2,'Asif','Khan',15000,'Delhi');
      Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(3,'Bhuvnesh','Shakya',19000,'Noida');
      Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(4,'Deepak','Kumar',19000,'Noida');
      --See created table
      Select * from Employee 
      
      

      

      
       --Create function to get emp full name 
      Create function fnGetEmpFullName
      (
       @FirstName varchar(50),
       @LastName varchar(50)
      )
      returns varchar(101)
      As
      Begin return (Select @FirstName + ' '+ @LastName);
      end 
      
      

      

      
       --Calling the above created function
      Select dbo.fnGetEmpFullName(FirstName,LastName) as Name, Salary from Employee 
      
      

      

   2. Inline Table-Valued Function

      User defined inline table-valued function returns a table variable as a result of actions perform by function. The value of table variable should be derived from a single SELECT statement.

      
       --Create function to get employees
      Create function fnGetEmployee()
      returns Table
      As
       return (Select * from Employee) 
      
      

      

      
       --Now call the above created function
      Select * from fnGetEmployee() 
      
      

      

   3. Multi-Statement Table-Valued Function

      User defined multi-statement table-valued function returns a table variable as a result of actions perform by function. In this a table variable must be explicitly declared and defined whose value can be derived from a multiple sql statements.

      
       --Create function for EmpID,FirstName and Salary of Employee
      Create function fnGetMulEmployee()
      returns @Emp Table
      (
      EmpID int, 
      FirstName varchar(50),
      Salary int
      )
      As
      begin
       Insert into @Emp Select e.EmpID,e.FirstName,e.Salary from Employee e;
      --Now update salary of first employee
       update @Emp set Salary=25000 where EmpID=1;
      --It will update only in @Emp table not in Original Employee table
      return
      end 
      
      

      

      
       --Now call the above created function
      Select * from fnGetMulEmployee() 
      
      

      

      
       --Now see the original table. This is not affected by above function update command
      Select * from Employee 
      
      

      

Note

1. Unlike Stored Procedure, Function returns only single value.
2. Unlike Stored Procedure, Function accepts only input parameters.
3. Unlike Stored Procedure, Function is not used to Insert, Update, Delete data in database table(s).
4. Like Stored Procedure, Function can be nested up to 32 level.
5. User Defined Function can have upto 1023 input parameters while a Stored Procedure can have upto 2100 input parameters.
6. User Defined Function can't returns XML Data Type.
7. User Defined Function doesn't support Exception handling.
8. User Defined Function can call only Extended Stored Procedure.
9. User Defined Function doesn't support set options like set ROWCOUNT etc.