Free Web Development Online Tutorials, Learn to Code
Tips to improve SQL Database Performance, SQL Query Performance, and SQL Best Practice

SQL Performance Improvement

In this tutorial you will learn how to improve sql database performance.

improve sql performance

SQL Database Performance Tuning Tips

  1. Setup right Data Type

    Setting up appropriate data type while creating table is very important, like for string data type use n [nvarchar] instead of Text or Varchar, Text field was designed for storing large data, nvarchar is better than Varchar, because Varchar unnecessary occupy specified length, when nvarchar occupy only used space.

  2. Don't use select * from in SELECT statement

    You should always specify the column names with comma separation instead of using “* from”, because “* from” statement unnecessary fetch all the columns in that table or view, add extra load to query, which can be easily avoided.

  3. SET NOCOUNT ON

    Whenever you write any stored procedure, make sure you specify “SET NOCOUNT ON”, because by default SQL query always return the number of rows affected for any operation, by setting NOCOUNT ON, you can reduce some load and improve performance.

    CREATE PROCEDURE usp_UpdateDetails
    @studentId bigint
    AS
    BEGIN
    SET NOCOUNT ON;
        declare @currentStudentId bigint
        select @currentStudentId=StudentId from tbStudent
        where StudentId=@studentId
        update tbAdmission
        set AdmissionDate=getdate()
        where StudentId=@currentStudentId               
    END
    GO
  4. Specify Schema name before object name

    Specifying schema name before object name is always considered as best practice, when you specify the schema name before object, it clearly indicates where to search, thus help query getting the result faster.

    Think of situation like if there are same object name under two different schema.

    Select [nickName] from students.student
    Select [nickName] from dbo.student
    
  5. Use Table variable instead of Temp table
  6. Don’t name procedure with “sp_” prefix

    You should not name any user-defined stored procedure with “sp_” prefix, this will indicate SQL server to search the stored procedure in master database, because all system defined stored procedure are prefixed with “sp_” , as a result this may slow down the performance.

  7. Use Try-Catch block to know the error root cause

    You should always use Try-Catch block, especially when writing any user defined function, stored procedure, triggers etc or any long code block, try to keep them as short as possible, that also help maintaining the code and indentifying the error root cause with no time.

    This practice also can save you from query causing dead-lock situation.

  8. Use EXISTS instead of IN with Where Clause

    Often we write query to find multiple matching records with where clause. like following two queries, both will get you the same result.

     --Good practice
    SELECT ProductName,Price, Size FROM tblProduct
    where PId EXISTS (Select distinct PId from tblOrder where odate >= getdate())
    

    Above query will perform faster than query below

     --Bad practice
    SELECT ProductName,Price, Size FROM tblProduct
    where PId IN (Select distinct PId from tblOrder where odate >= getdate())
    
  9. Avoid Storing Duplicate Data

    Duplicate data on multiple tables can cause many problems like add unnecessary overload, difficult to maintain, and considered as bad design, and obviously create impact on query performances.

    So always make sure no duplicate data is getting stored, create relationship and use multiple views to meet your business requirement.

  10. Use Transaction

    Use transaction wherever you want to update more than one table, so if anything goes wrong you can take action immediately rather than waiting to be notified by error handling mechanism.

    This will help unnecessary code not gets executed, and cause some other problem.

Comment
Name
Email
Website
Subscribe