In digital India, as all things are going to be on the website, so as a website developer we often hear that page is getting slow, and we need to get records within 2 seconds. To display records there are 3 main approaches we have to consider,
- Database
- C# coding
- HTML page
Here we are going to discuss database approach, the points that we should consider,
- We check the table schema to ensure there will not any columns that have datatype max like varchar(max), nvarchar(max), if not needed.
- We check there will be indexes as well as Clustered Index and Non-Clustered Index.
- Avoid creating unwanted indexes as it will cause performance issues.
- If it is indexed and still results are slow then we check the fragmentation of the index. If fragmentation is between 10% to 40%, Index should be reorganized, and if fragmentation is greater than 40% Index should be rebuilt.
- We should check the procedure/query having join on int datatype rather than varchar/nvarchar because join on varchar datatype will decrease the performance.
- We should ensure that there are no unwanted columns in the procedure.
- We should minimize the varchar type column from where condition.
- For a better search, we try to create a temp table with column primary key id that is going to join on another table, with all where conditions, and use this temp table with other join so that the search operation will work smoothly.
For Example, Master table like StudentMaster
| Student_Id |
Student_Name |
| 1 |
ABC |
| 2 |
DEF |
Student_Details
| Student_id |
ClassId |
FatherName |
MotherName |
| 1 |
2 |
Jhon |
Merry |
| 2 |
1 |
Marshal |
Rose |
Class_Master
| Id |
ClassName |
| 1 |
LKG |
| 2 |
UKG |
Here we can use a query like below,
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
DROP TABLE #Results
CREATE TABLE #Results
(
Student_id int
)
insert into #Results (Student_id)
select Student_id from StudentMaster sm
join Student_Details sd on sd.Student_id=sm.Student_id
join Class_Master cm on cm.id=sd.ClassId
where Student_Name='ABC' AND ClassName='LKG'
select Student_id,Student_Name,ClassName,FatherName,MotherName from StudentMaster sm
JOIN #Results RS on Rs.Student_id=sm.Student_id
join Student_Details sd on sd.Student_id=sm.Student_id
join Class_Master cm on cm.id=sd.ClassId
9. Try to avoid nested queries.
These are a few tips that can help you to query optimization