Another religious question: LINQ vs stored procedures
-
First of all, thanks very much to everyone who kicked in with thoughts regarding MVC. I'm currently doing some upgrades / installs for VS 2012 since I've been running 2010 so that I can give MVC 4 a go. Since I'm reevaluating technologies, here's something I just don't get - but perhaps I'm missing something. A year or two ago I played with LINQ. Essentially, it's a nice little chunk of technology to move your db queries into your code. My approach has traditionally been to use stored procedures, keeping SQL stuff in the database and procedural code / logic in the application. Why on earth would I want to move my queries into C# code? As long as the params and columns returned don't change, I can make changes all day long in Sql Server and never touch web apps or services. If my db logic is in the code, I have to recompile and deploy the binaries every time I touch something. I know it's the shiny new thing to play with but other than resume enhancement, I just don't see what value it brings to the table. What am I missing?
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
-
First of all, thanks very much to everyone who kicked in with thoughts regarding MVC. I'm currently doing some upgrades / installs for VS 2012 since I've been running 2010 so that I can give MVC 4 a go. Since I'm reevaluating technologies, here's something I just don't get - but perhaps I'm missing something. A year or two ago I played with LINQ. Essentially, it's a nice little chunk of technology to move your db queries into your code. My approach has traditionally been to use stored procedures, keeping SQL stuff in the database and procedural code / logic in the application. Why on earth would I want to move my queries into C# code? As long as the params and columns returned don't change, I can make changes all day long in Sql Server and never touch web apps or services. If my db logic is in the code, I have to recompile and deploy the binaries every time I touch something. I know it's the shiny new thing to play with but other than resume enhancement, I just don't see what value it brings to the table. What am I missing?
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
You're thinking of LINQ as "database only" for one thing. LINQ works on other things, like anything that implements or can be cast to a
IEnumerableor IQueryable. LINQ also lets you build a query, piece by piece, defering execution of the query to when you actually use the would-be-returned data.A guide to posting questions on CodeProject[^]
Dave Kreskowiak -
You're thinking of LINQ as "database only" for one thing. LINQ works on other things, like anything that implements or can be cast to a
IEnumerableor IQueryable. LINQ also lets you build a query, piece by piece, defering execution of the query to when you actually use the would-be-returned data.A guide to posting questions on CodeProject[^]
Dave KreskowiakYou're absolutely right, that was my context. So yes, it would be great to have a structured way of managing data sources that don't live in a relational database. Advantage: LINQ. How about working with a database, though - any advantages there? That's the part I'm just not seeing.
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
-
First of all, thanks very much to everyone who kicked in with thoughts regarding MVC. I'm currently doing some upgrades / installs for VS 2012 since I've been running 2010 so that I can give MVC 4 a go. Since I'm reevaluating technologies, here's something I just don't get - but perhaps I'm missing something. A year or two ago I played with LINQ. Essentially, it's a nice little chunk of technology to move your db queries into your code. My approach has traditionally been to use stored procedures, keeping SQL stuff in the database and procedural code / logic in the application. Why on earth would I want to move my queries into C# code? As long as the params and columns returned don't change, I can make changes all day long in Sql Server and never touch web apps or services. If my db logic is in the code, I have to recompile and deploy the binaries every time I touch something. I know it's the shiny new thing to play with but other than resume enhancement, I just don't see what value it brings to the table. What am I missing?
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
On heavily loaded client/server applications, stored procedures can cause major bottlenecks. I was on the fringe of at least two projects where anything more than simple stored procedures were prohibited for this reason (and they had tests to prove it.)
-
On heavily loaded client/server applications, stored procedures can cause major bottlenecks. I was on the fringe of at least two projects where anything more than simple stored procedures were prohibited for this reason (and they had tests to prove it.)
While I don't doubt what you're saying, the logic escapes me. Either way, you have to hit the database and fetch the data. What's special about a stored procedure that would make it more of a performance bottleneck than retrieving the same data from the code? Seems like the db is serving up the same amount of bytes either way.
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
-
While I don't doubt what you're saying, the logic escapes me. Either way, you have to hit the database and fetch the data. What's special about a stored procedure that would make it more of a performance bottleneck than retrieving the same data from the code? Seems like the db is serving up the same amount of bytes either way.
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
Because stored procedures take computing time and aren't always trivial. Stored procedures aren't just a few lines of code to do, say, a select or simple update trigger, they can be entire programs which may have little to do with setting/retrieving data, but in manipulating that data. So, who should manipulate the date, the client or the server? (The answer is, of course, it depends.)
-
First of all, thanks very much to everyone who kicked in with thoughts regarding MVC. I'm currently doing some upgrades / installs for VS 2012 since I've been running 2010 so that I can give MVC 4 a go. Since I'm reevaluating technologies, here's something I just don't get - but perhaps I'm missing something. A year or two ago I played with LINQ. Essentially, it's a nice little chunk of technology to move your db queries into your code. My approach has traditionally been to use stored procedures, keeping SQL stuff in the database and procedural code / logic in the application. Why on earth would I want to move my queries into C# code? As long as the params and columns returned don't change, I can make changes all day long in Sql Server and never touch web apps or services. If my db logic is in the code, I have to recompile and deploy the binaries every time I touch something. I know it's the shiny new thing to play with but other than resume enhancement, I just don't see what value it brings to the table. What am I missing?
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
-
Because stored procedures take computing time and aren't always trivial. Stored procedures aren't just a few lines of code to do, say, a select or simple update trigger, they can be entire programs which may have little to do with setting/retrieving data, but in manipulating that data. So, who should manipulate the date, the client or the server? (The answer is, of course, it depends.)
Well, if you're writing entire programs, yeah, I get that. If I want procedural code, I want a procedural language. But it still sounds to me like if you're just running queries to fetch data or do crud stuff, there's no benefit whatsoever to moving it into the application code. I get the impression people were hot to move that sort of thing to LINQ only because it was new and shiny.
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
-
I remember them releasing competing db platforms at the same time. LINQ to SQL, LINQ to Entities, Entity Framwork, Apparition Architect, yada, yada. That's one of the reasons I stayed away initially. I thought it was the height of stupidity. In other words, coming from MS, I didn't give it a second thought.
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
-
Well, if you're writing entire programs, yeah, I get that. If I want procedural code, I want a procedural language. But it still sounds to me like if you're just running queries to fetch data or do crud stuff, there's no benefit whatsoever to moving it into the application code. I get the impression people were hot to move that sort of thing to LINQ only because it was new and shiny.
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
Christopher Duncan wrote:
I get the impression people were hot to move that sort of thing to LINQ only because it was new and shiny.
My impression as well since the alternative to stored procedures is just writing normal queries. A few months ago, I used a tool that refactored some code into LINQ. The end result was only slightly shorter and much more difficult to understand than the original code. Plus, the LINQ code sucked in several more assemblies. I backed out the change. (My impression is that most things in software development are about being "new and shiny", which is a major headache when you have to maintain the code for someone who constantly got suckered by this.)
-
Christopher Duncan wrote:
I get the impression people were hot to move that sort of thing to LINQ only because it was new and shiny.
My impression as well since the alternative to stored procedures is just writing normal queries. A few months ago, I used a tool that refactored some code into LINQ. The end result was only slightly shorter and much more difficult to understand than the original code. Plus, the LINQ code sucked in several more assemblies. I backed out the change. (My impression is that most things in software development are about being "new and shiny", which is a major headache when you have to maintain the code for someone who constantly got suckered by this.)
Given that you've been in the game for a long time, I'm glad to hear I'm not the only one who sees this as more resume enhancement / religion than a functional improvement in development. By the way, just popped over to look at your profile. Do you still fool around with film stuff?
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
-
First of all, thanks very much to everyone who kicked in with thoughts regarding MVC. I'm currently doing some upgrades / installs for VS 2012 since I've been running 2010 so that I can give MVC 4 a go. Since I'm reevaluating technologies, here's something I just don't get - but perhaps I'm missing something. A year or two ago I played with LINQ. Essentially, it's a nice little chunk of technology to move your db queries into your code. My approach has traditionally been to use stored procedures, keeping SQL stuff in the database and procedural code / logic in the application. Why on earth would I want to move my queries into C# code? As long as the params and columns returned don't change, I can make changes all day long in Sql Server and never touch web apps or services. If my db logic is in the code, I have to recompile and deploy the binaries every time I touch something. I know it's the shiny new thing to play with but other than resume enhancement, I just don't see what value it brings to the table. What am I missing?
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
I would go with Entity Framework + Stored Procedure. With EF the advantage that we can get is the OR Mapping. Now the question goes to you, If you want to take advantage of the technology to simplify or make it better then go with EF Designer or Code first approach and also if you want to do some code re-factoring and wish to use your existing store procedure, Yes you can do so. Some interesting articles http://msdn.microsoft.com/en-us/data/gg699321.aspx[^] http://www.dotnetcurry.com/ShowArticle.aspx?ID=938[^] Thanks,
Ranjan.D
-
I would go with Entity Framework + Stored Procedure. With EF the advantage that we can get is the OR Mapping. Now the question goes to you, If you want to take advantage of the technology to simplify or make it better then go with EF Designer or Code first approach and also if you want to do some code re-factoring and wish to use your existing store procedure, Yes you can do so. Some interesting articles http://msdn.microsoft.com/en-us/data/gg699321.aspx[^] http://www.dotnetcurry.com/ShowArticle.aspx?ID=938[^] Thanks,
Ranjan.D
Don't mean to be dense, but I'm not really following the advantages here. What benefit do I gain by moving queries out of stored procedures and into compiled code?
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
-
Given that you've been in the game for a long time, I'm glad to hear I'm not the only one who sees this as more resume enhancement / religion than a functional improvement in development. By the way, just popped over to look at your profile. Do you still fool around with film stuff?
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
Christopher Duncan wrote:
Do you still fool around with film stuff?
Not much since the 1990s, when I created several interactive videos for edutainment products (and wrote the software.) Lost money, but had a blast. Since then, my biggest project was editing my daughter's wedding video (I should have done a divorce video to bookend that....) I may get back into it one of these days.
-
I remember them releasing competing db platforms at the same time. LINQ to SQL, LINQ to Entities, Entity Framwork, Apparition Architect, yada, yada. That's one of the reasons I stayed away initially. I thought it was the height of stupidity. In other words, coming from MS, I didn't give it a second thought.
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
Ok, so here a link to a discussion by people who did use it in production: LINQ Discussion[^] "with my experience with EF and Linq, If you are developing an application for less than 10-20 users and not expecting any huge data, go with EF and LINQ Otherwise, Never ever use LINQ and EF. I never saw many developers who care about performance during development. So don't adopt architecture that cost you more in the long run. "
-
First of all, thanks very much to everyone who kicked in with thoughts regarding MVC. I'm currently doing some upgrades / installs for VS 2012 since I've been running 2010 so that I can give MVC 4 a go. Since I'm reevaluating technologies, here's something I just don't get - but perhaps I'm missing something. A year or two ago I played with LINQ. Essentially, it's a nice little chunk of technology to move your db queries into your code. My approach has traditionally been to use stored procedures, keeping SQL stuff in the database and procedural code / logic in the application. Why on earth would I want to move my queries into C# code? As long as the params and columns returned don't change, I can make changes all day long in Sql Server and never touch web apps or services. If my db logic is in the code, I have to recompile and deploy the binaries every time I touch something. I know it's the shiny new thing to play with but other than resume enhancement, I just don't see what value it brings to the table. What am I missing?
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
Following is my comparision between Entity Framework (ORM that uses LINQ) and Stored Procedures. Stored Procedure Pros: Much faster performance over EF/LINQ, Could be updated without recompiling the code based since it's in the database, better separation of logic, you could utilize your DBAs, Works even if the database structure / design is poor, Much better security. Cons: Slow development process, unnecessary / repeated coding, No flexibility - works on only one database (I mean someday your management/client wants to move to MySQL from SQL Server, you will have to rewrite everything). Need extensive knowledge of database (I mean just knowing front-end language doesn't work). Maintain two separate things. Unit Testing nightmare. Entity Framework Pros: Crazy fast development time (you could skip one or two phases), Querying is very easy, Works even if you know only Front-End language, Flexible - you can change underlying database without changing your code base, Start coding without planning everything in advanced (for SP you need to create it first before you do anything), Only one code base to maintain, Code-First migrations makes seamless database changes (Just rebuilt the solution and push it, Magic!), If you need performance boost at particular scenario you can still use stored procedure and call it as a method. Cons: Requires almost perfect database structure/design (all the primary keys, foreign keys, constraints, normalized tables etc.), performance is not as good as SP (but you can use SP in that case, or buy a better hardware, or implement caching), your DBAs are now idle, Security is not as good as SP (Mostly any developer can do anything, in case of SP you could restrict the access of the SP and Tables to certain users), every minor changes in database call requires complete recompilation of code base. Also once you know LINQ, you can use it against XML, CSV and many other data structures. I have used EF in our latest product and it works great, few places where the performance was affected due to recursive call to methods, I changed it to stored procedures and now it's fine. I really like the abstraction and flexibility ORM offers. And now I only occasionally open SQL Server Management Studio, one less window to worry about. :)
Remind Me This - Manage, Collaborate and Execute your Project in the Cloud
-
Don't mean to be dense, but I'm not really following the advantages here. What benefit do I gain by moving queries out of stored procedures and into compiled code?
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
Quote:
What benefit do I gain by moving queries out of stored procedures and into compiled code?
I see two different set of peoples arguing about the Usage , Performance, Benefits of Stored Procedures , it's maintainability etc. I'm fine with either way. I don't know how you really want to go with, I say it depends on how we are deciding to go with. That's the reason I said , The Entity Framework doesn't really restricts the usage of Stored Procs , You can still make a call but what you get with EF is the result set as object. Is it good :) I think if you are planning to move out the queries out of Stored Proc, you will end up in writing tons on code, though it's compiled I don't think there would be a great benefit. Some times you might even end up with performance issues. I have seen several hundreds to thousands lines of stored procedures, If some one tells me to move them out of it to EF compiled queries , I would question why I'm doing this ? If you are working on some new products and wish to tryout EF with mix and match SP's it's good. But I don't think it's a good idea to perform code re-factoring for the existing ones. Thanks,
Ranjan.D
-
Don't mean to be dense, but I'm not really following the advantages here. What benefit do I gain by moving queries out of stored procedures and into compiled code?
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
It's the principle of keeping the code in one place, as opposed to keeping it in the right place.
Be excellent to each other. And... PARTY ON, DUDES! Abraham Lincoln
-
It's the principle of keeping the code in one place, as opposed to keeping it in the right place.
Be excellent to each other. And... PARTY ON, DUDES! Abraham Lincoln
-
Christopher Duncan wrote:
Do you still fool around with film stuff?
Not much since the 1990s, when I created several interactive videos for edutainment products (and wrote the software.) Lost money, but had a blast. Since then, my biggest project was editing my daughter's wedding video (I should have done a divorce video to bookend that....) I may get back into it one of these days.
A daughter's wedding is a project in and of itself. I figure the insanity of a major and largely unrehearsed production put on by amateurs is that if you still want to be married by the time it's all done, you should get married. Apparently from your comment that comes with some caveats. I'm enjoying the video stuff I've been playing with, but monetizing film, or video of any sort for that matter, is about as easy as winning the lottery. I'll do more to support my own creative work as it's just another medium in which to communicate, but it would be nice if there were a way to make a buck at it in the process.
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World