|
Sponsored Links
Resources
.NET Research Library
Get .NET related white papers, case studies and webcasts
|
News
News
News
|
Messages: 14
Messages: 14
Messages: 14
Printer friendly
Printer friendly
Printer friendly
Post reply
Post reply
Post reply
XML
XML
XML
|
 |
Stored procedures: yes, no, or maybe?
The world of software somewhat resembles a pendulum. Styles of development swing in and out of favor. Sometimes that swinging is very slow -- so that it is practically imperceptible. Developers with considerable experience may not be aware of pendulum swings that occurred just a few years before they started their careers.
Take stored procedures. Middle-tier specialists tried mightily to avoid these rolled up bits of logic placed in the database. They did that because the general consensus after the first generation of client-server development was that stored procedures did not scale and led to vendor lock in. A lot of the impetus for the whole three-tier server-oriented movement came from disaffection with stored procedures.
Things look different to people who count themselves as database developers, of course. But, there is no black and white. There is no pure world, or pure middle tier. Stored procedures work, to some extent, and they may be the shortest way to a successful project, sometimes. The original great impetus to avoid them was vendor lock-in. In former times, there were quite a number of different data bases to get locked into.
The issue seems to have paled some as the number of mainstream data bases has diminished. We should always be mindful, and if we lock ourselves in, we should at least be conscious of the fact.
This particular opinion piece was 'triggered' by a recent meeting of the Boston .NET Users Group that I was privileged to attend. There, data base consultant Adam Machanic discussed his ideas about architecting effective data access, with an eye toward creating abstracting APIs between the database and the business logic tier. You could have SOA-style stored procedures, he inferred. Can it really ever be quite that gray? It wasn't long before the audience engaged the presenter in some hot, but mostly good natured give and take. Where should the logic go? When? Let us know what you think.
|
|
|
Message #215743
Post reply
Post reply
Post reply
Go to top
Go to top
Go to top
|
|
Where you can test & debug
The majority of your business logic must be where you can easily debug it. Jumping from your application code to a stored procedure usually makes you lose your debug features. Same for unit testing and code coverage.
This said stored procedures are very useful and must be used when a compelling reason exists (performance in DB intensive operations, transparent data auditing, enforcement of complex and critical data level business rules). But SPs for CRUD operations is a big no-no.
David
|
|
|
Message #215744
Post reply
Post reply
Post reply
Go to top
Go to top
Go to top
|
|
Re: Stored procedures: yes, no, or maybe?
Stored procedures are not panacia for all the problems and challenges associated with data access, storage and retrieval. They like anything else in life, have their pros and cons. So a consultant answer for the stored proc , yes- no-maybe debate is, it depends and we may need to sit down and brainstorm our choices and come up with "best practices" around your problems.
Well consulting aside, I personally dont like to put my business and or application logic in stored procedures. The reason for this is not to avoid any db vendor lock-in. Infact,I have yet to come across companies or clients or people who are trying to avoid database vendor lock-in. Because that you wouuld be locked in anyway even if you dont use stored procs. For example , you may use some db vendor specific datatypes/length/or other constraints. So thats something you have to live with and accept. For some reason the database vendor lock-in debate never enraged as much as the lock-in debate for OS space i.e Windows/Unix space or Netscape/IE space or Java/C# space.
My reasoning for not using stored proc in lots of situation even when they seem like a good candidate to implement is lack of processing support in terms of XML processing, lack of in-built version control, lack of sophisticated IDE to ease writing stored proc, lack of integration with other middle ware components, where-in you may need to talk to some other corba, EJB components, etc for your business logic and /or data needs or some other external business rules. So I always felt, that I would be constrained in future if a need arose in my project to talk to some other component or application and then I would have a tough time to integrate my stored procedure logic and talking to external application. But if this is not the case for any of my projects and if for certain functionality I dont anticipate any such future need, I infact like to stored procedure and think that it gives lots of performance advantage to have good chunk of code in stored procs. I mean they are closest to your data, so why not. So it will depend on your application and its anticipated future needs and growth.
My 2 cents.
Praveen
|
|
|
Message #215750
Post reply
Post reply
Post reply
Go to top
Go to top
Go to top
|
|
Re: Where you can test & debug
SPs are a technical feature like any other. It has its pros and cons. Limiting their use by some architectural model like "multi layer/tier application" is contra productive. Whether to host (business) logic in a database using a SP or not should driven by careful analysis of the problem domain and its non functional requirements like performance and scalability. Testability or vendor lock-in might also be of concern. So all in all it´s a matter of meeting requirements on a case by case basis.
-Ralf
|
|
|
Message #215761
Post reply
Post reply
Post reply
Go to top
Go to top
Go to top
|
|
Re: Where you can test & debug
The majority of your business logic must be where you can easily debug it. Jumping from your application code to a stored procedure usually makes you lose your debug features. Same for unit testing and code coverage.
This said stored procedures are very useful and must be used when a compelling reason exists (performance in DB intensive operations, transparent data auditing, enforcement of complex and critical data level business rules). But SPs for CRUD operations is a big no-no.
David
Debugging should not be the primary purpose for determining where you place your applications logic. The truth is stay away from one solution or pattern fits all mentality and do what makes sense for the business.
If you are writing an application where the requirements are to be multi platform then it may not make sense to take advantage of platform extension like stored procedures. If you are writing a line of business application, then do not create a false requirement, such as the application needs to be portable. It is not worth the cost in almost any circumstance. Too many developers create false requirements like making reusable components, and portability when there is little to no chance your code for your line business application will ever be reused by anyone besides yourself.
Resusability and portability often times clash with requirments for departmental and line of business applications, where the primary purpose is to get the job done. This is the type of stuff that leads to faild projects or over budget and late projects. In this scenario platform extensions and stored procedures often time help improve performance, and allow you to get the job done faster.
I understand ISV's and some corporate developers may have the goal of being portable so this argument depends on what you are building and why you are building it. There is no one right answer.
|
|
|
Message #215772
Post reply
Post reply
Post reply
Go to top
Go to top
Go to top
|
|
Re: Stored procedures: yes, no, or maybe?
By far the most crucial need that stored procedures provide is an abstraction layer between the schema and applications that access the database. It is almost impossible to refactor the schema without it. That abstraction layer can be achieved through means other than stored procedures such as a windows service, web service or centralized data access library. HOWEVER, in order to make any of those alternatives work, you must, absolutely guarantee that ALL access goes through your service layer. That is the rub. Achieving that type of control through just a centralized library for example in a medium to large organization is extraordinarily difficult IMO and certainly more difficult than with stored procedures. Beyond that, stored procedures are substantially easier to performance tune than dynamic SQL because of the SQL Profiler.
|
|
|
Message #215819
Post reply
Post reply
Post reply
Go to top
Go to top
Go to top
|
|
Choose Transparently
We knew this was a big issue for some. So when we designed our new 2.0 .NET architecture called EntitySpaces we made sure that our customers could choose either stored procedures or dynamic sql. In fact, they indicate which they would rather use in the web/app.config file. There is no impact on their API at all. If they change thier mind later it's a two second config file change.
Mike Griffin
EntitySpaces
http://www.entityspaces.net
|
|
|
Message #216157
Post reply
Post reply
Post reply
Go to top
Go to top
Go to top
|
|
Re: Stored procedures: yes, no, or maybe?
SP's has some pros (inclusive performance and the
enforcement of business rules for all applivations)
and some cons (primarily the portability issue).
So much depend on whether your app needs to be
relative database independent or not. Now *and* in
the future.
If database portability is not an issue, then SP's seems
quite OK to me.
If database portability is an issue or *should* be an
issue, then there are two options:
- stay away from stored procedures
- spend time/money on developing/buying something
that encapsulates the usage of SP's
And the first is usually much cheaper than the second.
|
|
|
Message #216188
Post reply
Post reply
Post reply
Go to top
Go to top
Go to top
|
|
Dynamic SQL?
As in hacker friendly SQL? How do you prevent SQL-Injection?
My honest opinion, I don't care where business logic lies, but all access to a database should be martialled by stored procedures. Why? Because it is really hard to SQL inject into a properly written stored procedure. The only way anything bad is going to get in is if you run a dynamically created SQL statement.
Just my 2¢.
Steve Abram
|
|
|
Message #216200
Post reply
Post reply
Post reply
Go to top
Go to top
Go to top
|
|
SQL Injection
Steve, ever heard of using parameters with dynamic SQL ?
That gives you the same protection as in SP's.
Just my 2$
Danny
|
|
|
Message #216390
Post reply
Post reply
Post reply
Go to top
Go to top
Go to top
|
|
What are you supporting?
It seems like if you are going to have 1 app interacting with your database, then it really doesn't matter (at least to me).
But when you are writing enterprise applications, and you will have 1 SP that is called by 10 different apps, written by 5 different people, the value of the SP is big.
I supposed you could argue that instead of each app calling an SP they would all call an API.
But when it comes to locking down the DB I would rather just give the correct rights to the necessary SPs and not have to worry about client apps being able to directly access the database tables.
Maybe I am old school...
|
|
|
Message #218354
Post reply
Post reply
Post reply
Go to top
Go to top
Go to top
|
|
Parameters?
In .Net you can use parameters, but not in SQL. And let's look at the security and performance benefits of stored procedures. SP's are compiled, ad-hoc queries are not, so they are not optimized. Dynamic SQL means that the user needs to have a lot of permission on the table. Stored Procedures only need the person to have permission to execute it because the writer gave the permission to the stored procedure.
Steve Abram
|
|
|
Message #224588
Post reply
Post reply
Post reply
Go to top
Go to top
Go to top
|
|
SPROCS? -- Always
After seven years of always using stored procedures, we decided to take a roll your own ORM approach using Dynamic SQL. "Ahh, we'll factor in performance later" we sez.
Three years later, we are watching our database server idle between 10 - 20% as it compiles Dynamic SQL most of the day. In addition, we have no way to tune our SQL calls, as they are dictated by the ORM.
Stored Procedures provide an Interface to your data store that should not be violated. The sproc provides a way to maintain and tune your sql calls, which is increasingly important as your database grows over time.
Business Logic should reside in the middle tier as much as possible, but if you need raw data manipulition (ex. importing data into the database), sprocs provide the raw power required.
Good Databases have Solid Interfaces. SPROCs.
|
|
|
| |
|
| New content on TheServerSide.NETNew content on TheServerSide.NETNew content on TheServerSide.NET |
|
|
Language "mashups" will become more prominent, and developers will become polyglots, one programmer suggests.
SearchWinDevelopment.com offers an introduction to the language, performance, testing and data management improvements in VS 2008.
VBCode.com code snippets cover all aspects of application development, from data binding to security to the user interface.
Get up to date on XAML best practices with a variety of articles, tutorials and webcasts. [SearchWinDevelopment.com]
One team's experience with the VSTS DB edition suggests that it can improve workflow for dev teams. It also enhanced Agile efforts.
(June 24, Article)
Microsoft has begun to include DSL tools in the VSTS kit. A new book by Steve Cook and other VSTS team members helps set the stage.
(June 24, Article)
Cartoon: Be it ever so humble there is no place like your home after you get a Microsoft Home Server .
(June 18, Cartoon)
Microsoft's Thom Robbins says new technology to highlight in NET 3.5 includes AJAX, LINQ for both C# and VB, as well as tooling enhancements intended to ease the task of building WPF, WF and WCF apps.
(June 29, Podcast)
Venkat Subramaniam discusses AJAX bottlenecks, the tenets of Agile development and more. He spoke at the Ajax Experience.
(June 25, Tech Talk)
In the second of a two-part series, Michele Leroux Bustamente discusses design decisions related to the claims-based security model. Read the story and walk through the process for creating a set of claims-based utilities to encapsulate claims authorization at the service tier.
(May 24, Article)
Understanding why the Entity Framework exists and learning where it can fit into your projects can get you prepared for the eventual release early next year.
(May 10, Article)
Resource: This learning guide gives you quick access to useful links on Windows Communication Foundation security information.
(April 24, Article)
TSS.NET's Jack Vaughan spoke recently spoke with Microsoft's Brad Abrams to find out what he is seeing in the field and what the chefs in Redmond are cooking. Along the way he discusses patterns of AJAX frameworks.
(April 11, Article)
In a two-part series, Michele Leroux Bustamente explains how claims-based security is supported by WCF, and how you can implement a claims-based security model for your services.
(March 29, Article)
Windows Workflow Foundation is a new technology that many developers will need to get their heads around. In a brief excerpt adapted from Programming Windows Workflow Foundation: Practical WF Techniques and Examples using XAML and C#, K.Scott Allen considers aspects of workflow definition.
(March 22, Chapter Excerpt)
|
|
