none
critique requested on thin Data Access Layer RRS feed

  • Question

  • I have devised a way to save tons of code by calling stored procs with a single web service method, which takes a DataSet parameter, containing a DataTable with the name of the stored procedure and column names matching the parameter names, and row(0) contents matching the values to be passed. I have further simplified matters by making all parameters varchar, so a call from my client looks like:

      Dim paramVals As String() = { myString1, myString2 }

      Dim ds As DataSet = oneWsMethod("storedProcName", "param1,param2", paramVals)

      ...

      Dim params As String() = { this1String, thisOtherstring, thirdString }

      ds = oneWsMethod("anotherProc", "p1,p2,p3", params, usingTransaction:=True)

    My oneWsMethod creates the DataSet's DataTable from the list of params and string array, then calls the web service. The web method returns exceptions in a table that's handled in oneWsMethod. I've been told this works like a Strategy pattern, but I couldn't see the connection. It feels kind of like a hack, but it saves miles of code, and lets me enhance the client code without any changes to the web service.

    Is this a sound practice? Can someone please offer critique? Thanks.

    Wednesday, January 24, 2007 3:06 AM

Answers

  • Hi Kit,

       that way you may be paying a performance penalty, as you have to parse your "p1,p2,p3" inside oneWsMethod each time, although that doesn't prevent you to collect the benefits of your code line reduction. Actually you can benchmark both alternatives: yours and some other which doesn't consider the code reduction. Just in order to quantify how much are you paying in execution time for that gain in development time. Be realistic, don't meassure just one invocation: create a job which launches 100, 1000 invocations to oneWsMethod(...) and compare the END TO END elapsed time (from 1st call to last one)

       Eventually another way to address your problem could be with some sort of code generator, for instance with code snippets. That way you can shortcut your syntax, which will later expanded in the necessary code lines and compiled to CLI. That way nothing will be parsed in execution time so the performance penalty won't exist. But, again, try this just if you discover that the gap between your implementation and a regular is really considerable

     

     

    (If you consider this answer useful for the purposes you opened the thread, pls tag it as USEFUL. Thanks!)

    Wednesday, January 24, 2007 9:07 PM
  • Your method is very similar to the method I use in my own database layer.

    There is some speed advantage to having everything inline and avoiding object creation and looping where possible. However, this also creates code that's harder to read and understand, greatly increases the risk of inconsistant coding practices and typos, and can make debugging more difficult. These problems get multiplied when there are more than one developer on a project.

    Generally, I've found that unless very high speed performance is a strong requirement for a project, it is best to use methods that emphasize maintainability and stability over outright performance.

    Thursday, January 25, 2007 6:04 PM
  • I agree there is too much overhead in that. I actually recently developed a thin Layer using generated static methods for each stored procedure, following the idea of the SqlHelper.  this has worked to our advantage and the purpose of creating this thin layer was to improve performance and security (through Stored Procs).

    Now that the thin layer may be required to be used by other applications, we are actually considering using Remoting to make the layer available. we considered using Web Services, but definately using remoting and a binary formatter is way faster than using soap. Now that the WCF is out, maybe you should consider using it.

    Just remeber that string manipulation has a severe performance hit on any process, so parsing parameters like that is very expensive.
    Thursday, January 25, 2007 9:58 PM
  • I have a question, Kit

    You said you passed a DataSet as input...

     

    What if you passed, in the case of "p1,p2,p3" three individual SqlParameters assigning to them, 'course, three strings in the Value property?

    I always thought that the original version was that one. Why do you need to use a DataSet as input, instead of those three individual p1, p2 and p3?

    Friday, January 26, 2007 4:31 PM
  • Hi Kit

    Yeah, I think Ron calls it loosy goosy

    It's not just SOA though.  In OO you would be suffering from a lack of encapsulation. Where does your business logic lie?

    Sounds like the business logic is in the clients or the in database....

    Regards

    Ed Hill
    Wednesday, January 31, 2007 7:37 PM

  • Depends on your specifc situation but having the business logic in the clients can cause a few problems:

    - Updates to your business logic will require a new version rolled out to your clients. This can be easy if it's a small user base or if the clients are on a local network and your IT department can roll out the new clients easily. You should have some sort of update procedure in place.

    - Scalability. If you find you need to scale out to support more users for the business logic that sits in the client you can't. If the business logic is behind your web services you can farm them and scale out much more easily. Having business logic in stored procedures may hurt scalability too as it's easier to scale out a business logic layer than to scale out your database usually. If you find that performance is suffering in the business logic in the stored procedures you have to go out and buy a bigger database machine.

    - Security. Clients will have access to the busienss logic code that sits on their machines. This means that the code can be reverse engineered and could provide access to information or processes that you don't want them to then it's not a good place for the business logic. A further problem with security is that a malicious client could just post directly to your web services bypassing any business logic validation that exists on the client. Unless this side of things is in the stored procedures you can't enforce client side business logic.

     - Reuse. If in the future you have requirements for seperate developments to reuse your services, you would then need to implement the business logic that sits client side again in the new development. In SOA your usually looking to gain the ability to reuse your web services across developments, having business logic in the clients makes this harder to acheive.

    I've forgotten if your stiving for SOA or if it was mentioned by someone else in a reply but this approach seems to fall a little foul of the two tenents: Boundaries ere explicit & Services are autonomous.

    Of course this all depends on your particular situation, as always :-)

    Regards

    Ed Hill
    Thursday, February 8, 2007 6:58 PM

All replies

  • Hi Kit,

       that way you may be paying a performance penalty, as you have to parse your "p1,p2,p3" inside oneWsMethod each time, although that doesn't prevent you to collect the benefits of your code line reduction. Actually you can benchmark both alternatives: yours and some other which doesn't consider the code reduction. Just in order to quantify how much are you paying in execution time for that gain in development time. Be realistic, don't meassure just one invocation: create a job which launches 100, 1000 invocations to oneWsMethod(...) and compare the END TO END elapsed time (from 1st call to last one)

       Eventually another way to address your problem could be with some sort of code generator, for instance with code snippets. That way you can shortcut your syntax, which will later expanded in the necessary code lines and compiled to CLI. That way nothing will be parsed in execution time so the performance penalty won't exist. But, again, try this just if you discover that the gap between your implementation and a regular is really considerable

     

     

    (If you consider this answer useful for the purposes you opened the thread, pls tag it as USEFUL. Thanks!)

    Wednesday, January 24, 2007 9:07 PM
  • Your method is very similar to the method I use in my own database layer.

    There is some speed advantage to having everything inline and avoiding object creation and looping where possible. However, this also creates code that's harder to read and understand, greatly increases the risk of inconsistant coding practices and typos, and can make debugging more difficult. These problems get multiplied when there are more than one developer on a project.

    Generally, I've found that unless very high speed performance is a strong requirement for a project, it is best to use methods that emphasize maintainability and stability over outright performance.

    Thursday, January 25, 2007 6:04 PM
  • I agree there is too much overhead in that. I actually recently developed a thin Layer using generated static methods for each stored procedure, following the idea of the SqlHelper.  this has worked to our advantage and the purpose of creating this thin layer was to improve performance and security (through Stored Procs).

    Now that the thin layer may be required to be used by other applications, we are actually considering using Remoting to make the layer available. we considered using Web Services, but definately using remoting and a binary formatter is way faster than using soap. Now that the WCF is out, maybe you should consider using it.

    Just remeber that string manipulation has a severe performance hit on any process, so parsing parameters like that is very expensive.
    Thursday, January 25, 2007 9:58 PM
  • Thanks for the recommendation. Average time (of hundreds) was .77 sec / round trip vs. .55 sec -- after I changed my OneWsMethod to use string arrays instead of a DataSet to pass parameter names and values. (It still returns a DataSet.)

    My initial thought is that the performance impact is probably worth the benefit in simplicity and maintainability.

    Friday, January 26, 2007 4:24 PM
  • I have a question, Kit

    You said you passed a DataSet as input...

     

    What if you passed, in the case of "p1,p2,p3" three individual SqlParameters assigning to them, 'course, three strings in the Value property?

    I always thought that the original version was that one. Why do you need to use a DataSet as input, instead of those three individual p1, p2 and p3?

    Friday, January 26, 2007 4:31 PM
  • Good question. I had one case where I needed to pass multiple records, and found a DataSet very handy, so I thought it would make a great general-purpose, one-size-fits-all pattern. But I've never since had a need for it, so I now prefer passing string arrays:

      Dim paramNames As String() = {"@p1", "@p2"}

      Dim paramValues As String() = {"myValue", "anotherValue"}

      ds = oneWsMethod("anotherProc", paramNames, paramValues, usingTransaction:=True)

    Perhaps JSON would be lighter/faster, but I suspect I'd get more speed by switching from web services (on SOAP) to .NET remoting.

    Friday, January 26, 2007 7:36 PM
  • I found some thorough critique in Jeromy Carriere's MSDN Webcast: Patterns and Anti-Patterns in SOA. This doesn't use explicit boundaries: it would be awkward to change a stored proc without affecting the client, for instance. Also, the details of the "contract" are not spelled out in the interface: you can't tell, for instance, when you're supposed to pass an integer Supplier_ID along with a string Serial_Nbr. The client code has to "know" about the stored procedure independent of the interface.

    I paraphrase quotes of Ron Jacobs, product manager for the patterns & practices team, found at http://www.theserverside.net/tt/articles/showarticle.tss?id=SOAPatterns:

    "The four major tenets to be considered when designing service oriented applications: boundaries are explicit; services are autonomous; services share schema and contracts, not classes; and service compatibility is determined by policy. Underlying these tenets is the notion that systems must be built to anticipate change -- that they will need to adapt to new services in the future.

    The goal of SOA patterns is to bring loose coupling to architecture for systems that sustain for a long period of time. Anti-patterns do just the opposite. They have you use loosely coupled Web services in a tightly couple way. Flexibility and extensibility can become negative factors. One anti-pattern is 'loosey-goosey.' This is the idea that you are going to have an interface that accepts a fixed set of things but which also allows an extensible hunk of XML that someone might send. If I do this, the contract is weak.

    If you use a pattern like this, you tend to rely on the implicit behavior of the service. It works, but can falter when it encounters services outside of its initial experience. If an architecture is based on implicit behavior, it is very fragile."

    Having said that, I'm not convinced that Service Oriented Architecture is the best model for services we don't want called by anything (at this point) except our specific client.

    Saturday, January 27, 2007 10:12 PM
  • Hi Kit

    Yeah, I think Ron calls it loosy goosy

    It's not just SOA though.  In OO you would be suffering from a lack of encapsulation. Where does your business logic lie?

    Sounds like the business logic is in the clients or the in database....

    Regards

    Ed Hill
    Wednesday, January 31, 2007 7:37 PM
  • Hello Diego

    Although you are right when talking about performance penalty when manipulating strings, but since we are discussing a web service topic, I guess by definition we are talking about parsing XML strings, as well as serializing/deserializing data to/from text format. So an extra small parsing "p1, p2, p3" won't be a big deal. Please correct me if I am wrong.

     

     

    Wednesday, February 7, 2007 7:16 PM
  • Business logic is split about 50-50 between client and stored procs.
    Thursday, February 8, 2007 6:24 PM

  • Depends on your specifc situation but having the business logic in the clients can cause a few problems:

    - Updates to your business logic will require a new version rolled out to your clients. This can be easy if it's a small user base or if the clients are on a local network and your IT department can roll out the new clients easily. You should have some sort of update procedure in place.

    - Scalability. If you find you need to scale out to support more users for the business logic that sits in the client you can't. If the business logic is behind your web services you can farm them and scale out much more easily. Having business logic in stored procedures may hurt scalability too as it's easier to scale out a business logic layer than to scale out your database usually. If you find that performance is suffering in the business logic in the stored procedures you have to go out and buy a bigger database machine.

    - Security. Clients will have access to the busienss logic code that sits on their machines. This means that the code can be reverse engineered and could provide access to information or processes that you don't want them to then it's not a good place for the business logic. A further problem with security is that a malicious client could just post directly to your web services bypassing any business logic validation that exists on the client. Unless this side of things is in the stored procedures you can't enforce client side business logic.

     - Reuse. If in the future you have requirements for seperate developments to reuse your services, you would then need to implement the business logic that sits client side again in the new development. In SOA your usually looking to gain the ability to reuse your web services across developments, having business logic in the clients makes this harder to acheive.

    I've forgotten if your stiving for SOA or if it was mentioned by someone else in a reply but this approach seems to fall a little foul of the two tenents: Boundaries ere explicit & Services are autonomous.

    Of course this all depends on your particular situation, as always :-)

    Regards

    Ed Hill
    Thursday, February 8, 2007 6:58 PM
  • These are good points. I'm moving some business logic out of the client and into stored procedures.

    I think I can recast the issue as wrapping an extra layer onto the SqlHelper used in the Data Access Layer, so clients can talk directly to the stored procs. I'm concerned about the SOA tenets -- explicit boundaries and autonomous services -- so I wonder whether it's legitimate to maintain that the stored procs present the boundaries and autonomous services, while the web service just provides a way to interface with them.

    Tuesday, February 13, 2007 8:43 PM