Why do you use stored procedures?

Sep 7, 2007 at 12:34 PM
Edited Sep 7, 2007 at 12:36 PM

I find this project very interesting but I don't understand why are you using stored procedures in its logic. I think it will be more versatile if you don't use them. Don't you agree with me?


Sep 12, 2007 at 5:59 AM
Edited Sep 12, 2007 at 6:02 AM
Hi Francesc,

I guess that depends what you mean by "don't use them". If you mean using dynamic SQL then I'm afraid I don't agree with you! I'll give you a couple of reasons, you can decide for yourself...

  • Performance - in the kind of applications that many workflows are used, performance is of prime importance. Dynamically executed SQL, as you suggest, does not perform any where near the levels that a stored procedure can - e.g. under Oracle there is overhead in creating execution plans, etc. for purely dynamic SQL (less so for parameterised queries).
  • Maintenance - sticking SQL in the C# code really is not very intuitive or easy to maintain. This is particularly true where you have DBAs maintaining the back-end database - if we use stored procedures a DBA can optimise and maintain those stored procedures. If we use dynamic SQL then the DBA has absolutely no control over what executes against the database! Changes to the SQL may require a re-compilation of the C# code before performance fixes can be made :S.
  • Batching - a number of the stored procedures used by the persistence & tracking services tend to execute a number of distinct operations. Using dynamic SQL we'd need to execute several round trips to the database server. Icky...

If, on the other hand, you're advocating the use of an ORM such as NHibernate or Linq for SQL then I'd happily agree with you, although its use would depend on the specific scenario... We just haven't got around to writing a provider for this yet! Expect that in one of the upcoming releases.

You are more than welcome to create such a provider yourself. This should be relatively easy for the persistence service, perhaps slightly more complex for the tracking service. Drop me any code you come up with, I'll code review and we'll get it stuck into the source tree!

Let me know what you think!


Project Co-ordinator
Windows Workflow Tools
Sep 25, 2007 at 9:19 AM
Thank you very much for your response. At this moment I'm having a lot of work related to workflow so I prefer to use something as stable as possible for the persistence service. So I will use WFTools as is, maybe in the future I will try to create a provider without storeds.

I will report any issue I find and I will try to solve it too.

Thanks for your work!!