|
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
|
|
|
|
|
Actually, I'm starting a couple of new projects, but I don't see trying it out unless there are distinct and tangible benefits. Looks like it would be cool for non database stuff, but for a relational db, I'm not hearing anything it does for me that I don't get out of stored procs.
|
|
|
|
|
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
|
|
|
|
|
Well said. 
|
|
|
|
|
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.
|
|
|
|
|
Thanks for the in depth thoughts, man.
Rutvik Dave wrote: Also once you know LINQ, you can use it against XML, CSV and many other data
structures.
That seems like one of the best uses for it.
|
|
|
|
|
Christopher Duncan wrote: Why on earth would I want to move my queries into C# code?
No idea.
After all why would anyone want a database layer in their application when it would obviously make more sense to scatter it throughout the business layer in exactly each spot where it is used?
Christopher Duncan wrote: What am I missing?
Being a bit sarcastic lets suppose that you are less than a DB guru when it comes to your database. And no one else is either. And something is wrong with your database. You don't even know what.
So you hire a DB guru. And they fix it.
If you managed a reasonable business interface with your store procs you will have no or minimal changes to your application code. Without you will be refactoring everything.
modified 18-Oct-13 19:06pm.
|
|
|
|
|
jschell wrote: After all why would anyone want a database layer in their application
You're making the assumption that I was stipulating a 2 tier app. As I told Marc, even if you have a db layer, ultimatey you either write your queries in a compiled language or you put them in sql procs in your db. I'm not seeing the benefit of the former.
jschell wrote: Being a bit sarcastic lets suppose that you are less than a DB guru when it comes to your database. And no one else is either. And something is wrong with your database. You don't even know what.
Well, since you're invoking sarcasm, I'd say that if your team is that inept, perhaps you should all just step away from the keyboard. Perhaps a career change might even be in order.
Seriously, though, incompetence is not a reason to choose an architecture, although tool manufacturers make good money trying to make programming easy for those who should never be attempting it in the first place.
As for refactoring, if you make major changes to your database schema, you will absolutely be refactoring your code, whether it's LINQ or ADO. It's simply a matter of where - inside or outside of your business objects. Perhaps both. And as an aside, you don't need LINQ to create a collection of absracted business objects. We've been doing that since the days of DOS / C++.
|
|
|
|
|
Christopher Duncan wrote: You're making the assumption that I was stipulating a 2 tier app
No I wasn't. When I said "layer" I meant it in many ways but specifically that one would keep their database code in one location rather than scattering it throughout the rest of the application. The reason one does that is to make maintenance easier when changes to the database code is needed. And the same thing can be applied to using store procs in that they provide a disconnected api in the database.
Christopher Duncan wrote: Well, since you're invoking sarcasm
Unfortunately I said that incorrectly - the first part of my response was intended to be sarcastic where the second half wasn't.
Christopher Duncan wrote: I'd say that if your team is that inept,
There is of course a difference between "team" and "individual". Allowing all team members to hack at the database without review is probably a process failure, and one that I have seen in a company where performance and big data was a requirement. That would be a 'team' failure. An individual might also fail as well.
And unless one is specifically focused on a database it might still be the case that pulling in a consultant, with specific database experience, can be helpful in any company that does do large amounts of data.
Christopher Duncan wrote: As for refactoring, if you make major changes to your database schema
Probably but, as I believe I mentioned, with layering it is less likely that the entire code base needs to be changed versus just parts. And one might still be fairly competent at creating the interface API design while still failing at a database side solution that meets performance goals. So procs could provide that disconnect.
Christopher Duncan wrote: And as an aside, you don't need LINQ to create a collection of absracted
business objects. We've been doing that since the days of DOS / C++.
With about 20 years of experience specifically related to creating services and implementing persistent storage solutions in a variety of languages and database solutions I am aware of what they do.
|
|
|
|
|
I think we agree more than we disagree about this stuff. One of the scenarios someone brought up for doing queries in the code was the scenario where you work at a big enough company to have DBAs, procedures and politics, all of which would slow you down (nobody cares that you couldn't meet the deadline because the DBAs were stonewalling - you take the hit anyway). In that case, a reality check says you do what you gotta do.
And I didn't really take the sarcasm seriously. I figured you were just yanking my chain a bit, so I thought I'd yank back.
|
|
|
|
|
I have not and never intend to use linq to the database but then new and shiny does not attract me. Linq against iEnumerable is just excellent.
EF is a nightmare for new devs, sure it is easy and very quick developing the app but they have no depth in their knowledge of the database. Take a look at some of the questions in Q&A, they have no idea how a database works.
I hate black box software, if it breaks (and it does) you are screwed. EF being in it's 4th or 5th version is much more stable but is still a black box.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yeah, I've been reading an MVC book today and Linq against IEnumerable sounds extremely handy.
|
|
|
|
|
They're not mutually exclusive. I use neither. Old tried-and-true still rules.
Christopher Duncan wrote: SQL stuff in the database
The database is for data; code belongs in a code base (preferably a library).
Christopher Duncan wrote: move my queries into C# code
Because they're not there yet, but should be? And stored procedures still leave SQL in the program anyway in the form of the EXECUTE statements and parameters, etc.
Christopher Duncan wrote: I can make changes all day long in Sql Server and never touch web apps or
services
That's a baaaaad thing. Your code relies on the procedures doing a particular thing, but the procedure could be changed to do something completely different.
Or a table could be changed in a way that breaks the procedure.
And besides you'd still have to update the EXECUTE statements if you add or remove parameters.
Christopher Duncan wrote: If my db logic is in the code, I have to recompile and deploy the binaries every
time I touch something
Exactly; that's a gooood thing.
Christopher Duncan wrote: 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
That's about right. There's a guy on my team now who seems to think that every new thing is the bestest way to do things and any (me) who want to stick with tried-and-true need to be enlightened.
|
|
|
|
|
I know what you mean by tried-and-true. To a degree, that's what I've been invoking. Developing web apps with the sql code in the database and the procedural code in the app is the way things have been done for a long time, and it still works just fine.
That said, I try to keep an open mind as new technologies come out so I don't miss anything worthwhile. Of course, I've also been in the biz long enough to know that every time you turn around MS is telling you to throw away what you used to do in favor of this new, shiny thing - which they'll in turn tell you to throw away next year. Consequently, I take all this stuff with a grain of salt.
|
|
|
|
|
While I would say you're not missing anything, my experience is that people who write SP's have really no good practices regarding re-use (I believe another person responded about the lack of re-use with SP's as well.) And the triggers can get huge, spaghetti code, and invariably have undocumented logic and special case handlers, often using hardcoded data values. It's a mess, and when you talk to the people managing the code, they invariably say, we know it's a mess but we've had to make all these patches without any budget to fix the real underlying problems. And why's that? Because that would require changes to application, and the app devs, that were outsourced to write the app to begin with, are no longer around and nobody knows how to make changes in THAT code base without breaking half a dozen other features. So, that describes my real-world experiences, and probably those of many other people.
Also (stepping down from my rant platform), what you're describing in both cases is a 2-tier environment in most cases: app talking directly to the database. I hope I never have to work in a 2-tier environment ever again, but most people don't understand the benefits of a 3-tier environment. First off, you can isolate the application from "the database business." The stuff that manipulates data in the database's representation (aka schema) rather than the application's model, which can be quite different (but I'm not saying anything you don't already know.) But, what I like to do is then choose whether something should be an SP, or the SQL can be auto-generated by the middle tier, or it pulls in additional metadata that supplements the DB schema for further automation, or, as a last resort, the code in the middle tier does some processing of the data in C# (or whatever.)
But it's hard to convince people to take the time to architect a 3-tier system in which most stuff can be handled by metadata (schema + domain-specific declarative stuff) and then leverage either the middle tier or the SP's for the stuff that doesn't fit.
So, the question, in my mind, isn't "what am I missing with SP's vs Linq vs EF?", but "why am I still stuck in a 2-tier development environment?"
Well, that's my somewhat ranty reply.
Marc
|
|
|
|
|
I'm totally with you conceptually, although the rather loose way I phrased things allowed you to make an incorrect assumption.
It doesn't matter if you have 2 tiers or 42 - ultimately, you have to talk to the database. When you do, you can write your queries in compiled code, be it a separate assembly, a web service, etc. or you can write them in stored procedures.
My point is that I don't see the benefit in writing queries in a compiled, procedural language as opposed to letting sql be sql and do what you pay it to do.
On the other hand, it sounds like using LING with non relational db stuff could be very handy indeed.
So, put down that flamethrower, son, and keep your hands where I can see them.
|
|
|
|
|
As someone who has worked as both a DBA and developer I have a pet theory about this development away from stored procedures.
Like you I tend to stick to stored procedures and pass the parameters through from the client.
I have noticed that DBA's have a tendency to lock down as much of the database as possible and in many companies a stored procedure needs to go through a thorough vetting process before it is allowed into the database.
So add to the project lifecycle the client and database qc procedures and you have a bit of a nightmare on your hands - plus if a stored procedure needs to be changed...
As a consequence developers will do what they can to find ways around this spanner in the works of their project.
So the developers decide to do away with stored procedures and do the heavy lifting at the client end because they can navigate around the whole DBA qc environment and consequently get their projects completed more quickly.
This is my pet theory based on experience in small and very large companies.
I tend to think that if a stored procedure is written well and the correct indexes and partitions are created on database tables there should be no real problem with data access bottlenecks.
The alternative is to do all this work on the client which is faster, development wise, and requires less database knowledge.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
I think your assessment is spot on. Since I tend to write about life in the real world and how to cope with all those pesky humans, this fits right in with my own perceptions as well.
Add the shiny factor to this and you have a powerful force for change, whether it's warranted or not.
|
|
|
|
|
GuyThiebaut wrote: do all this work on the client which is faster, development wise, and requires
less database knowledge.
The deuce you say.
|
|
|
|
|
I don't mean that everyone who uses the client side option knows less about databases.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
IMHO
If you are / have access to competent DB developers (i.e. you can write or have written good SQL stored procedures, can debug them and, if necessary, measure and tweak performance) then stored procedures are the way to go.
If you are scared of SQL, or are crap at it, have no idea what is efficient or not, can manage a quick select or insert butr quake at the sight of anything more complex, then you might be more comfortable writing C# or VB rather than SQL and let LINQ to SQL manage the SQL code for you.
I am with you on the SP front - I much prefer the separation of concerns - I can have my .Net devs write against what is to essentially an interface, and my DB developers concentrate on the DB.
IF a SP is inefficient, I can tinker with it, and test it independently of any .Net code, and redeploy it without the need to re-deploy any client side code.
With well constructed SPs I can even significantly change the underlying DB structure without worrying about changing any deployed .NET code.
And if it is more efficient to mangle the data in the application, I can still have an SP return a record set and use LINQ to POCO to do the mangling.
My first use of SPs was entirely due to security of the DB - access to which was restricted entirely to stored procedures, so even giving someone access to the DB server still only gave them access to the SPs - they couldn't even see the tables - let alone try to update them.
(AM I starting to sound religious enough?)
MVVM # - I did it My Way
___________________________________________
Man, you're a god. - walterhevedeich 26/05/2011
.\\axxx
(That's an 'M')
|
|
|
|
|
Yep, that's largely the way I've seen things as well.
|
|
|
|
|
FYI MacBook Air owners:
Quote: Apple has determined that certain 64GB and 128GB flash storage drives used in the previous generation of MacBook Air systems may fail. These systems were sold between June 2012 through June 2013.
MacBook Air Flash Storage Drive Replacement Program[^]
Kevin Priddle
Editor and Special Projects Manager | CodeProject & Developer Media
|
|
|
|
|
Thanks, will look into that tonight.
I'd rather be phishing!
|
|
|
|
|
Since Windows 7, when you run a disk cleanup and select Windows Update Cleanup (mostly after the service pack installation). It takes at-least an hour to cleanup. I can download and install the same update 10 times in that time.
Windows 8.1 just updated with today's launch. and I performed disk cleanup to free up 450 MB of disk space on 5 different computers, and it took 1.0 - 2.5 hours to cleanup with > 70% CPU utilization. 
When you run disk cleanup, it scans your computer and then tells you how much space it is going to free. So I am guessing it creates some king of list. So why it takes to long to loop through the list and remove the files.
When they are going to fix this. 
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
