Menu
I'm writing a .NET application and was wondering... should I really write a stored procedure for every query I have, or is there some role of thumb here?
I know the benefits of writing SPs (like security, not having to recompile code to change a query, query compilation).
But I find myself many times in situations where I just need a simple select or delete operation against my DB (really really simple stuff - with not parameters) - so what do you think is better - writing a stored procedure for each and every query or having some queries hard coded? I had some projects where I found myself with a great deal of stored procedures just because of that...
Thanks
392
One of the biggest drawbacks of stored procedures is that it is extremely difficult to tell which parts of a system use them and which not.
where as stored procedure is compiled when it is submitted for the first time & this compiled content is stored in something called procedure cache,for subsequent calls no compilation,just execution & hence better performance than query.
Conclusion. Overall, stored procedures outperform dynamic SQL. They are faster, easier to maintain, and require less network traffic. The rule of thumb would suggest using stored procedures in scenarios where you don't have to modify queries, and those queries are not very complex.
query and stored procedure do the same thing but the difference is that a query should be compiled everytime the query is executed,while the stored procedure is in compiled form when executed first time.
There are a few options cited for stored procedures:
Personally, I rarely use sprocs. Raw SQL is much more flexible - and sensible c# code can write appropriate SQL dynamically. You can do that inside SQL too, but it isn't a good fit. You also get to use tools like ORMs. More importantly, it avoids a huge deployment problem: the chicken and egg sproc change. If the sproc change is major, you can't deploy the sproc without breaking the callers, and you can't update the callers without updating the sproc. And you have multiple servers calling a single central db. This means that you need to carefully coordinate changes such that both ends are happy. If the query is in the c# this simply isn't a problem: as you update each individual server, it is by definition using the version of the query it expects.
Basically, I'm a huge fan of direct parameterized SQL these days.
121
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
