Coffeehouse Thread

14 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Do you still use stored procedures?

Back to Forum: Coffeehouse
  • User profile image
    vesuvius

    If you are a database oriented developer, there is a myriad of choice nowadays. Do you still use stored procedures or do you see Linq and the ADO.NET Entity Framework as they way to go?

    Yes, it is true that one can access stored procedures from both Linq and EF, but do you prefer the flexibility they give as a "trade off" over supposed performance and or code separation?

  • User profile image
    blowdart

    Funny that conversation is going on in the subtext mailing list right now, along with what ORM, what DI and the move to MVC.

    However for me stored procs and view encapsulate the underlying tables. Something that's a basic tenant of OOP and I have no idea why suddenly we can ignore that for database access.

  • User profile image
    wisemx

    I find them usefull but not as much as they were with Classic ASP.
    Now I'm using them more locally with triggers in packages that pump to and from remote servers.

  • User profile image
    Dr Herbie

    wisemx said:
    I find them usefull but not as much as they were with Classic ASP.
    Now I'm using them more locally with triggers in packages that pump to and from remote servers.
    Horses for courses.

    All of our 250+ reports use stored procedures to pull the data from the database (into DataSets which are then attached to the reports). So when the customer asks for changes to calculations we can often just supply them with a script to alter the stored proc rather than an entire release of the software.

    For other processes, we might use a stored procedure for bulk updates (we have some annual data updates that need to occur for every customer account in the system, for example).

    For performance, we might consider converting complex data manipulations into stored procedures to reduce the 'shuffling about' of data from the DB server to our app server on a different machine and back again.

    Herbie

  • User profile image
    phreaks

    Yes, I still use Stored Procs for all database centric CRUD operations.

    In some areas Linq comes up short when compared to stored procedures.

    Also, a DBA is unable to review Linq classes for standards and compliance purposes, and auditing can be tricky.

    Personally, I typically rely on Typed DataSets for CRUD and Linq for biz processing of the data.

  • User profile image
    PerfectPhase

    Dr Herbie said:
    wisemx said:
    *snip*
    Horses for courses.

    All of our 250+ reports use stored procedures to pull the data from the database (into DataSets which are then attached to the reports). So when the customer asks for changes to calculations we can often just supply them with a script to alter the stored proc rather than an entire release of the software.

    For other processes, we might use a stored procedure for bulk updates (we have some annual data updates that need to occur for every customer account in the system, for example).

    For performance, we might consider converting complex data manipulations into stored procedures to reduce the 'shuffling about' of data from the DB server to our app server on a different machine and back again.

    Herbie

    I've settled on the middle ground of late, simple  selects/insert/deletes are done via dynamic sql in typed datasets, EF or other ORM.  Anything more than that say something that would need multiple back and forths between the app and DB is wrapped up in a sproc and pushed up to the DB.

    I like the encapsulation of the sprocs, and all our third party extension points are via views and sprocs, but Dynamic-SQL+EF+Linq is so much easier for stuff we have complete control over.

  • User profile image
    TheFormer​BrianLy

    I haven't written new code that uses stored procedures in the last few years. They are a real pain to maintain in an agile / continuous integration / TDD environment. It's much easier to use an ORM tool like LLBLGen or NHibernate and push an updated application . The EF and Linq fall short in places where other ORM tools are stronger (more mature?).

    If a third-party needs access to data then they do that with Web Services. No one gets near the database!

    (If there was a realistic need for data access or stored procedures then I'm happy to look at them individually, but in most cases they are not as necessary as people like to think. The performance and security aspects of stored procedures are overblown.)

    -Brian (an ALT.NETer)

  • User profile image
    figuerres

    I have Linq calling Procs that do more complex things that a Proc as always done for me.
    I write a Proc when I can see that it is the better thing to do for any of a number of reasons.

    To me it's a clear case of "user the tool that fits the problem"

    so if I nee to write a loop over a table and update some other table and I do not need per row data from the outside then that becomes a proc in SQL not in C#

    if I see a bunch of inserts or updates that can all be done serverside w/o passing a ton of stuff that's a proc in SQL also.

    if I have to go back and forth for things that sql does not know then I use C# in whatever form , classic sql commands, Linq or whatever fits the rest of the app.

  • User profile image
    littleguru

    I would say: it depends.

    Speed-wise: no
    Encapsulation-wise: yes

    I still need to figure out how SPs fit into the OOP paradigma that blowdart described... It's having encapsulation but that's everything it has... but that's also what methods in C had, like they encapsulated algorithms.

  • User profile image
    Red5

    As a developer, I want the simplest and highest performing options when using CRUD and display data in web applications.
    We have not started using LINQ yet, but plan to very soon for some of our operations.

    I wonder how accurate this particular article is?
    http://www.codeproject.com/KB/linq/performance_comparisons.aspx

    If it's accurate, then it provides some interesting points on performance.

  • User profile image
    Maddus Mattus

    It all depends on your requirements really.

    I would rather not use them, as it is more work I have to do. And I am a lazy git. But if the DBA demands it, well, then I let them write them.

    Smiley

  • User profile image
    Bas

    I think of the database as a completely separate thing from the application. It contains all the data, and it should protect that data. One way to do it is by having the database decide how people retrieve and manipulate data. I find that to be easiest with stored procedures. It's pretty much the same as having a class decide what it tells other classes about its private data through functions and properties.

    I also find stored procedures convenient from an abstraction perspective. I can alter the entire database diagram and still not have to change a single line in my application, just because the database allows me to GetPerson() instead of select specific columns from a specific table based on specific values in specific columns in other specific tables. My application doesn't care how a database stores its stuff internally; it just wants data about a person. 

    It's a way of decoupling the code from your database implementation. Again, much like the way classes abstract their internal workings through their public interface. I can likewise completely rewrite a class's implementation and it'll still work with all my existing code. It's good OO.

    I personally don't care about the performance implications, because I'm not noticing any.

  • User profile image
    Klaus Enevoldsen

    We only use LINQ for our business applications (so far LINQ-To-SQL, but soon ADO.NET entity framework). Our BI-department still use stored procedures.

    I would love to never write another line of T-SQL ever again!

  • User profile image
    Maddus Mattus

    Klaus Enevoldsen said:

    We only use LINQ for our business applications (so far LINQ-To-SQL, but soon ADO.NET entity framework). Our BI-department still use stored procedures.

    I would love to never write another line of T-SQL ever again!

    How do you do fulltext operations with LINQ, such as CONTAINSTABLE, CONTAINS, FREETEXTTABLE and FREETEXT?

    If LINQ had those, I would be really happy!

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.