Stored Procs: Use them or not?



  • Do you support ORM in favor using ancient and outdated tech like stored procedures?

    I think stored procedures had their place once upon a time, but as things become more and more dynamic with the turn of this decade, it is clear that you must learn ORM technologies to stay on top of everyone.

    Discuss now.



  • Not at all a loaded question!!



  • So you think we can have a serious discussion about this topic when you start it all with an obviously loaded question and subjective comment like that?

    Interesting...



  • @boog said:

    So you think we can have a serious discussion about this topic when you start it all with an obviously loaded question and subjective comment like that?

    Interesting...

    You tell me. I think we can. I have a completely open mind.


  • Discourse touched me in a no-no place

    @Nagesh said:

    I have a completely open mind.
    It's not showing in your opening post.



  • Well, two points:

    1) Using sprocs and using a ORM are not mutually-exclusive
    2) I personally like the finer-grained permissions you gain by using sprocs. They can serve as kind of a "gatekeeper": nobody can get into this table without going through me first!



  • 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.



  • @Nagesh said:

    I have a completely open mind.
    Forgive my skepticism.

    But if you are being sincere, then please start off the discussion by proposing an objective form of measurement by which to compare the two options.  Without that, there is no reason for me to continue any further.

    Keep in mind that I do reserve the right to dispute your chosen metric, as long as I can adequately explain why I disagree.  You of course have the right to argue your case as well.



  • @blakeyrat said:

    Well, two points:

    1) Using sprocs and using a ORM are not mutually-exclusive
    2) I personally like the finer-grained permissions you gain by using sprocs. They can serve as kind of a "gatekeeper": nobody can get into this table without going through me first!

    I think that's a very good point.



  • @hoodaticus said:

    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.

    Job Security for Old Fuddy Duddy's is one good reason.



  • Job security?  Do explain how the use of stored procedures constitutes job security.

    This should be hilarious interesting.



  • @boog said:

    Job security?  Do explain how the use of stored procedures constitutes job security.

    This should be hilarious interesting.

    Because Nagesh doesn't know how to write them, he will never get a real database job.  This translates into job security for everyone else.

    I have a proposed, objective standard though: ease of tracking dependencies between tables and the SQL queries that call them.  Or in other words, "if I delete this table, what would be affected?".  Just try that with dynamic SQL scattered about your codebase.



  • @hoodaticus said:

    @boog said:

    Job security?  Do explain how the use of stored procedures constitutes job security.

    This should be hilarious interesting.

    Because Nagesh doesn't know how to write them, he will never get a real database job.  This translates into job security for everyone else.

    I have a proposed, objective standard though: ease of tracking dependencies between tables and the SQL queries that call them.  Or in other words, "if I delete this table, what would be affected?".  Just try that with dynamic SQL scattered about your codebase.

    You should do your business application in such a way, that you rely on the database as less as possible and on your middle layers as much as possible.

    Stored procedures can be used for certain things like auditing the database objects. You can call the stored procedure from your table level or row level triggers, depending on the auditing capability you want to incorporate. This work is usually done by the DBA.



  • Some database administrators are rightly paranoid and would prefer to have some application logic only go through stored procedures. Then they can easily set permissions on a task-by-task basis, and without dynamic SQL queries everywhere their databases are protected from all the incompetent, retarded programmers who don't know the first thing about web application security that may be working at their company. Of course this is assuming the DB admin is competent.



  • And when other applications in the enterprise want access to your data?  If your data is useful, it doesn't usually take long for other groups within the enterprise to ask for access to your data.  If all of the database definitions only exist in the ORM application layer, that data is now inaccessible except through the application.

    I just got off (escaped?) a project full of ORM fans who insisted that only old obsolete "fuddy duddies" used stored procedures.  Oddly, the ORM advocates spent over a year struggling to get their ORM based application to work, and it was plagued with data integrity and performance problems.  Right before I left that project, I finally got approval to use a stored procedure based solution.  It took 3 weeks with testing, and no performance or integrity problems.  Strangely enough, the ORM advocates always defended the ORM problems, by claiming that, "just one more fix", would make everything work great.  The ORM advocates claimed that it was all about saving developer time, it seems to me it cost substantially more developer time to make the ORM stuff work, than a far simplier raw SQL/Stored Procedure solution required.  And with the cleaner seperation between the application and the database, when other groups asked for access to the data, it was no big deal, to grant database tier access.

    I keep hearing about the "impediance mismatch" between relational databases, and objects.  Object Oriented methods are a tool to be used to manage complexity.  But it is not the answer to every problem.  Functional and procedural methods have their place too.  This "impediance mismatch", only exists, because developers insist on using Objects in a way that creates more complexity.  Redefine the solution to use the tools where they best fit, and the impediance mismatch goes away.



  • @Nagesh said:

    You should do your business application in such a way, that you rely on the database as less as possible and on your middle layers as much as possible.

    Is "how much something relies on the database" your suggested form of measurement, or is this unrelated to my request?  If it's how you want to measure the two options (ORM vs. stored procs), then I have a few issues with it:

    1. Could you be more specific?  "How much something relies on the database" is not a clear measurement, as it can be interpreted differently in separate situations.  Please describe a method that leads to an objective measurement, ideally one that is numeric.
    2. This convention (as I cannot yet call it a measurement) seems awfully biased; did you just come up with a convention that favored your preferred option?  Why do you believe that "how much something relies on the database" is the dominant feature by which to compare two options?
    3. Does this convention even relate to the problems that ORM and stored procs were meant to solve?  It seems that would be a better place to start.

    If your comment above was unrelated to my request, then you can safely ignore this comment and know that I'm still waiting for a method by which to measure ORM vs. stored procs for comparison.

    Discuss now.



  • @hrothgar said:

    Strangely enough, the ORM advocates always defended the ORM problems, by claiming that, "just one more fix", would make everything work great.  The ORM advocates claimed that it was all about saving developer time, it seems to me it cost substantially more developer time to make the ORM stuff work, than a far simplier raw SQL/Stored Procedure solution required.

    I think, and I don't really have a lot of good evidence for this but bear with me, that a sizable number of programmers:

    1) Don't understand how relational databases work, or any of the underlying theories
    2) Are afraid of databases

    Or are some combination of the two. Look at today's homepage story, for example. That's not laziness at work-- the database query would have been significantly faster to develop and test-- that's pure "fear/ignorance of the relational data model."



  • @blakeyrat said:

    I think, and I don't really have a lot of good evidence for this but bear with me, that a sizable number of programmers:

    1) Don't understand how relational databases work, or any of the underlying theories
    2) Are afraid of databases

     

    I think both of those are very true. I majored in Computer Science in college and I never had to take a database course for my major, the only reason I know anything about them is because I had a database minor. I think the MIS students had to take the database courses, but I doubt that turns out well since our MIS curriculum is really light on programming.



  • @blakeyrat said:

    @hrothgar said:
    Strangely enough, the ORM advocates always defended the ORM problems, by claiming that, "just one more fix", would make everything work great.  The ORM advocates claimed that it was all about saving developer time, it seems to me it cost substantially more developer time to make the ORM stuff work, than a far simplier raw SQL/Stored Procedure solution required.

    I think, and I don't really have a lot of good evidence for this but bear with me, that a sizable number of programmers:

    1) Don't understand how relational databases work, or any of the underlying theories
    2) Are afraid of databases

    Or are some combination of the two. Look at today's homepage story, for example. That's not laziness at work-- the database query would have been significantly faster to develop and test-- that's pure "fear/ignorance of the relational data model."

    This.

    It's surprising how many programmers seem to believe that "working with databases" is not their job.  Honestly I don't know what's so scary about it.  However, I would like to add the following possibility, as it seems pretty common too:

    3) Think they will change databases often enough to justify moving all database-related tasks into the application's codebase.

    And I would say that if you are changing databases that often, you're doing something wrong.



  • @hrothgar said:

    And when other applications in the enterprise want access to your data?  If your data is useful, it doesn't usually take long for other groups within the enterprise to ask for access to your data.  If all of the database definitions only exist in the ORM application layer, that data is now inaccessible except through the application.

    I just got off (escaped?) a project full of ORM fans who insisted that only old obsolete "fuddy duddies" used stored procedures.  Oddly, the ORM advocates spent over a year struggling to get their ORM based application to work, and it was plagued with data integrity and performance problems.  Right before I left that project, I finally got approval to use a stored procedure based solution.  It took 3 weeks with testing, and no performance or integrity problems.  Strangely enough, the ORM advocates always defended the ORM problems, by claiming that, "just one more fix", would make everything work great.  The ORM advocates claimed that it was all about saving developer time, it seems to me it cost substantially more developer time to make the ORM stuff work, than a far simplier raw SQL/Stored Procedure solution required.  And with the cleaner seperation between the application and the database, when other groups asked for access to the data, it was no big deal, to grant database tier access.

    I keep hearing about the "impediance mismatch" between relational databases, and objects.  Object Oriented methods are a tool to be used to manage complexity.  But it is not the answer to every problem.  Functional and procedural methods have their place too.  This "impediance mismatch", only exists, because developers insist on using Objects in a way that creates more complexity.  Redefine the solution to use the tools where they best fit, and the impediance mismatch goes away.

    These are good points.

     

    However, the group you mentioned seems like "ORM Wannabes". An expert wouldn't have faced this issue. Though I am no expert, I have the privilege of working with more than one. They have used stored procs in their hey day, but now they are content with ORM tools.



  • @boog said:

    @blakeyrat said:

    @hrothgar said:
    Strangely enough, the ORM advocates always defended the ORM problems, by claiming that, "just one more fix", would make everything work great.  The ORM advocates claimed that it was all about saving developer time, it seems to me it cost substantially more developer time to make the ORM stuff work, than a far simplier raw SQL/Stored Procedure solution required.
    I think, and I don't really have a lot of good evidence for this but bear with me, that a sizable number of programmers:

    1) Don't understand how relational databases work, or any of the underlying theories
    2) Are afraid of databases

    Or are some combination of the two. Look at today's homepage story, for example. That's not laziness at work-- the database query would have been significantly faster to develop and test-- that's pure "fear/ignorance of the relational data model."

    This.

    It's surprising how many programmers seem to believe that "working with databases" is not their job.  Honestly I don't know what's so scary about it.  However, I would like to add the following possibility, as it seems pretty common too:

    3) Think they will change databases often enough to justify moving all database-related tasks into the application's codebase.

    And I would say that if you are changing databases that often, you're doing something wrong.

    If you were working on a product that had to work on DB2, MySQL, Oracle and SQL Server, what would you do?



  • @Nagesh said:

    If you were working on a product that had to work on DB2, MySQL, Oracle and SQL Server, what would you do?

    First of all, no product "has" to work on all of those different DBMSes. You've chosen to, as a selling feature to your clients. (And it's probably a poor choice considering the cost involved... but then again you're probably not in sales and don't have a say in the matter.)

    Secondly, you have two options:
    1) Pick one, and make it your customer's problem to ensure they have an instance of it
    2) Pick one, become a reseller, and ship/install it with your product

    If I were you, I'd pick option 2.



  •  Of course they were ORM wannabes, but they were buzzword compliant wannabes.



  • @hrothgar said:

    I keep hearing about the "impediance mismatch" between relational databases, and objects.  Object Oriented methods are a tool to be used to manage complexity.  But it is not the answer to every problem.  Functional and procedural methods have their place too.  This "impediance mismatch", only exists, because developers insist on using Objects in a way that creates more complexity.  Redefine the solution to use the tools where they best fit, and the impediance mismatch goes away.
    Very well said!



  • @Nagesh said:

    Though I am no expert, I have the privilege of working with more than one. They have used stored procs in their hey day, but now they are content with ORM tools.

    Who cares?



  • @Nagesh said:

    If you were working on a product that had to work on DB2, MySQL, Oracle and SQL Server, what would you do?

    Shoot myself.

    I'm sorry, but if you aren't going to take this discussion seriously, why should I?



  • @hrothgar said:

     Of course they were ORM wannabes, but they were buzzword compliant wannabes.

     

    That made me laugh.



  • @boog said:

    @Nagesh said:

    If you were working on a product that had to work on DB2, MySQL, Oracle and SQL Server, what would you do?

    Shoot myself.

    I'm sorry, but if you aren't going to take this discussion seriously, why should I?

     

    I am sorry, but I am serious. Are you a cynic?



  • @Nagesh said:

    I am sorry, but I am serious. Are you a cynic?
     

    We're all cynics, but that's beside the point.

    For an explanation why he's doubting your seriousness, see blakey's response.



  • @blakeyrat said:

    @Nagesh said:
    If you were working on a product that had to work on DB2, MySQL, Oracle and SQL Server, what would you do?

    First of all, no product "has" to work on all of those different DBMSes. You've chosen to, as a selling feature to your clients. (And it's probably a poor choice considering the cost involved... but then again you're probably not in sales and don't have a say in the matter.)

    Secondly, you have two options:
    1) Pick one, and make it your customer's problem to ensure they have an instance of it
    2) Pick one, become a reseller, and ship/install it with your product

    If I were you, I'd pick option 2.

    This is a good approach, but in either case we have to be ready for anything the customer throws at us. This is the only way we can get more business. We are a backward bending, getting it up our **** kind of vendor. Our clients are all over the world, so we don't know what they are throwing at us.

    Maybe I need to find another job.



  • @Nagesh said:

    This is a good approach, but in either case we have to be ready for anything the customer throws at us.

    No, you don't. You need to define your fucking product and fucking sell it.

    @Nagesh said:

    This is the only way we can get more business.

    Well here's a thought: reduce the insane amount of time you spent writing idiotic and useless compatibility layers, do the 3 days of negotiations to become a reseller of your favorite DBMS (or just ship with PostGRE or something free). Now you have tons of free time you can use to write new features or fix bugs or make a new product or basically do all those things that make your customer happy!

    This is such a false economy it's unbelievable. Does your product manager (or whoever's in charge) not know the cost of things? You could pre-load it onto a server, and sell the client the hardware *and* software (sell it as an appliance, as we say here in the US) for less than the cost of fucking porting your code to four database systems.

    Plus then your customer doesn't have to do jack to install it except plug in a network cable and maybe type in an IP address.

    Plus then you have reasonable ability to remote-in to your product's server when it's having trouble and fixing it remotely.

    Plus your product's database isn't clogging up the client's DB server.

    Plus you're only supporting one DBMS.

    Everybody's happy.

    (BTW, it's no wonder you hate sprocs if you have to work with MySQL. Although I think even that ball of useless shite has working sprocs at this point.)

    @Nagesh said:

    We are a backward bending, getting it up our **** kind of vendor. Our clients are all over the world, so we don't know what they are throwing at us.

    Poor baby.

    @Nagesh said:

    Maybe I need to find another job.

    Or just talk some sense into your management. But the fact that you'd come here and tell us how awful sprocs are when the only reason you hold that opinion is that your management is idiots... The Real WTF!



  • @Nagesh said:

    I am sorry, but I am serious. Are you a cynic?

    There's a difference between cynicism and skepticism.

    And no, you have not taken this discussion seriously.  You have yet to define any metric by which to compare ORM and stored procs, yet you continue to bark about ORM being better.  All you've said so far is your opinion, which is meaningless; nobody cares.

    So until you take this discussion seriously, I refuse to.



  • @blakeyrat said:

    No, you don't. You need to define your fucking product and fucking sell it.
     

    Maybe it's a culture clash thing on how to conduct business in India vs Western Europe/the US.

    I mean, one is clearly the better method, but still, some thing to think about.

    Maybe.



  •  As usual, I agree with everything blakeyrat said.



  • @boog said:

    @Nagesh said:

    If you were working on a product that had to work on DB2, MySQL, Oracle and SQL Server, what would you do?

    Shoot myself.

    I'm sorry, but if you aren't going to take this discussion seriously, why should I?

    boog,

    I am completely serious.



  • @blakeyrat said:

    @Nagesh said:
    This is a good approach, but in either case we have to be ready for anything the customer throws at us.

    No, you don't. You need to define your fucking product and fucking sell it.

    @Nagesh said:

    This is the only way we can get more business.

    Well here's a thought: reduce the insane amount of time you spent writing idiotic and useless compatibility layers, do the 3 days of negotiations to become a reseller of your favorite DBMS (or just ship with PostGRE or something free). Now you have tons of free time you can use to write new features or fix bugs or make a new product or basically do all those things that make your customer happy!

    Selling is not my job. My job is development. If I can develop a product that a team has imagined, I have a job.

    This is such a false economy it's unbelievable. Does your product manager (or whoever's in charge) not know

    the cost of things? You could pre-load it onto a server, and sell the client the hardware and software (sell it as an appliance, as we say here in the US) for less than the cost of fucking porting your code to four database systems.

    I agree with you here. It is definitely more expensive, but it is cheap to get this done over here in Hyderabad than in the US. So they can put more men on the job. Project Manager (PM) like to think that putitng 9 people on one woman will give baby in one month. We all know - not possible, but mythical man month is for eating dal-wada and not for reading.

    Plus then your customer doesn't have to do jack to install it except plug in a network cable and maybe type in an IP address.

    Plus then you have reasonable ability to remote-in to your product's server when it's having trouble and fixing it remotely.

    Plus your product's database isn't clogging up the client's DB server.

    Plus you're only supporting one DBMS.

    Everybody's happy.

    (BTW, it's no wonder you hate sprocs if you have to work with MySQL. Although I think even that ball of useless shite has working sprocs at this point.)

    @Nagesh said:

    We are a backward bending, getting it up our **** kind of vendor. Our clients are all over the world, so we don't know what they are throwing at us.

    Poor baby.

    @Nagesh said:

    Maybe I need to find another job.

    Or just talk some sense into your management. But the fact that you'd come here and tell us how awful sprocs are when the only reason you hold that opinion is that your management is idiots... The Real WTF!

    Maybe you should become our manager. :rolleyethingyemote:



  • @Nagesh said:

    I am completely serious.

    No, you're a troll.

     

    Now I'm being cynical.



  • @boog said:

    @Nagesh said:

    I am completely serious.

    No, you're a troll.

    Now I'm being cynical.

    I am a junior developer. I am no troll. Thanks for the clarification.



  • @Nagesh said:

    I am a junior developer.

    Who cares?



  • @hoodaticus said:

    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!



  • @blakeyrat said:

    (or just ship with PostGRE or something free)

    "Postgres". "Postgres-95" database engine was adapted to do SQL, thus PostgreSQL.



  • @bannedfromcoding said:

    @blakeyrat said:
    (or just ship with PostGRE or something free)

    "Postgres". "Postgres-95" database engine was adapted to do SQL, thus PostgreSQL.

    And yet somehow you knew what I fucking meant! Hmm...

    From now on, in your honor, I'll call it AsswipeSQL.



  • A lot of people abbreviate it by dropping the SQL from the name, hence "Postgre".  It's not a big deal, really.  For me, the problem comes from pronouncing it: "post-grey".

    I did this until I had a chance to work with Postfix's greylisting using Postgrey.  That's when I stopped and just started calling it "post-gres".



  • @blakeyrat said:

    2) Pick one, become a reseller, and ship/install it with your product

    If I were you, I'd pick option 2.

    I'll endorse option 2. The company I am working for now has gone the option 2 route. As a result they have developed a deep level of expertise in the solutions they sell (even though they only offer a narrow range of products), have developed a set of standards that streamline project development, and are the current holders of an industry award for 2011 as being one of the best companies in the business. Throw in being a good place to work in general and I'll take the option 2 route any day!


  • @blakeyrat said:

    Well, two points:

    1) Using sprocs and using a ORM are not mutually-exclusive
    2) I personally like the finer-grained permissions you gain by using sprocs. They can serve as kind of a "gatekeeper": nobody can get into this table without going through me first!

    Stored procedures are not the only viable gatekeeper. You can get the exact same permission granularity by putting the data access code in a web service, regardless of the use of stored procedures. This is more of an argument for putting permissions on the business layer than it is an argument for stored procedures.


  • @Jaime said:

    Stored procedures are not the only viable gatekeeper. You can get the exact same permission granularity by putting the data access code in a web service, regardless of the use of stored procedures. This is more of an argument for putting permissions on the business layer than it is an argument for stored procedures.

    Yeah, but that's goofy... then you have to not put any permissions on the actual database (the web service can do whatever the hell it wants!) and trust that you coded your web service permissions correctly. Sure you could do that, but what would be the point? Your homegrown permissions implementation is undoubtedly more buggy and less tested than the database engine's. I see that as adding a layer which gives almost no benefit. (At least, not database-wise.) The odds of someone breaking into your web service are orders of magnitude higher than the odds someone will crack you database security.

    It's also unfeasible for an app that needs to be easily deployed. Network administrator: "you mean I need an IIS or Apache server to run your product? Nuts to that!"

    And of course, above all that, using sprocs and a web service gatekeeper aren't (necessarily) mutually-exclusive either.



  • @mott555 said:

    Some database administrators are rightly paranoid and would prefer to have some application logic only go through stored procedures. Then they can easily set permissions on a task-by-task basis, and without dynamic SQL queries everywhere their databases are protected from all the incompetent, retarded programmers who don't know the first thing about web application security that may be working at their company. Of course this is assuming the DB admin is competent.
    This is an argument to move responsibility for critical sections of code from "those idiots" to "us". If the teams switched roles, then this argument would be an effective one for the avoidance of stored procedures.

    Also, if you are tying to fix web application security issues by using stored procedures, you are screwed. Of the OWASP top ten, stored procedures are part of the solution to A1, and do not contribute to the solution for the other 9. OWASP even specifically calls out stored procedures for appearing to be effective, but not being a complete solution by themselves. They also list two effective solution strategies for SQL Injection that don't use stored procedures at all.



  • @blakeyrat said:

    @Jaime said:
    Stored procedures are not the only viable gatekeeper. You can get the exact same permission granularity by putting the data access code in a web service, regardless of the use of stored procedures. This is more of an argument for putting permissions on the business layer than it is an argument for stored procedures.

    Yeah, but that's goofy... then you have to not put any permissions on the actual database (the web service can do whatever the hell it wants!) and trust that you coded your web service permissions correctly. Sure you could do that, but what would be the point? Your homegrown permissions implementation is undoubtedly more buggy and less tested than the database engine's. I see that as adding a layer which gives almost no benefit. (At least, not database-wise.) The odds of someone breaking into your web service are orders of magnitude higher than the odds someone will crack you database security.

    It's also unfeasible for an app that needs to be easily deployed. Network administrator: "you mean I need an IIS or Apache server to run your product? Nuts to that!"

    And of course, above all that, using sprocs and a web service gatekeeper aren't (necessarily) mutually-exclusive either.

    This is our standard architecture and it isn't difficult to deploy at all. Back end is SQL Server, with one application account that is in the db_datareader and db_datawriter roles. This account is used as the process identity for the middle tier (but not the front end). The app server is an IIS server, however, since .Net 2.0 it is easy to host web services without installing IIS. Look at SQL Reporting Services in SQL 2008 for an example, it doesn't require IIS. For permissions, we use standard ASP.Net role based security. Coarse grained security is done in Web.Config, finer grained permissions are managed on the web methods themselves. It's not a homegrown permission implementation, we simply choose to use a different established permission system. The web service is not accessible to the outside world, only to the application running on the front end web server (or desktop apps). If we choose to expose some web methods to the outside world, we go through a dedicated reverse-proxy specifically designed for web services (IBM DataPower).

    As for benefits, we get several from this architecture. First, caching is much easier in ASP.Net than it is in SQL. 90% of our requests never make it to the database at all. Second, we get to use a real language. T-SQL is not a great procedural language and the function library is horrific. CLR in the database helps a bit, but it's still more cumbersome than a real language. There's more, but that at least raises the bar above "almost no benefit".

    @blakeyrat said:
    ... (the web service can do whatever the hell it wants!) and trust that you coded your web service permissions correctly.
    How is that different from stored procedures? They can do whatever the hell they want and you have to trust they have permissions applied correctly too.


  • I'm not sure I understand the question. ORM requires storing and retrieving the data from somewhere. ORM is a way of structuring the data. Stored procs are a way of GETTING the data.

    Let's say you use web services to get your data. Maybe you use the oData standard. That data from served by that service still has to come from SOMEWHERE, right? That's where you'd use stored procedures.

    In another thread, you are asking about the NoSQL movement. Isn't that what you are asking here? Or am I missing something in how ORM works?



  • @wydok said:

    That data from served by that service still has to come from SOMEWHERE, right? That's where you'd use stored procedures.
    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.


    To your original question, using an ORM that creates SQL on the fly and using stored procedures would be doing the same job twice. Having an ORM automatically generate stored procedures is more likely to cause more performance problems that not doing so. Some code generators like to produce procedures that look like this:

    CREATE PROC Table1_Get (@Col1 int, @Col2 int)
    AS
    SELECT
      *
    FROM
      Table1
    WHERE
      (@Col1 IS NULL OR Col1 LIKE @Col1)
        AND
      (@Col2 IS NULL OR Col2 LIKE @Col2)
    

    Procedures of this type can cause performance problems if the following series of calls is made:

    EXEC Table1_Get 4, NULL
    EXEC Table1_Get NULL, 12
    

    The first call will compile the proc and likely optimize on an index on Col1. The second call will likely use the pre-generated execution plan and use an index on Col1, even though it will cause a table scan. There are solutions to this problem, but not using stored procedures eliminates the problem at its source. The really interesting thing about this case is that the problem is that the execution plan is cached. Most people like to point to the cached execution plan as the primary advantage of using stored procedures, but in this case that is what caused the performance problem.


Log in to reply