Last week I was revisiting one of stored procedure for performance improvement, existing procedure had some complex conditions that was making it few table several times in IF condition & Select for data retrieval (most of times sort of data is retrieved). Tables that were queried several times is supposed to giant with data and same can be referred by another process or same process from different client.
When I was revisiting the procedure, I thought to get all the required data from these huge tables and hold it in Temp table. So I can avoid having shared locks on giant tables to avoid blocking and reducing wait time for other processes.
After re-written the procedure I noticed that the IO & time consumption of new procedure is slightly on higher side than of the older one (might because I am reading the data and writing to the temp table to read it later…J ), I could not compare the both with concurrent users.
I am looking for expert’s opinion on my thoughts as I didn’t see this practice mentioned anywhere.
Regards,
No comments:
Post a Comment