Stored Procs: Use them or not?
-
@Jaime said:
The data comes from a database. SQL is the standard way of getting data from a database. Are you suggesting you have to use stored procedures? If you agree that you don't have to use stored procedures, then why use them? In my opinion, sometimes they are a good idea and sometimes they are 100% useless.
I see what you mean. Stored procedures don't always help with performance. In the world I am currently in, with a business layer in compiled VB6 DLLs (I know, I know, it wasn't my choice), having stored procedures deal with getting data from the database makes things easier to maintain. If we discover that the logic of a SQL is wrong, we don't have to go into the VB6 code, change the SQL, compile, and distribute to our customer base (requiring downtime). We just update the stored procedure. I also come from a world where a lot of our performance hits come from SQL statements with multiple inner and outer joins.
[QUOTE]To your original question, using an ORM that creates SQL on the fly and using stored procedures would be doing the same job twice.[/QUOTE]
Another part of my problem was that I wasn't considering the fact that the original poster was talking about using an ORM tool that will generate SQL for you. My biggest issue with ORM though (with my relatively little experience using it) is you have to trust that the ORM is generating the most efficient possible SQL.
For example, you have a table called Student, and that student has a Counselor (some id field), and that Counselor has his or her name stored in a Staff table.
If you want to load a list of students, you aren't necessarily going to want the data for the counselor. So the ORM can use "lazy loading" to only get the data it needs. BUT, if it turns out you DO need the counselor's name (say you are displaying a list of students and their counselors), the ORM can end up running SQL after SQL on the Staff table instead of running one SQL up front. So that can be a pitfall of using an ORM tool. But I suppose this concept is true of anything. You have to know what your tools are doing in order to use them most effectively.
-
@blakeyrat said:
And of course, above all that, using sprocs and a web service gatekeeper aren't (necessarily) mutually-exclusive either.
A web service is actually an area where I am more likely to use a stored procedure. I am likely to forget about some pissant web service next year when I start messing around with its tables or questioning their need to exist. Having a stored procedure show up in a dependency trace will be a life saver. Comments in the stored procedures can point me to the ultimate consumers as well.Dynamic SQL (which I also use) would not provide this alone without depending on documentation being up-to-date. As long as I have one stored procedure referencing every table on which my application depends, I'm happy. For various reasons, I put the important or complex stuff in the DB, and the short, insignificant stuff (or the super-dynamic stuff that is more clearly defined in code than in a gigantic stored procedure) goes dynamic.
-
@belgariontheking said:
@hoodaticus said:
LOL!If you can't find even one good reason a stored procedure should be used in any situation, then you are an idiot. How is that for open-minded? Reply now.
You, sir, are nothing more than a- ALL HAIL THE HYPNOTOAD!
-
@hoodaticus said:
You're looking at it from the persective of someone who has decided ahead of time that stored procedures are the only way to solve this problem. In a proper web service based data access layer there isn't any "pissant web services" anywhere. There is only one instance of the web service. All database requests go through it, so you won't lose track of it. You know that all database requests go through it because no other account even has rights to log into the database server. You get to use Visual Studio's dependency tracking instead of SQL Server's, which is a huge plus. If you drop and recreate underlying objects in SQL Server, you won't break procedures, but you will sometimes cause sp_depends to miss some dependencies.@blakeyrat said:
And of course, above all that, using sprocs and a web service gatekeeper aren't (necessarily) mutually-exclusive either.
A web service is actually an area where I am more likely to use a stored procedure. I am likely to forget about some pissant web service next year when I start messing around with its tables or questioning their need to exist. Having a stored procedure show up in a dependency trace will be a life saver. Comments in the stored procedures can point me to the ultimate consumers as well.Dynamic SQL (which I also use) would not provide this alone without depending on documentation being up-to-date. As long as I have one stored procedure referencing every table on which my application depends, I'm happy. For various reasons, I put the important or complex stuff in the DB, and the short, insignificant stuff (or the super-dynamic stuff that is more clearly defined in code than in a gigantic stored procedure) goes dynamic.
-
Best to have more than just a hammer in your toolbelt.
There is a place for ORM, and a place for Stored Procs. There is even a place for, dare I say it... ad hoc queries. There. I said it.
-
I've often heard the saying:
If all you have is a hammer, everything looks like a nail.
However, sometimes I feel like a more appropriate saying for IT should be:
If all you have are nails, any one of them will be examined and claimed sufficiently unique in order to justify wasting time and resources designing and constructing a fashionably new hammer which you can claim is "better" and write magazine articles about and invent acronyms for its components and sell it to your clients for twice what the old hammer cost them. Oh, and you can tell everyone that the old hammer now officially "sucks" and should be replaced, because if they don't then they will be "old fuddy duddies" who are "living in the past."
Not sure if it'll catch on though.
-
@boog said:
However, sometimes I feel like a more appropriate saying for IT should be:
If all you have are nails, any one of them will be examined and claimed sufficiently unique in order to justify wasting time and resources designing and constructing a fashionably new hammer which you can claim is "better" and write magazine articles about and invent acronyms for its components and sell it to your clients for twice what the old hammer cost them. Oh, and you can tell everyone that the old hammer now officially "sucks" and should be replaced, because if they don't then they will be "old fuddy duddies" who are "living in the past."
Not sure if it'll catch on though.
That's beautiful. I'm going to so start using that...
-
@boog said:
I've often heard the saying:
If all you have is a hammer, everything looks like a nail.
However, sometimes I feel like a more appropriate saying for IT should be:
If all you have are nails, any one of them will be examined and claimed sufficiently unique in order to justify wasting time and resources designing and constructing a fashionably new hammer which you can claim is "better" and write magazine articles about and invent acronyms for its components and sell it to your clients for twice what the old hammer cost them. Oh, and you can tell everyone that the old hammer now officially "sucks" and should be replaced, because if they don't then they will be "old fuddy duddies" who are "living in the past."
Not sure if it'll catch on though.
So you're calling ORM the new hammer!
-
-
@boog said:
@Nagesh said:
So you're calling ORM the new hammer!
Just remember, you said it first.
You remember, that you influenced my thought.
