Search in Help for developer site.

Thursday 12 July 2018

Timeout exception in stored procedure sql server

How to resolve Timeout exception in Stored Procedure Sql Server.

Using Temp tables.

Today i will be talking about how to improve Stored Procedure performance. I was working on a lengthy stored procedure where some tables are used frequently.
For example tblDoctors, tblPatients and a master table.

In the store procedure i was using these tables around 10 times.
all tables are having around 10 lakh records.

I need to select all records from tblDoctors table based on some key fields and update in patient table or master table.
Likewise i wanted to update around 10 column in each table based on some conditions.
So there were some 10 update and 10 select statements.

So due to that Timeout exception was coming in the stored procedure, Because i was hitting the tables around 10 times and then updating.

So to solve this problem we have added 2 local temporary tables where data was more.
Like #tblDoctors, #tblPatients

Now we will select all records based on some key fields which is used filter those records. Then insert them into Temp tables.
Now the load will be less.
Do your all operations in temp table and at the find Join with actual table and update all the fields from temp to actual tables.

Thanks


2 comments:

  1. Nice articel, This article help me very well. Thank you. Also please check my article on my site about What Is Angular?.

    ReplyDelete