Optimizing multiple SProc Call.

Question

Hello Everyone I have a enterprise application in which i have multiple functions that executes same sql stored procedure with same parameters. This causes the redundancy of stored procedures. By any means is it possible to reduce/optimize the stored procedure call by creating property ? If yes then please help or suggest any other way. Regards

Rp Singh · Answer

Consider this scenario - SP - dbo. executeMe the code is as - functionabc() { .....statements execdbo.executeMe //letssay values-- storeresult-- } functioncde() { .....statements execdbo.executeMe //letssay values-- storeresult-- } functionefg() { .....statements execdbo.executeMe //letssay values-- storeresult-- } I have another page lets say page2.cs which has two functions which aslo executs the execdbo.executeMe //letssay values-- storeresult- Now how i can execute the function only once and get the values all the time. ( USE THE 1ST EXECUTED STATEMENT) all the time without invoking the sp again and again. Causing redundant sp reduces the optimization. Solutions are welcome..

Bikesh Srivastava · Answer

Hi  To optimize sql query you can try many tricks. 1:-Apply indexing on each where condition 2:-Try to remove subquery from procedure. 3:-Try to remove multiple query where you don't need .