Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Using "Stored Procedures" vs. SQL statements within the Applicatin code.

Posted on 2016-08-03
7
Medium Priority
?
94 Views
Last Modified: 2016-08-14
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
0
Comment
Question by:Mr_Fulano
7 Comments
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 400 total points
ID: 41741354
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.
1
 
LVL 41

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 400 total points
ID: 41741373
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.
1
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 600 total points
ID: 41741626
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
1
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Assisted Solution

by:Paweł
Paweł earned 600 total points
ID: 41742440
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.
0
 

Author Closing Comment

by:Mr_Fulano
ID: 41748191
Thank you all. All comments were very helpful.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41749050
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.
0
 

Author Comment

by:Mr_Fulano
ID: 41755524
Thank you S. Jimbo very good comment.

Fulano
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question