Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage
Using "Stored Procedures" vs. SQL statements within the Applicatin code.
Hi, I'm developing an Application in Visual Studio 2015 - C# that has an SQL database with multiple joined tables. I have the option of creating most, if not all of the SQL queries within the database itself as stored procedures, or within the code of my Application.
My question is - which is a better option? Is it more efficient to create the queries as stored procedures and save them within the database itself, or is it better to keep the query code within the Application code?
My first thought is that calling a stored procedure name like "spGetAllEmployeeNames" to the DB would be less overhead than passing all SQL code to the DB each and every time.
Does anyone have any pros / cons for doing it either way?
Thanks for your guidance,
Fulano
My question is - which is a better option? Is it more efficient to create the queries as stored procedures and save them within the database itself, or is it better to keep the query code within the Application code?
My first thought is that calling a stored procedure name like "spGetAllEmployeeNames" to the DB would be less overhead than passing all SQL code to the DB each and every time.
Does anyone have any pros / cons for doing it either way?
Thanks for your guidance,
Fulano
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
LVL 51
Stored Procedures are generally thought to be the more secure and robust method. They can also lead to better execution times as they don't need (re)compilation at each run.
Stored procedures also help with the separation of responsibilities - especially for code reviews. I go, I make this call, then I do this with the data. Versus trying to parse out the sql at the same time.
A lot of times you end up re-using sql code and there's no need to touch multiple places in your application if you can change the stored proc once.
Execution time and cached results help performance.
Stored procs are usually the way to go IMO.
A lot of times you end up re-using sql code and there's no need to touch multiple places in your application if you can change the stored proc once.
Execution time and cached results help performance.
Stored procs are usually the way to go IMO.
I was the kind of guy to put all the queries in my code. I switch my mind a long time ago for a couple of reasons:
-.net code much easier to read
-SPs can be tested relatively easily without the need to run a full application
-SPs can be fixed without having to redeploy the application
-SPs can also be used with ORM tools like EF
-.net code much easier to read
-SPs can be tested relatively easily without the need to run a full application
-SPs can be fixed without having to redeploy the application
-SPs can also be used with ORM tools like EF
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
one great reason that no one has brought up is that if you use stored procedures, you are free to update your db schema and you could potentially modify your stored procedures and never have to recompile your application.
a downside could be if you have a very bloated organization in which anything that needs to get done has to go through a DB admin and that admin's priorities are not supporting your application.
odds are 99% of the time, stored procedures are the way to go. personally i like to add another layer of abstraction and create a web service that calls my database. so this way your application calls a rest service and it interacts with your database. this way you can add native applications in the future that use the same web service or you can even swap the entire database out for some other data retention technology and all you'd have to do is update the web service.
this separation of concerns also lets you create a web service that returns test data without querying a db leaving you free to develop your application and data layer in parallel.
a downside could be if you have a very bloated organization in which anything that needs to get done has to go through a DB admin and that admin's priorities are not supporting your application.
odds are 99% of the time, stored procedures are the way to go. personally i like to add another layer of abstraction and create a web service that calls my database. so this way your application calls a rest service and it interacts with your database. this way you can add native applications in the future that use the same web service or you can even swap the entire database out for some other data retention technology and all you'd have to do is update the web service.
this separation of concerns also lets you create a web service that returns test data without querying a db leaving you free to develop your application and data layer in parallel.
In addition to the above correct answers having T-SQL in the database is ideal for 'impact analysis', which loosely means 'If I wanted to change/delete/rename table X, where in my code would I also have to change it?'. Searching the database for all instances of X is WAYYYY easier than searching the database plus any number of application / report / ETL package files for any instance of X, in any number of locations they may reside.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2007 Basic 239 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.