I am developing a web page for search. I have two options to build this page
To build the query on page according to parameter enter by the user and send it to server.
To send parameters to stored procedure and build query there and get it executed.
I want to know that which approach I should adopt and why.
I want to know the advantages or disadvantages of both approaches.
From my side store procedure will be good.
Stored procedure will not compile again and again where simple Query compile every time then execute.
Stored procedure execute server side that's why it reduces network traffic. SQL query also executes on server also but if you have big query then it will take more time comparison to Stored Procedure to traverse from client side to server.
by using SQL queries we actually exposing our database design schema(design) in the code which may be changed. so we use stored procedure which are pre-compiled execute-able object which can contain one or more sql statements. Hence stored procedures are the replica of the complex SQL statements. A stored procedure may be written to accept inputs and return output.
If the query is going to be used only sparingly, adhoc query may be an option. On almost all other occasions it is good to go by stored procedures.
Also consider setting optimize for ad hoc workloads option to 1. Following is from msdn
The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.
References:
I'm not a database expert by any means, but my experience is that stored procedures are good for encapsulating fixed queries that don't change over long periods. But it's harder to maintain, at least it was back in the day when I was doing it, because there was no way to keep the stored procedures under revision control and do something like an rsync to the server. Therefore it was easier to maintain just building queries on the fly, as I could make all necessary changes in a single script, or a single group of scripts under revision control. Perhaps things have changed in this regard, and stored procedures can be better integrated with your codebase; in that case, stored procedures might make your code more manageable and maintainable.
Use stored procedures. Not becaue they are faster, but because they enforce a contract with the database (Give the stored procede these parameters and it will return the data).
Let the app decide what to do with the data, like display it in the UI. Let the stored procedure decide how to insert/update/delete/retrieve the data.
It is also much easier to resuse the queries/logic in stored procedures across multiple front ends.
By passing query from front end the chances of sql injection increases . Store prcedure is pre compiled while query first compiled than execute so chances of syntax error increases
I adopt stored procedure approach as its easy to maintain and easy to trouble shoot.
Using a stored procedure is always better.
1) If your query is long it will consume your network resources. When we send large SQL queries over a network connection It will increase network traffic.
2)Stored procedures are pre compiled and stored hence they are faster.
3) It is also easy to manage when you want to modify the query.
