In this tutorial you will learn how to improve sql database performance, learn how you should optimize sql query to get faster output!
Here are top 10 ideas for SQL performance tuning, best way to optimize SQL Query for better performance.
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.
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.
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
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
@table
) is created in the memory. when a Temporary table (#temp
) is created in the tempdb database.@table
variable faster then #temp
Learn more about SQL Temp Table and Table variable
“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.
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.
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())
Try to avoid all above subquery, rather use SQL Join, query will produce faster result.
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 sql query performances.
So always make sure no duplicate data is getting stored, create relationship and use multiple views to meet your business requirement.
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.
Now, there are different ways to optimize sql query, read here about Sql query optimization tips- Part2
You also may be interested in following tutorial