{"id":160,"date":"2009-05-13T19:17:29","date_gmt":"2009-05-13T18:17:29","guid":{"rendered":"http:\/\/blog.eweibel.net\/?p=160"},"modified":"2009-05-13T20:01:49","modified_gmt":"2009-05-13T19:01:49","slug":"when-to-use-stored-procedures","status":"publish","type":"post","link":"https:\/\/blog.eweibel.net\/?p=160","title":{"rendered":"When to use stored procedures"},"content":{"rendered":"<p>Recently I discussed with a colleague when to use stored procedures. As exptected it was quite a religious conversation. A few days later I found the following screencast: <a href=\"http:\/\/www.msteched.com\/online\/view.aspx?tid=1fd917e2-e451-44c2-b515-c778325846fe\"><br \/>\nThe Pros and Cons of Stored Procedures<\/a><\/p>\n<p>Based on the discussion and the screencast I tried to summarize my Pros and Cons: <\/p>\n<p><strong>Pros<\/strong><\/p>\n<ul>\n<li>Layering<\/li>\n<li>Low-level-Security<\/li>\n<li>Protect queries from changing data-access technologies (jdbc, hibernate, toplink, dbcommands, linq to sql, entity framework, nhibernate, etc&#8230;)<\/li>\n<li>Ivory-Tower-Feature: changing database during project does nearly never happen<\/li>\n<li>Coarse-grained Interface approach<\/li>\n<li>Performance<\/li>\n<li>DBA could tune the queries in a procedure<\/li>\n<li>From an integration view, logic could be reuse by several applications<\/li>\n<\/ul>\n<p><strong>Cons<\/strong><\/p>\n<ul>\n<li>If all logic is in the database, there is a danger that the communication is too chatty (to many calls to the database-server), what is too expensive from a performance perspective<\/li>\n<li>Security-Chaos (Too much security-rules on different layers)<\/li>\n<li>If you are using the database just as an dump datastore<\/li>\n<li>Use the database for what it is strong in it (managing data, not logic)<\/li>\n<li>Developer has to maintain the logic in two different languages (java or c# and t-sql)<\/li>\n<li>Hard to test (slow, friction with current frameworks, anti-pattern for unit-tests (use of a slow infrastructure))<\/li>\n<li>Danger of duplicity of logic (in c# and in t-sql)<\/li>\n<li>Bad refactoring support<\/li>\n<li>DBA could change the logic in a procedure<\/li>\n<li>From an integration view, there could be side-effects, if a stored procedure is used by several applications and one application change the logic<\/li>\n<\/ul>\n<p><strong>Conclusion<\/strong><br \/>\nAnd what is the conclusion? Surprise, surprise: It depends. You shouldn&#8217;t use stored procedures for anything and you shouldn&#8217;t categorically reject them.<br \/>\nI tend to use stored procedures for performance issues and try to avoid to use them to implement business logic.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Recently I discussed with a colleague when to use stored procedures. As exptected it was quite a religious conversation. A few days later I found the following screencast: The Pros and Cons of Stored Procedures Based on the discussion and the screencast I tried to summarize my Pros and Cons: Pros Layering Low-level-Security Protect queries from changing data-access technologies (jdbc, hibernate, toplink, dbcommands, linq to sql, entity framework, nhibernate, etc&#8230;) Ivory-Tower-Feature: changing database during project does nearly never happen Coarse-grained&#8230;<\/p>\n<p class=\"read-more\"><a class=\"btn btn-default\" href=\"https:\/\/blog.eweibel.net\/?p=160\"> Read More<span class=\"screen-reader-text\">  Read More<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[5,4],"tags":[],"class_list":["post-160","post","type-post","status-publish","format-standard","hentry","category-good-practices","category-software-architecture"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/plOV9-2A","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":1182,"url":"https:\/\/blog.eweibel.net\/?p=1182","url_meta":{"origin":160,"position":0},"title":"Anti-Pattern &#8216;Validation by Execute &#8216;n&#8217; Rollback&#8217;","author":"Patrick","date":"21. Feb 2012","format":false,"excerpt":"Recently in some reviews I saw an anti-pattern. First you have to know, in the code, there was a validation of the data before it was stored in the database. So far so good. But when I looked at the validation code, I saw the following: public void Validate() {\u2026","rel":"","context":"In &quot;Anti patterns&quot;","block_context":{"text":"Anti patterns","link":"https:\/\/blog.eweibel.net\/?cat=8"},"img":{"alt_text":"Fotolia_20233238_S","src":"https:\/\/i0.wp.com\/blog.eweibel.net\/wp-content\/uploads\/Fotolia_20233238_S_thumb1.jpg?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":797,"url":"https:\/\/blog.eweibel.net\/?p=797","url_meta":{"origin":160,"position":1},"title":"NHibernate day in Bologna","author":"Patrick","date":"25. Oct 2010","format":false,"excerpt":"During a whole day several speakers spoke about NHibernate and related topics. This conference was in Bologna and was very well prepared and organized. You could watch the slides and the videos of the sessions here. I joined the following sessions: Keynote Simone Chiaretta opened the conference and showed during\u2026","rel":"","context":"In &quot;.NET&quot;","block_context":{"text":".NET","link":"https:\/\/blog.eweibel.net\/?cat=13"},"img":{"alt_text":"NHDay_3","src":"https:\/\/i0.wp.com\/blog.eweibel.net\/wp-content\/uploads\/NHDay_3_thumb.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":76,"url":"https:\/\/blog.eweibel.net\/?p=76","url_meta":{"origin":160,"position":2},"title":"Code generation for unit testing","author":"Patrick","date":"10. Mar 2009","format":false,"excerpt":"There exists a project at Microsoft which generate unit tests based of source code. The name of this interesting project is Pex.At the first time when I heard about this project I was skeptical. Where's the value of generated tests? Doesn't it break the \"test first\" approach? After I saw\u2026","rel":"","context":"In &quot;.NET&quot;","block_context":{"text":".NET","link":"https:\/\/blog.eweibel.net\/?cat=13"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":677,"url":"https:\/\/blog.eweibel.net\/?p=677","url_meta":{"origin":160,"position":3},"title":"Round-up of a data centric architecture","author":"Patrick","date":"11. Apr 2010","format":false,"excerpt":"In my last big project we had to use a data centric architecture. There was a learning curve which architecture was the most appropriate one. The result is visible in the picture bellow: Lets explaining the diagram. The data (or state) is managed by the database layer and the common\u2026","rel":"","context":"In &quot;Software architecture&quot;","block_context":{"text":"Software architecture","link":"https:\/\/blog.eweibel.net\/?cat=4"},"img":{"alt_text":"Architektur","src":"https:\/\/i0.wp.com\/blog.eweibel.net\/wp-content\/uploads\/Architektur_thumb.jpg?resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/blog.eweibel.net\/wp-content\/uploads\/Architektur_thumb.jpg?resize=350%2C200 1x, https:\/\/i0.wp.com\/blog.eweibel.net\/wp-content\/uploads\/Architektur_thumb.jpg?resize=525%2C300 1.5x"},"classes":[]},{"id":1198,"url":"https:\/\/blog.eweibel.net\/?p=1198","url_meta":{"origin":160,"position":4},"title":"Are stale data evil?","author":"Patrick","date":"27. Feb 2012","format":false,"excerpt":"When you're a software engineer who produces software for enterprises like banks or assurances, then it is normal you have huge databases (several gigabytes). Such systems have an operative application where users do the daily business of the company and there are more informative parts (or strategic parts) of the\u2026","rel":"","context":"In &quot;Software architecture&quot;","block_context":{"text":"Software architecture","link":"https:\/\/blog.eweibel.net\/?cat=4"},"img":{"alt_text":"Sexy young woman as devil in fire","src":"https:\/\/i0.wp.com\/blog.eweibel.net\/wp-content\/uploads\/Fotolia_37310173_S_thumb.jpg?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1213,"url":"https:\/\/blog.eweibel.net\/?p=1213","url_meta":{"origin":160,"position":5},"title":"Quality isn&rsquo;t a tool&ndash;You can&rsquo;t install it!","author":"Patrick","date":"20. Jun 2012","format":false,"excerpt":"Did you ask yourself why a team in an organization produces very good software quality and another team in the same organization just struggles to get things done and those things are in really bad quality? Interesting is also that for both teams exists the same rules (methologies, procedures, tools,\u2026","rel":"","context":"In &quot;Agile&quot;","block_context":{"text":"Agile","link":"https:\/\/blog.eweibel.net\/?cat=17"},"img":{"alt_text":"time, quality and money concept","src":"https:\/\/i0.wp.com\/blog.eweibel.net\/wp-content\/uploads\/Fotolia_36622856_S_thumb.jpg?resize=350%2C200","width":350,"height":200},"classes":[]}],"_links":{"self":[{"href":"https:\/\/blog.eweibel.net\/index.php?rest_route=\/wp\/v2\/posts\/160","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.eweibel.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.eweibel.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.eweibel.net\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.eweibel.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=160"}],"version-history":[{"count":23,"href":"https:\/\/blog.eweibel.net\/index.php?rest_route=\/wp\/v2\/posts\/160\/revisions"}],"predecessor-version":[{"id":183,"href":"https:\/\/blog.eweibel.net\/index.php?rest_route=\/wp\/v2\/posts\/160\/revisions\/183"}],"wp:attachment":[{"href":"https:\/\/blog.eweibel.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=160"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.eweibel.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=160"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.eweibel.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=160"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}