Introducing Amber, Relational-to-Object Mapping instead of ORM

Discussions

News: Introducing Amber, Relational-to-Object Mapping instead of ORM

  1. The connection between object-oriented systems and relational databases is commonly solved by employing a so-called O/R mapping (ORM) framework whose goal it is to map object-oriented models to entity relationship models which are used, often generated, in relational databases. The bridging between two entirely different modelling techniques results in a so-called impedance mismatch, that needs to be overcome.

    An unwritten consensus in the industry is that the best approach in solving these problems is a process where the business requirements are modelled in the object domain and where the resulting object model is mapped via an O/R mapping framework into the relational database system.

    This article proposes a reversed approach where the modelling is done in the relational tier and as much business logic as possible is handled within the database by employing a set of stored procedures as the middle tier. A lightweight Java API, called Amber, is introduced that uses Java annotations instead of XML descriptors to help marshal result sets to Java objects and back into the database.

    This text discusses the reversed nature of R/O mapping compared to O/R mapping, points out pros and cons, and shows how Amber, in concert with a set of stored procedures, can be used for simple yet powerful R/O mapping. While this text deals with these issues in a little more abstract way and more from the point of view of the database, an introductory article on Amber, its mapping strategy and its underlying concepts has been published under the title Lightweight R/O Mapping from OnJava.com.

    Read Java and the Empowered Database.

    Threaded Messages (120)

  2. This can work in small to smaller mid size projects. As the complexity of the system grows (and it WILL grow if your system is used and evolves) you end up with the complicated procedural code with a lot of database specific spicy things like:

    - cyclic dependencies (spiced by invalidated objects),

    - dozens of triggers per table (especialy when you start to 'integrate' your database with other systems),

    - code duplication (better said code 'tenplication'),

    - view nesting (calling view from view from view...),

    - business logic distribution:
    a) it is hard to put all the business logic in the database as database objects and stored procedure languages are not enough expressive,
    b) logic is distributed in different database objects: tables (defaults, constraints, keys), views, stored procedures, triggers etc,
    c) application deveopers can't/won't wait/bother for/with database developers to implement some feature and implement it in the application tier,

    - low performance (as nobody knows what is happening in the code as it is hard to navigate and have the 'global picture'),

    - no caching or hard-to-implement caching on application side (record set caching leads to complex and error prone cache invalidation code as well as cache size explosion as pieces of data are stored in the cache multiple times),

    - hard development and maintenance. Stored procedures are mostly written in procedural languages which are hard to navigate and maintain and the tools are a generation or two behind Java/.NET tools. When OO languages are used then it is commonly a painful compile/deploy/test cycle.

    Who needs more?
  3. Actually, there is more:

    - complex deployment, especialy upgrades,

    - higher TCO (licensing, consulting) as everything is more complex then it should be,

    - lower quality (as it is harder to test),

    - lower ability to adapt to cahnge in requirements.
  4. You also have the scaling problem.

    Most every system fans out from the DB outward, but inevitably your major scalability obstacle is the DB. Pushing more load on the DB lowers your options.

    On the other hand, I've always thought that it would be interesting to have a DB integrated with an App server, to wit you can have DB triggers and stored procedures talking to Session Beans, etc. Using triggers to maintain middle tier caches, fire business rules on inserts, etc.

    I think a tight integration between something like Derby and Glassfish would be just a really powerful platform, giving all sorts of avenues for development.

    But, again, it becomes difficult to scale in the long term.
  5. On the other hand, I've always thought that it would be interesting to have a DB integrated with an App server, to wit you can have DB triggers and stored procedures talking to Session Beans, etc. Using triggers to maintain middle tier caches, fire business rules on inserts, etc.

    Yes, integrating DB with app server would be great. Having DB and App server in one process is sure benefital from the performance point. But I disagree with the triger/st.proc. way of integration. For far future I am hoping for something I call object server (object database with embeded app server/vm or app server/vm with persistence service, depends on your side of view).
  6. DB/App Server integration: Yes, but...[ Go to top ]

    For far future I am hoping for something I call object server (object database with embeded app server/vm or app server/vm with persistence service, depends on your side of view).
    It sounds like GemFire isn't it ?
  7. You also have the scaling problem. Most every system fans out from the DB outward, but inevitably your major scalability obstacle is the DB. Pushing more load on the DB lowers your options. On the other hand, I've always thought that it would be interesting to have a DB integrated with an App server ..

    That's the functionality that a product like Coherence provides. The application can obtain the data it needs within the application tier, thus offloading the work from the database and moving it into a horizontally-scalable tier.

    Peace,

    Cameron Purdy
    Tangosol Coherence: The Java Data Grid
  8. ..., but inevitably your major scalability obstacle is the DB.

    Really?? Since when. Modern enterprise level DBs (with the right DBA) seem to be much more scalable than anything I've ever been able to write for Websphere or weblogic.

    Stored procedures may be slower or faster than app-server based code, or incomparable altogether, since they can apply optimisation at a completely different level, but when it comes to scalability, with enough hardware, they seem to have no probs at all...

    The problem here is that you're succumbing to a suboptimal application model, dictated by the DB structure.
  9. Scalin'[ Go to top ]

    " Since when."
    Since the last time I looked

    " Modern enterprise level DBs (with the right DBA) seem to be much more scalable..."
    Sure, they can scale to a point. But at what cost ...
    ".. than anything I've ever been able to write for Websphere or weblogic."

    Well,let me introduce you to Cameron. See one of his previous posts about good architecture and his video post on TSS awhile ago.
  10. Scalin'[ Go to top ]

    " Since when."Since the last time I looked" Modern enterprise level DBs (with the right DBA) seem to be much more scalable..."Sure, they can scale to a point. But at what cost ...".. than anything I've ever been able to write for Websphere or weblogic."Well,let me introduce you to Cameron. See one of his previous posts about good architecture and his video post on TSS awhile ago.

    Found It! http://www.theserverside.com/symposium/FLV_Presentations/PerformanceScalability_TSSJS/PerformanceScalability_TSSJS.html

    I was on my PocketPC earlier.
  11. ..., but inevitably your major scalability obstacle is the DB.
    Really?? Since when. Modern enterprise level DBs (with the right DBA) seem to be much more scalable than anything I've ever been able to write for Websphere or weblogic.Stored procedures may be slower or faster than app-server based code, or incomparable altogether, since they can apply optimisation at a completely different level, but when it comes to scalability, with enough hardware, they seem to have no probs at all...The problem here is that you're succumbing to a suboptimal application model, dictated by the DB structure.

    The major scalability obstacle is your application, not the DB. You can tinker with stored procedures all you like, but if you hammer the database from your application your DB is eventually going to grind to a halt no matter how scalable it is.

    I watched a .NET project that built a moderate volume web site go bad for that exact reason. Everything in the DB was tuned and filed to perfection by a DBA but sure enough, the application turned stiff like a salted herring as soon as it experienced any load. The problem went away as soon as the page content was cached properly on the web server. That database server was pretty beefy compared to the web server. Yet, the problem could not be solved on the database level.
  12. I know of an app that has reached its limits. It has no UI so , of course, it has no need for anything but a db server. ;)

    One of the biggest problems I have seen in these sorts of things is that they are bulldozers moving mountains. And that, again, goes back to what Cameron is saying and I was kidding him about. It takes a little more than just using Java and an App Server.
  13. ..., but inevitably your major scalability obstacle is the DB.
    Really?? Since when. Modern enterprise level DBs (with the right DBA) seem to be much more scalable than anything I've ever been able to write for Websphere or weblogic.Stored procedures may be slower or faster than app-server based code, or incomparable altogether, since they can apply optimisation at a completely different level, but when it comes to scalability, with enough hardware, they seem to have no probs at all...The problem here is that you're succumbing to a suboptimal application model, dictated by the DB structure.

    Imagine 2 app server machines and 1 DB machine running 100% CPU and I/O.

    Now you move all the business logic from app server machines to DB machine (implemented by stored procedures).

    Now both app server machines are almost CPU and I/O IDLE as they have nothing to do.

    Theoretically the DB machine is running now 300% CPU and I/O.

    >>> Modern enterprise level DBs (with the right DBA) seem to be much more scalable

    Please elaborate, how are you going to horizontally scale your system as the vertical scalability is more expensive and very limited???

    If you have app servers just with the sticky session load balancing you can scale horizontally almost infinitely (500 - 1000 boxes).

    How are you going to horizontally scale your DB???
  14. How are you going to horizontally scale your DB???

    Well, I'm not a DBA, but how about this: http://www-128.ibm.com/developerworks/db2/library/techarticle/adamache/0110adamache.html
  15. How are you going to horizontally scale your DB???
    Well, I'm not a DBA, but how about this: http://www-128.ibm.com/developerworks/db2/library/techarticle/adamache/0110adamache.html

    You can go for a shared storage (Oracle)
    Or you can go for shared nothing

    In both cases you have to use some way of a distributed lock/cache mechanism, which is the bottle neck. It usually scales in same way as the vertical scaling (max 8/16 CPUs or boxes)

    you can also go for a table partitioning, but it is an application specific (time, users, random ...)

    IMO it is better to scale only application logic than to scale the application logic and DB persistence together, especially in a case, when the business logic scales better than DB persistence

    Also usually DB machines are optimized for their I/O and not for CPU. Business logic can tend to be more CPU than I/O intensive.

    We left the thin client-server architecture from 90s (Informix4GL), because it could not scale more than 500 users. Maybe now this technology would scale better with modern DBs.

    IMO the split of business logic and DB persistence was done, because of the scalability
  16. I reckon there are many reasons to split storage and functionality (and many benefits), one of them being that in a big organisation you don't know what the back end might be. if you remember the original j2ee specs an all that, they treat the database as EIS tier, ie. equivalent to any odd mainframe system you might have there. I think that was the rationale behind the connector architecture as well. which just highlights why a db centric approach contradicts with the spirit of enterprise java.
  17. I reckon there are many reasons to split storage and functionality (and many benefits), one of them being that in a big organisation you don't know what the back end might be. if you remember the original j2ee specs an all that, they treat the database as EIS tier, ie. equivalent to any odd mainframe system you might have there. I think that was the rationale behind the connector architecture as well. which just highlights why a db centric approach contradicts with the spirit of enterprise java.

    It is against OO concept to split data and behaviour.
  18. I reckon there are many reasons to split storage and functionality (and many benefits), one of them being that in a big organisation you don't know what the back end might be. if you remember the original j2ee specs an all that, they treat the database as EIS tier, ie. equivalent to any odd mainframe system you might have there. I think that was the rationale behind the connector architecture as well. which just highlights why a db centric approach contradicts with the spirit of enterprise java.
    It is against OO concept to split data and behaviour.
    But it is not against common sence, is it ? OO concepts are not dogma. If you have a reason then you can break any concept, it is not a crime to split data and behaviour.
  19. I reckon there are many reasons to split storage and functionality (and many benefits), one of them being that in a big organisation you don't know what the back end might be. if you remember the original j2ee specs an all that, they treat the database as EIS tier, ie. equivalent to any odd mainframe system you might have there. I think that was the rationale behind the connector architecture as well. which just highlights why a db centric approach contradicts with the spirit of enterprise java.
    It is against OO concept to split data and behaviour.
    But it is not against common sence, is it ? OO concepts are not dogma. If you have a reason then you can break any concept, it is not a crime to split data and behaviour.


    Well, database contains data and if we move behaviour(business logic) to outside(J2EE container), one of the fundamental concept of OO design is broken. It is no more OO!
  20. Yes, you must encapsulate data using stored procedures if this dogma is valid. http://en.wikipedia.org/wiki/Information_hiding
  21. Yes, you must encapsulate data using stored procedures if this dogma is valid.

    There is no need. If you are using an O/R Mapping then information is retained about the objects that are represented by tables or views in the database, so the behaviour is 'bound' to the data via the mapping - the system remains OO.

    Of course, stored procedures + data are not OO - there is no inheritance or polymorphism.
  22. OOP concepts are defined by analogy, we are free to interpret analogies (fantazy), but common sence must help to avoid "wrong" interpreation.
  23. OOP concepts are defined by analogy, we are free to interpret analogies (fantazy), but common sence must help to avoid "wrong" interpreation.

    OO concepts in ORM aren't fantasy, as they are used in practical applications to retrieve data from relational stores as objects. There is no wrong interpretation - just appropriate interpretations in different circumstances. A whole range of interpretations can be put on information stored in relational systems, depending on how that information is to be used.
  24. Analogy interpreation like "if information hidding is the right way then hide information in stored procedures" is fantasy. Probably I just failed to write it clear in previous post.
  25. There is a lot of information about this stuff, http://www.databasejournal.com/features/mssql/article.php/1583581, but probably it is too expensive for "typical" web sites.
  26. Probably this is not so expensive: http://www.mysql.com/products/database/cluster/
  27. scalability[ Go to top ]

    ..., but inevitably your major scalability obstacle is the DB.

    Really?? Since when. Modern enterprise level DBs (with the right DBA) seem to be much more scalable than anything I've ever been able to write for Websphere or weblogic.

    There are Java-based equities trading systems built with the aforementioned J2EE server products that have to support over 10,000 concurrent users, with real-time event flow to the trading desktops.

    There are computational grids of over 1000 nodes (i.e. over 4,000 x86 CPU cores) already in use for Java-based risk management applications.

    There are Java-based real-time compliance systems that log over a billion transactions an hour.

    Even "simple" ;-) ecommerce applications such as eBay and Amazon have been forced to move away from reliance on database scaling and tuning, due to ludicrous expense and inability to scale. I could name a half dozen smaller etailers here in the states and in Europe that have been forced to do the same.

    The database isn't going away, any more than banks are going away. However, most people carry cash in their wallet and some credit cards instead of running to the bank to conduct every transaction. The scalability of the modern banking system is accomplished through its asynchronous nature (cash in the wallet acting as an I/O buffer) and its distributed nature (credit cards accepted at every retail point). Databases in large-scale systems are no different.

    Peace,

    Cameron Purdy
    Tangosol Coherence: Clustered Shared Memory for Java
  28. scalability[ Go to top ]

    Peace,Cameron Purdy

    Yep! Peace, man!

    On a slightly different note, I wonder why object DBs are not getting more attention on TSS. I know some of these heavy risk-management apps use them as storage for short lived data to maximise performance. But somehow they don't seem to be very popular yet as a main persistence solution. Are they not good enough yet, or is it just inertia?
  29. scalability[ Go to top ]

    On a slightly different note, I wonder why object DBs are not getting more attention on TSS. I know some of these heavy risk-management apps use them as storage for short lived data to maximise performance. But somehow they don't seem to be very popular yet as a main persistence solution. Are they not good enough yet, or is it just inertia?

    I can't speak universally, but two (soon to be three) of the "big four" use our Coherence product for the data layer in their production risk management grids, but it's *not* an object database, just a distributed in-memory object management system (alternatively called a "coherent cache", "data grid", "information fabric", "clustered shared memory", "single system image", etc.) See:

    http://wiki.tangosol.com/display/COH31UG/Provide+a+Queryable+Data+Fabric
    http://wiki.tangosol.com/display/COH31UG/Provide+a+Data+Grid

    The database in one case is Sybase. I don't know about the others.

    A lot of banks use Data Synapse for the compute portion of their risk management grids and Coherence as the data caching service.

    I'm not aware of an object database being used for that role in any of the big four or any of the big banks. Two of the big four tend not to buy any software if they can help it (they still can't figure out if they want to be software companies or financial services firms when they grow up.)

    For persistence, I'd still say go Oracle / Sybase / DB2. The infrastructure around those products is 100x as stable and mature as any of the OODBMSs. It's just not worth sacficing the serviceability and manageability you get with an established RDBMS to obtain the theoretical speedup you might get with an OODBMS. Call me conservative, but when you're dealing with persistent data, it's always better to be safe.

    Besides, none of the database systems (object or relational) can provide the throughput necessary to run even a single large risk management calculation in anywhere near real time, and with the explosion in credit derivatives, the data sets are only getting larger and more complex. A "small" risk management system today might have "only" 600 engines. Each can theoretically crunch a gigabyte of data each second. That's an aggregate of about 100x what the highest throughput scale-out database can produce. There's just no possible way to build these systems on a database-centric model.

    Peace,

    Cameron Purdy
    Tangosol Coherence: The Java Data Grid
  30. scalability[ Go to top ]

    Cameron, I would be interested in how these systems load the data, etc. Just general info, I know you can't give specifics. I know of one that works with truck configurations. I had suggested your product to them because they were experiencing all the problems on your web page :), but I was a contractor so ... .

    Anyway, there is a system that my company maintains that is very database centric (I don't think any code runs outside the db) and the processing time is incredible. I believe it can be improved greatly, but I would love to have examples demonstate what can be done. Maybe even some stats.
  31. scalability[ Go to top ]

    Cameron, I would be interested in how these systems load the data, etc. Just general info, I know you can't give specifics.

    There are a variety of data sources. In a risk management app for example, reference data is relatively small (e.g. less than 1GB) but every engine uses it. It used to be that the engines would load it from a db, but nowadays they either load it from a reference service (i.e. directly from a dedicated Coherence data grid or even via web services). The bulk of the data is either historical (sitting on a db or olap/warehouse somewhere) or its feed-based, meaning in some cases several thousand messages per second sustained flow. The nice thing about the feeds is that they don't even have to be persistend anymore -- you just pump them straight into the data grid.
    I know of one that works with truck configurations. I had suggested your product to them because they were experiencing all the problems on your web page :), but I was a contractor so ... .

    If it's the one I think it is, they took your advice ;-).
    Anyway, there is a system that my company maintains that is very database centric (I don't think any code runs outside the db) and the processing time is incredible. I believe it can be improved greatly, but I would love to have examples demonstate what can be done. Maybe even some stats.

    Can you describe the process / processing?

    Peace,

    Cameron Purdy
    Tangosol Coherence: Clustered Shared Memory for Java
  32. scalability[ Go to top ]

    Cameron, I would be interested in how these systems load the data, etc. Just general info, I know you can't give specifics.
    There are a variety of data sources. In a risk management app for example, reference data is relatively small (e.g. less than 1GB) but every engine uses it. It used to be that the engines would load it from a db, but nowadays they either load it from a reference service (i.e. directly from a dedicated Coherence data grid or even via web services). The bulk of the data is either historical (sitting on a db or olap/warehouse somewhere) or its feed-based, meaning in some cases several thousand messages per second sustained flow. The nice thing about the feeds is that they don't even have to be persistend anymore -- you just pump them straight into the data grid.
    I know of one that works with truck configurations. I had suggested your product to them because they were experiencing all the problems on your web page :), but I was a contractor so ... .
    If it's the one I think it is, they took your advice ;-).
    Anyway, there is a system that my company maintains that is very database centric (I don't think any code runs outside the db) and the processing time is incredible. I believe it can be improved greatly, but I would love to have examples demonstate what can be done. Maybe even some stats.
    Can you describe the process / processing?Peace,Cameron PurdyTangosol Coherence: Clustered Shared Memory for Java
    Thanks. Can we talk about the other stuff offline?
  33. scalability[ Go to top ]

    Can we talk about the other stuff offline?

    Sure, my email is cameron at tangosol dot com

    Peace,

    Cameron Purdy
    Tangosol Coherence: The Java Data Grid
  34. Stored procedures are grim[ Go to top ]

    They are hard to manage and hard to debug. But the killer is lack of portability.

    At my previous company, one of their products is written in VB/.net and uses MS SqlServer as the backend. They can't scale the system beyond 10,000 employees. They'd like to sell it to larger enterprise customers but they'd have to support Oracle to get that scalability. The problem is, their system has thousands of stored procedures.

    The cost involved in porting the stored procedures to Oracle and maintaining their business logic in two different stored procedure languages is huge.

    The main argument for stored procedures came back in the day when we were all doing client-server applications with weak desktops at the end of narrow pipes. Stored procedures kept the processing on the server, where we had processing power, and also reduced the amount of data that we had to stuff down that narrow pipe.

    But that's not what we have today, is it? Today, we most likely have an application server that talks to the database, not a client. The application server is a big beafy machine probably sitting in the same data center as the database machine and they are connected via fiber.

    Today's architecture has changed. Stored procedures can be useful in rare circumstances. But they are not a good solution to most of today's problems.
  35. ObjectDB[ Go to top ]

    In my previous job we implemented ObjectDB as the application database with Oracle serving only as enterprise quality central data store. We had originally thought of using MySQL, but ObjectDB was 45 times faster than MySQL with a single query running, and more than 200 times faster with 25 queries running against the database (retrieving identical results). Add to the performance natural object modelling, and you get an absolute winner.
  36. This is an interesting framework. It would be interesting to compare it with iBatis, which also allows a stored procedure-centric approach.

    I agree with some of your points but not all. Specifically:

    "it is hard to put all the business logic in the database as database objects and stored procedure languages are not enough expressive,"

    PL/SQL is not too bad, although T-SQL is admittedly not a great language. It is quite simple however which does have a lot of value. It is harder to hang yourself when you don't have much rope!

    "application deveopers can't/won't wait/bother for/with database developers to implement some feature and implement it in the application tier"

    I don't think it matters whether you are using stored procedures or Hibernate. If your developers don't understand both tiers and in particular how to tune the database your application will probably end up running like a dog.

    "low performance (as nobody knows what is happening in the code as it is hard to navigate and have the 'global picture')"

    This is where I strongly disagree. The strong point about stored procedures is that you can analyse all the query plans up front (since the SQL is not generated dynamically) and you can see exactly which line of the proc is running in the database at any point in time. You can also capture and keep logs that show in detail what locking and I/O is taking place at any given time and look at these historically to understand where problems lie by linking back to specific lines in stored procedures. You have to do other things to enable you to have this analysis capability with other approaches (such as Hibernate). Databases such as Sybase that don't have MVCC are particularly vulnerable to poorly coded queries - even a bad select can bring a database to its knees.

    Delimiting transactions in the database can lead to shorter transactions (since there is less I/O involved between the client and the database) which in turn means more transactions per second. To see this try a very simple example where you have a proc that does begin tran insert commit, and then do the same thing where you have three statements in Java.

    Having said that, not all applications have as their main goal maximising the number of transactions per second. Hibernate does give you greater flexibility and the ability to take advantage of better tooling which in turn can lead to faster development.

    One other thing to consider is dependency analysis. Typically your data outlives your application. Most databases provide excellent dependency tools (e.g. which stored procedures use column x in table y etc.). This can be much harder to track in middle tier code - particularly when DBAs don't know the middle tier language.
  37. Delimiting transactions in the database can lead to shorter transactions (since there is less I/O involved between the client and the database) which in turn means more transactions per second. To see this try a very simple example where you have a proc that does begin tran insert commit, and then do the same thing where you have three statements in Java.

    I'm not entirely sure what you mean by 'three statements in Java', but good ORMs will batch SQL for increased performance; I don't think that I/O is usually the issue you suggest it might be.
    Typically your data outlives your application. Most databases provide excellent dependency tools (e.g. which stored procedures use column x in table y etc.). This can be much harder to track in middle tier code - particularly when DBAs don't know the middle tier language.

    I often come across situations where the data not only outlives the application, also it outlives the database, as issues of support, pricing, licensing and customer requirements require transfer of information to different storage products and methods. Some databases may provide excellent dependency tools, but there are plenty of tools for such analysis of the middle-tier language code and object model which can represent the relationships in a way that even DBAs can understand....
  38. Yes, "insert" is not a problem, "update/delete" is more problematic. Probably some ORM implementations can "update/delete" not loaded object by id or by query without "select", but it must be better to use something else than ORM if this is an important issue.
  39. Yes, "insert" is not a problem, "update/delete" is more problematic. Probably some ORM implementations can "update/delete" not loaded object by id or by query without "select", but it must be better to use something else than ORM if this is an important issue.

    Yes, modern ORMs can do updates and deletes without having to load objects. JDO 2.0 has delete by query, and Hibernate has bulk update/delete.

    Of course, using ORM is not an all-or-nothing decision. All modern ORMs allow the use of stored procedures and native SQL where necessary or useful.
  40. Yes, "insert" is not a problem, "update/delete" is more problematic. Probably some ORM implementations can "update/delete" not loaded object by id or by query without "select", but it must be better to use something else than ORM if this is an important issue.
    Yes, modern ORMs can do updates and deletes without having to load objects. JDO 2.0 has delete by query, and Hibernate has bulk update/delete.Of course, using ORM is not an all-or-nothing decision. All modern ORMs allow the use of stored procedures and native SQL where necessary or useful.
    Yes, I like modern ORM implementations for this reason too. There is a "stateless" session implementation in hibernate for transaction processing too.
  41. TPS[ Go to top ]

    Delimiting transactions in the database can lead to shorter transactions (since there is less I/O involved between the client and the database) which in turn means more transactions per second.

    Shorter transactions only lead to more TPS in a single-threaded system. Any decent database can handle thousands of concurrent transactions.

    Peace,

    Cameron Purdy
    Tangosol Coherence: The Java Data Grid
  42. TPS[ Go to top ]

    Shorter transactions only lead to more TPS in a single-threaded system.

    This is not true. It also depends on the locking that is being done during each transaction. If you reduce the length of each transaction you reduce the time any locks are held which can increase throughput.

    How big an impact this will have depends in part on the locking model used in your database. Sybase, for example, does not have MVCC therefore it is particularly sensitive to this (even more so where page level locking is used).

    If your ORM spits out a poor query that causes, say, a table scan to be used that could lead to a table level lock being held for quite some time (depending on the size of the table). That in turn will dramatically limit the number of transactions per second you can achieve if the table is used frequently.

    --Robert
  43. TPS[ Go to top ]

    Shorter transactions only lead to more TPS in a single-threaded system.

    This is not true. It also depends on the locking that is being done during each transaction. If you reduce the length of each transaction you reduce the time any locks are held which can increase throughput.How big an impact this will have depends in part on the locking model used in your database. Sybase, for example, does not have MVCC therefore it is particularly sensitive to this (even more so where page level locking is used).If your ORM spits out a poor query that causes, say, a table scan to be used that could lead to a table level lock being held for quite some time (depending on the size of the table). That in turn will dramatically limit the number of transactions per second you can achieve if the table is used frequently.

    We could go back and forth. For example, Sybase tends to avoid pessimistic (blocking) locks as much as possible, so regardless of MVCC support, it's possible that the condition you suggested would never occur even with long-running transactions (assuming they are not coordinated 2pc).

    The point I was trying to make is that shorter transactions do not in any way guarantee more transactions. Latency and throughput are only directly related in a single-threaded environment.

    You may be right (I believe you are) that there are lots of good reasons to keep transactions very short, but that does not necessarily translate to a throughput gain.

    Peace,

    Cameron Purdy
    Tangosol Coherence: The Java Data Grid
  44. TPS[ Go to top ]

    We could go back and forth. For example, Sybase tends to avoid pessimistic (blocking) locks as much as possible, so regardless of MVCC support, it's possible that the condition you suggested would never occur even with long-running transactions (assuming they are not coordinated 2pc).

    Can you elaborate on this? Are you referring to the threshold for promotion from a page (or row) level lock to a table lock?
    The point I was trying to make is that shorter transactions do not in any way guarantee more transactions. Latency and throughput are only directly related in a single-threaded environment.You may be right (I believe you are) that there are lots of good reasons to keep transactions very short, but that does not necessarily translate to a throughput gain.

    I was certainly not trying to argue that short transactions are a sufficient condition for high throughput and I apologise if that is how my comment read.

    However, in my experience of tuning (Sybase based) trading systems one of the most effective things you can do is minimise the transaction windows and intelligent use of stored procedures can be an effective way to help achieve this.

    --Robert
  45. TPS[ Go to top ]

    We could go back and forth. For example, Sybase tends to avoid pessimistic (blocking) locks as much as possible, so regardless of MVCC support, it's possible that the condition you suggested would never occur even with long-running transactions (assuming they are not coordinated 2pc).

    Can you elaborate on this? Are you referring to the threshold for promotion from a page (or row) level lock to a table lock?

    I'd have to go back to my notes, but my recollection is that we didn't see any actual blocking locks until the application started propagating the relevant inserts/updates/deletes to the database. Since Sybase implements locks at a page level, at the isolation level (read committed) being used, those updates would only block other modifications to the same pages, so the lock duration did not impact TPS significantly.

    You are correct that stored procedures would likely significantly reduce the length of time that those locks were held, but considering an application's typical tx mix, it will likely only make sense to use sprocs when there is actual observed contention.

    Further, in the Sybase case, the contention can be largely avoided by a number of 'hacks', such as forcing less rows per page and using a phyiscally ordered b-tree index (Sybase calls it a clustered index) on a non-obvious (e.g. nonsensical) value, thus handling well-known problems such as sequential inserts by distributing them randomly across the entire b-tree.

    At any rate, it's been several years since I've worked on projects at that level, but as I have been typing my response to you, I have realized that there are obvious cases in which you'd be correct. On Sybase, the heavier the write to read ratio, the higer the isolation, and the more concurrent transactions to total page (in a table) ratio, the higher the TPS from shortening the modification portion of the transactional cycle. For a trading system, the number of concurrent users can be quite high, and symbol-based tables (as one example) will tend to have a very high user-to-page ratio.
    I was certainly not trying to argue that short transactions are a sufficient condition for high throughput and I apologise if that is how my comment read. However, in my experience of tuning (Sybase based) trading systems one of the most effective things you can do is minimise the transaction windows and intelligent use of stored procedures can be an effective way to help achieve this.

    I think it's important for architects and developers to clearly understand the concepts of scalability and performance, and how those relate to latency and throughput. Unfortunately, building highly performant, massively scaled systems is still viewed as a black art, and there are a lot of things that we can do to change that. I would like to see an industry in which all server-based applications are being architected for high scalability, high performance and high availability, even if in their initial roll-outs they are only running on a single commodity server. With the proper care up front, there is little additional work necessary to ensure that an application can scale out nicely, and with the technologies available to Java developers today, these applications can achieve extremely high levels of availability and reliability with relatively minute investments.

    Peace,

    Cameron Purdy
    Tangosol Coherence: Clustered Shared Memory for Java
  46. TPS[ Go to top ]

    I would like to see an industry in which all server-based applications are being architected for high scalability, high performance and high availability, even if in their initial roll-outs they are only running on a single commodity server.
    Or we can just use Coherence. :)
  47. TPS[ Go to top ]

    Or we can just use Coherence. :)

    Well, exactly...
  48. In this article author talk about puting business logic in database. It is possible to use R/O approach without need to store business logic into database. Try http://dods.objectweb.org framework. It starts from database model and generate objects through which application can persist data, but business logic is still in Java code.
  49. There are many good reasons to use and not to use stored procedures. It is possible to misuse any stuff, if you failed with stored procedures then it can be not a procedure fault too.
  50. Distributed Transactions[ Go to top ]

    According to the article, RO/Amber would leave all transaction management to the database. That's great, if you only have to deal with a single database and no other transactional sources like JMS queues, etc. Can RO systems work in a JTA world?
  51. Classic example of "do not do it yourself". Terrible design. Enough reasons were, already, named.
  52. An unwritten consensus in the industry is that the best approach in solving these problems is a process where the business requirements are modelled in the object domain and where the resulting object model is mapped via an O/R mapping framework into the relational database system.

    I have no way of telling whether or not this consensus is true, but what I find when I use quality ORM tools is that they are actually largely agnostic about where the data model is defined, with powerful two-way mappings between relational schemas and a Java object model. The objection that changes in ORMs need to be performed in both classes and mapping files out of date. EJB 3.0 Annotations solve this problem, and I have my own Annotation-based metadata-generating system for JDO 2.0. Avoiding the duplication of changes is easy.

    My real problem with the approach suggested in the article is the implementation of the middle tier as stored procedures. Stored procedures are the least portable aspect of SQL. I value portability hugely. I am currently developing code that uses HSQLDB, PostgreSQL for development and Oracle for deployment. Switching between these database is trivial using an ORM (well, at least the way I am using one). Maintaining common stored procedure functionality in these databases would be far from easy.
  53. The objection that changes in ORMs need to be performed in both classes and mapping files out of date.

    On how menu places column change is reflected in database centric systems ;) ? Tens or even hundreds?
  54. Might be useful for some hard-to-imagine situations, but generally just brings to mind the old saying: Just because you can do something, doesn't mean you should.
  55. We all know that object->relational mapping works smoothly when you're designing a new system, but in case of migrating some large, old apps (RPG, COBOL... ;>) to modern OOD platform it's often really difficult (even impossible if you care for final performance) to obtain a consistent object model from untidy production database which has evolved over years as user requirements changes. The concept of R/O mapping also seems to be sensible when building some strongly data-driven systems which performance is mostly dependent on the quality of database schema and RDBMS final tuning.

    Most of the opinions are generally negative, but... we can't also forget that the proposed design of data layer seems to be widely used in .NET which creates a harmonious pair with SQL Server - an intensive usage of stored procedures is a popular practice there and even the most advanced hibernate junkies have to admit that the general problem of choosing between different O-R mapping concepts has no one anwer. The best proof is that some popular tools already support O/R, R/O and transitional mappings just out-of-the-box, ie. Rational Application Developer.

    Regards,
    Michal
  56. Really Nothing new...[ Go to top ]

    It's just a hack when someone uses legacy schema from mssql.
    I did it too. For examle, simple library based on Spring JDBC templates

    List<GoodsItem> list = new ReflectListStoredProcedure<GoodsItem>(
    "ektrd_scorp_GoodsSelectList", GoodsItem.class).query(goodGroup, likeString);

    But I just use reflection of column names.
    Parameters inserted by their index

    ...select Goods_ID as [id], GoodsName as [name]...

    But I don't think my hack worth some "whitepaper" echo.
  57. Why stored procedures?[ Go to top ]

    Why do you use stored procedures? The example procedures seem to be far too short to provide any performance gain? I looks like you just move the DAO objects into the database.

    Using stored procedures will make you RDBMS vendor dependent, which is a big problem today (unless you are in the Microsoft area).

    Why do you need annotations? Wouldn't it be possible generate the Task class based on database metadata. If the approach is R/O instead of O/R, the classes could strictly follow the tables. Imagine in a legacy application with 400 tables, how much work a generator would save.

    Fredrik Bertilsson
    http://butler.sourceforge.net
  58. IMO the most important reason to do NOT do it is the SCALABILITY.

    Relational DBs still can NOT scale HORIZONTALLY at all. Maybe Oracle 10g, but still its very application specific.

    As you remember in 90s we had client-server architecture, which could not scale more than 200-500 users, just because the business logic was runing in the DB (Informix4GL).

    This is the reason we have application servers, which are trying to take as much work as possible from DB machine to app server machines.

    Just pure load balancing with out session failover offers almost infinite horizontal scalability.

    Stored procedures are still good if you have some perfromance issue, but you should use them in minimal way.

    IMO for any bigger application your DAOs should be implemented like that:
    - OR mapping - 60%-70%
    - JDBC - 30%-25%
    - Stored p. - 10%-5%
  59. Yes, there are not so many good use cases for stored procedure. Stored procedure is a good way to extend SQL, but plain SQL with predefined functions must be fine in 99% of cases and probably there are no reasons to use procedures for home page,blog of forum type web applications.
  60. Stored Procs[ Go to top ]

    Yes, there are not so many good use cases for stored procedure. Stored procedure is a good way to extend SQL, but plain SQL with predefined functions must be fine in 99% of cases and probably there are no reasons to use procedures for home page,blog of forum type web applications.

    Juozas, MSSQL programmers often use Stored Procedures because MSSQL does not support prepared statements (in the Oracle, DB2, etc. sense). So a MSSQL stored proc is a prepared statement, in a way.

    Peace,

    Cameron Purdy
    Tangosol Coherence: The Java Data Grid
  61. Stored Procs[ Go to top ]

    MSSQL driver generated stored procedure for prepared statements, you do not need to implement stored procedures to use prepared statements with MSSQL. But stored procedure are usefull in many ways, it can be used as parametrized view "SELECT * FROM myProc(?)" and it makes sence to implement logic shared by many applications using stored procedures, but it makes sence for systems. Enterprice home page applications do not need any stored procedures.
  62. Stored Procs[ Go to top ]

    Juozas, MSSQL programmers often use Stored Procedures because MSSQL does not support prepared statements (in the Oracle, DB2, etc. sense). So a MSSQL stored proc is a prepared statement, in a way.
    Not sure what you mean here. I see the SqlCommand[1] class in ADO.NET does provide a Prepare method. The example in that page prepares an insert statement. Am I missing something?

    [1]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlCommandClassPrepareTopic.asp
  63. Stored Procs[ Go to top ]

    Juozas, MSSQL programmers often use Stored Procedures because MSSQL does not support prepared statements (in the Oracle, DB2, etc. sense). So a MSSQL stored proc is a prepared statement, in a way.
    Not sure what you mean here. I see the SqlCommand[1] class in ADO.NET does provide a Prepare method. The example in that page prepares an insert statement. Am I missing something?[1]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlCommandClassPrepareTopic.asp

    I am not sure about all MSSQL versions, but it doe's not support prepered statements in protocol directly and some drivers fake it using stored procedures internaly.
  64. Stored Procs[ Go to top ]

    Go here and read the first line -
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlCommandClassTopic.asp
  65. Stored Procs[ Go to top ]

    Go here and read the first line - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlCommandClassTopic.asp
    The first line says: "Represents a Transact-SQL statement or stored procedure to execute against a SQL Server database."

    So?
  66. Stored Procs[ Go to top ]

    Exactly. Proves Steves point.

    It doesn't prove that the code you pointed to is doing prepared statements.
  67. Probably there is nothing wrong in ROM, but I see this article is a bit lame:
    Connector connector =
       ConnectorFactory.getInstance().createConnector( "starwars" );
    BeanReader jediReader =
       new BeanReader( Jedi.class, connector );
    BeanReader fighterReader =
       new BeanReader( Fighter.class, connector );
    Collection<Jedi> jediList =
       reader.executeCreateBeanList( "select * from jedi" );
    for( Jedi jedi : jediList ) {
       String query =
          "select * from fighter where jedi_id = " + jedi.getId();
       Collection<Fighter> fighters =
          fighterReader.executeCreateBeanList( query );
       jedi.setFighterList(
          new ArrayList<Fighter>( fighters ) );
    }
    I hope author doe's not write this kind of code in his applications and it just a bad data access code example.
  68. BTW there is annotation based "framework" in JDBC 4, it is a bit experinetal, but it is not as lame as this code.
  69. Nice more R/O![ Go to top ]

    Funny I have been working on a similar project lately!

    It is great to see projects like iBatis and Amber emerging. In general I think R/O mapping is more natural than O/R mapping as chances are that your SQL will live longer than your code and will work on more platforms than just Java.

    I dissagree though with the transaction and buissness logic in database aproach. Application servers provide a much more apealing solution to these.

    I hope to be able to release my work sometime in the future too. My take has a hibernate-like API with an iBatis functionality. I try to get the best of these worlds.
  70. Nice more R/O![ Go to top ]

    ORM implementations like hibernate are two way mapping engines and it is up to you to map object to table or table to object.
  71. Nice more R/O![ Go to top ]

    In general I think R/O mapping is more natural than O/R mapping as chances are that your SQL will live longer than your code and will work on more platforms than just Java.

    I don't understand this. If you take this approach, then SQL IS your code, and there isn't a single SQL standard that is fully supported by all of the major database vendors. We don't have SQL portability now, let alone on the range of platforms where we gave the guaranteed compatibility of J2SE and JEE.
  72. Nice more R/O![ Go to top ]

    SQL is not so bad as you think, there are many ways to use plain SQL without portability problems. Probably some QL makes query string shorter, but there are SQL parsers to transform query strings and this SQL is as portable as home made QL string.
  73. Nice more R/O![ Go to top ]

    SQL is not so bad as you think, there are many ways to use plain SQL without portability problems. Probably some QL makes query string shorter, but there are SQL parsers to transform query strings and this SQL is as portable as home made QL string.

    We have discused this before. You can only use minor subsets of SQL portably, and there is very little portability at all for PL/SQL, which is the version being considered here (try writing the same PL/SQL for Oracle, PostgreSQL and SQL Server, for example). SQL is not as portable as 'home made QL' strings because, as we have discussed in detail in previous threads, such query languages as JDOQL are guaranteed to have all features implemented, no matter what the platform or the features of the underlying query language.

    If you doubt this, I can refer back to previous threads where we discussed this :)
  74. Nice more R/O![ Go to top ]

    http://www.swissql.com/products/sqlone-apijava/sqlone-apijava.html#SwisSQLSupportedDatabases
  75. Nice more R/O![ Go to top ]

    http://www.swissql.com/products/sqlone-apijava/sqlone-apijava.html#SwisSQLSupportedDatabases

    What is the point, when there are java standards for ORM languages and companies have had years of experience producing optimised SQL for a range of databases? That product does not support modern database products such as PostgreSQL 8.x and MySQL 5.x. Why should I choose a single SQL translation product when with JDO 2.0 and EJB 3.0
    I have vendors like BEA and Oracle providing translation of query languages to high-performance SQL?

    Also, this thread has been about where you put the business logic - in Java code, or in stored procedures. If you are going to put the logic in the database, you will have problems with portability because of the widely differing SQL dialects used for stored procedures - a problem that that product does not deal with for any but a few databases.

    One advantage I can see to the product is the 'Oracle -> Java' stored procedure conversion. As their website says:

    "Hence, translating the existing PL/SQL procedures to Java offers the developers code maintainability, portability, flexibility of deployment and numerous other benefits. It is also easier to find Java programmers than PL/SQL programmers thus keeping the cost of building and maintaining enterprise applications under control."

    My view exactly :)


    Sorry, but even if it did work, relying on a single vendor to long-term provide compatibility does not appeal to me.
  76. Nice more R/O![ Go to top ]

    It is fine if you favorite ORM product vendor provides database migration tool with ORM, but you can use any database migration tool to solve this problem without ORM too.
    In short it not a problem to solve for ORM.
  77. Nice more R/O![ Go to top ]

    It is fine if you favorite ORM product vendor provides database migration tool with ORM, but you can use any database migration tool to solve this problem without ORM too.In short it not a problem to solve for ORM.

    ORM vendors aren't providing database migration tools. They are providing tools which allow two-way mapping between relational stores and object models. Migration is a secondary (and to me highly useful) feature of these tools because of the portable query languages they include. (Of course, you can use them with vendor-specific SQL as well).

    If there is a database migration tool that will allow me to effortlessly port between Oracle PL/SQL and stored procedures in SQL Server, DB2, PostgreSQL, MySQL and others, so I can maintain a single codebase I might be interested. Until then, I will maintain that this definitely is a problem that ORM can help with, and is problem that the R/O approach does not address.
  78. Nice more R/O![ Go to top ]

    There is a standard language for stored procedures, it is very similar to DB2 stuff (C and JAVA can be used to implement stored procedures too), but I do not care about stored procedure portability.
  79. Nice more R/O![ Go to top ]

    There is a standard language for stored procedures, it is very similar to DB2 stuff (C and JAVA can be used to implement stored procedures too), but I do not care about stored procedure portability.

    A standard is irrelevant if no-one implements it.

    You may not care about stored procedure portability, but I do, and so do thousands of other developers who have seen code tied in too much to one particular vendor leading to huge problems and expense when circumstances call for migration. This is an important issue.
  80. Nice more R/O![ Go to top ]

    How do you write stored procedures if you care about portability ? Can you avoid stored procedures and solve all problems using JDOQL ?
  81. Nice more R/O![ Go to top ]

    How do you write stored procedures if you care about portability ? Can you avoid stored procedures and solve all problems using JDOQL ?

    This is the point of much of the discussion of this thread. If you have your business logic in Java, you can avoid problems that can arise from the use of stored procedures: lack of portability being one of them.

    The use of JDOQL or any other portable query language is a minor part of the question. Just as stored procedures consist of more than just select statements, ORM-based Java business logic consists of more than just JDOQL/EJBQL statements.
  82. Nice more R/O![ Go to top ]

    I do not advocate procedures and I try to avoid this stuff, but it is hard to live without stored procedures and triggers. This stuff helps in many cases, but it is more exception than rule. Ideal relational model doe's not need procedures, but procedures are usefull in some exceptional cases for performace reasons.
    Some developers use stored procedures for security reasons, but it is possible to develop secure data access code without procedures too.
  83. Nice more R/O![ Go to top ]

    It is great to see projects like iBatis and Amber emerging. In general I think R/O mapping is more natural than O/R mapping as chances are that your SQL will live longer than your code and will work on more platforms than just Java.
    Typically, everything needs to be recoded because the data structure changes too.

    Using OR vs RO allows the data structure to change, as needed, without alot of SQL and Domain changes. Of course, the amount domain changes depends on the level and type of data structure changes.
  84. It is sometimes hard to argue with DBAs and Stored Proc lovers. They have a long list of defences against the people who disagree.

    I had a tiffie with one of the new guys in the office about stored procs. He argued that everything should be a stored proc and you should never write an adhoc query to the database, nor should you ever use an ORM.

    You would get the typical list of great things, like performance proper db schema protection, DBA can now worry about the database, security, etc...

    I argued that I use stored procs only to do work inside the database to do some heavy administrative lifting and the sorts. Sometimes I solve a performance problem with it, but I rarely have to.

    I also argued that I would not push logic to the database because I can scale the stuff better on a cheap web server if I have to - why burden the database. His answer was that if the stored proc is faster why would offloading make it scale better. I then showed that the stored proc is not faster with a simple demonstration of repeated calls to the database with a prepared statement - no difference.

    His view of the world always started from the database. Database was the center of the universe, and he would always start building his application by writing stored procs for everything - hundreds of them over time. This included application integration in terms of shared views across databases, central integration databases etc etc.

    It is a totally different world :)
  85. ...He argued that everything should be a stored proc and you should never write an adhoc query to the database, nor should you ever use an ORM...

    Yes, why should you be able to write adhoc queries? Dynamic reporting would never be needed in any application would it?

    Funny how often the concept of speed=performance gets misused. Even on a current project a Java Stored Procedure that does XML parsing is argued for against doing the same thing on a server. Who cares if a single transaction is faster? I want to know how 100 transactions/second will act.

    Maybe I'm still carrying the old fashioned ideas from the mid 90s but I was always taught that it is easier and cheaper to scale the middle tier than the database tier. Are all those pyramid diagrams with the web-server farm as the broad base and the DB at the pinnacle no longer valid?

    I don't think ROM can be used fully in a large cooperate environment. Most of the DBAs I've worked with do their utmost to keep the database code-free. For them programmers are on the other side of the wall and should remain there. Even if I use Java Stored Procedures over yucky PL-SQL so that I can acutally have libraries to program against, do I really want to load Spring, JDOM, Apache stuff, etc into my Oracle process? Where is my PL-SQL rules engine?

    On the other hand, early in my career there was this concept called client-server which worked well for some projects. We'd write a bunch of specialized reporting stored-procedures and a VB or Access front end. Up to 40 employees were able to use that application at the same time!
  86. The fundamental problem with this approach is that you are no longer doing OOP. You may be using an OO language for part of your code, but your core business logic is no longer in your object model. This has disastrous consequences for the maintainablity of a real system. The key basic benefit of OO is encapsulation: having the logic and data in the same place. With an RO approach this is lost. You might as well be programming in C or VB3.
  87. The fundamental problem with this approach is that you are no longer doing OOP. You may be using an OO language for part of your code, but your core business logic is no longer in your object model. This has disastrous consequences for the maintainablity of a real system. The key basic benefit of OO is encapsulation: having the logic and data in the same place. With an RO approach this is lost. You might as well be programming in C or VB3.
    OOP is not so far from procedures and probably most of JAVA developrs use JAVA as procedurial anyway. More "fundamental problem" is that you are no longer doing relational data access.
  88. OOP is not so far from procedures and probably most of JAVA developrs use JAVA as procedurial anyway.

    This is a very broad statement. As someone with a Smalltalk background I think you are fundamentally mistaken about the first part of the statement, and I think the second part requires considerable evidence...
    More "fundamental problem" is that you are no longer doing relational data access.

    Yes you are. If you use the R/O approach, you are certainly using relational data access (the 'R' in R/O). If you use ORM, you are also using relational data access (the 'R' in ORM).
  89. I was talking about "stored procedure" approach, it is not very relational (relational query languages like SQL are more declarative than imperative), sorry if it was out of topic, but this "R/O" approach is just boring and a bit lame and I thought we changed topic :)
  90. I was talking about "stored procedure" approach, it is not very relational (relational query languages like SQL are more declarative than imperative), sorry if it was out of topic, but this "R/O" approach is just boring and a bit lame and I thought we changed topic :)

    Sorry - I misunderstood you!

    If you don't like stored procedures, how (where?) do you implement business logic?
  91. Sometimes I use stored procedures for constraint triggers (very exeptional case, denormalization hacks) It is usefull for external scripts and integration for security reasons. "business logic" is not well defined term, but prefer to use client side application logic and plain relational database model (with some exceptions). Stored procedures are very usefull in exeptional cases, but it limits relational model.
  92. Sometimes I use stored procedures for constraint triggers (very exeptional case, denormalization hacks) It is usefull for external scripts and integration for security reasons. "business logic" is not well defined term, but prefer to use client side application logic and plain relational database model (with some exceptions). Stored procedures are very usefull in exeptional cases, but it limits relational model.

    So you wouldn't even have it server side?
  93. If it is possible to avoid stored procedure then it must be better to avoid it, procedure indicates is a limitation in relational model. And it is very wrong to implement some logic like data formating on server (web server is a client in this case) data can be viewed in many ways by clients, procedure limits client. Client specific procedures probably makes sence for security reasons only in paranoid systems to limit client.
  94. OOP is not so far from procedures
    I'm not even sure how to answer that. That's like saying object oriented programming is not so far from programming. While I suppose in some sort of way you are correct, it still seems like missing the point.
     and probably most of JAVA developrs use JAVA as procedurial anyway.
    Sadly, I have to agree that a large percentage of java developers write procedural code. This doesn't mean it's a good way to build maintainable systems.
     More "fundamental problem" is that you are no longer doing relational data access.

    Umm... huh?
  95. Sadly, I have to agree that a large percentage of java developers write procedural code. This doesn't mean it's a good way to build maintainable systems.
    Probably it is better to write procedural code than to missuse OOP. Procedural code is not so bad in many applications.
  96. Sadly, I have to agree that a large percentage of java developers write procedural code. This doesn't mean it's a good way to build maintainable systems.
    Probably it is better to write procedural code than to missuse OOP. Procedural code is not so bad in many applications.
    Isn't writing procedural code in an OO Language a misuse of OOP?
  97. It is not, JAVA ia procerial language with OOP features. Nobody forces you to use JAVA OOP features :)
    I was talking about "declarative" (procedures) vs "imperative" (queries), procedurial and OOP languages are very similar in this context.
  98. "declarative" (procedures) vs "imperative" (queries), " Sorry, it is opposite.
  99. It is not, JAVA ia procerial language with OOP features. Nobody forces you to use JAVA OOP features
    I didn't mention Java. We were just talking OO.
  100. Where are the OO concepts?[ Go to top ]

    "[Amber] assumes that the ER model is the reference for resulting OO structures".
    This is interesting. The OO development paradigm was originally developed, because the busines world is inherently object oriented. Our understanding of the world, or better our capabilities to abstract the business world into usuable software, is greatly enhanced using an object-oriented model. Anybody who has developed software (business) using procedural approaches ran sooner or later into unavoidable problems (hence the creation of the OO paradigm). So my question here is: how can Amber assume the ER model is the reference for a resulting OO structure, if a relational model is based on set theory? Where is polymorphism, generalization, and encapsulation? Set theory simply does not deal with those capabilities, so no matter how good the database, the resulting software is procudural with all the problems that the software engineering community has been trying to solve!
  101. Where are the OO concepts?[ Go to top ]

    This is interesting. The OO development paradigm was originally developed, because the busines world is inherently object oriented. Our understanding of the world, or better our capabilities to abstract the business world into usuable software, is greatly enhanced using an object-oriented model.

    No, I don't believe so. OO was developed because software engineers deal with the complexities of managing both state and behavior, and OO could combine both concerns into a single programming model.

    Peace,

    Cameron Purdy
    Tangosol Coherence: Clustered Shared Memory for Java
  102. Where are the OO concepts?[ Go to top ]

    ...OO was developed because software engineers deal with the complexities of managing both state and behavior, and OO could combine both concerns into a single programming model

    For whatever reason it might have been originally developed, it just so happens, that the world is object oriented and consists of object with attributes and behaviour. Not of records with triggers on delete, create, and modify and certainly not with cascade option or we'd all be gone.....
  103. Where are the OO concepts?[ Go to top ]

    ...OO was developed because software engineers deal with the complexities of managing both state and behavior, and OO could combine both concerns into a single programming model
    For whatever reason it might have been originally developed, it just so happens, that the world is object oriented and consists of object with attributes and behaviour. Not of records with triggers on delete, create, and modify and certainly not with cascade option or we'd all be gone.....

    Really?

    I thought everything in the world was just an element in a list being operated on by a higher order function.
  104. maybe[ Go to top ]

    Actually it is just a bunch of random funtions that just happened and it is just survival of the non garbage collected.
  105. Where are the OO concepts?[ Go to top ]

    ...OO was developed because software engineers deal with the complexities of managing both state and behavior, and OO could combine both concerns into a single programming model

    For whatever reason it might have been originally developed, it just so happens, that the world is object oriented and consists of object with attributes and behaviour. Not of records with triggers on delete, create, and modify and certainly not with cascade option or we'd all be gone.....

    Really? I thought everything in the world was just an element in a list being operated on by a higher order function.

    I'm not worried about the closures, because I have faith in continuations ;-)

    Peace,

    Cameron Purdy
    Tangosol Coherence: The Java Data Grid
  106. The basic problem is that Java was simply not designed with database processing in mind. Database support like JDBC was always a patchwork which unfortunately was accepted as The Bible by the community. As for relational databases, they do have serious drawbacks in implementing Object Oriented models, which was overlooked during the heydays of relational technology. When OO became popular, it was very clear that relational databases were a big mismatch with OO model. Attempts were there to patch again with products like Illustra... Databases based on E-R model were better suited to implement any object oriented structure... but it was too late and there were no such databases available in the new world of Unix and Windows.

    Today it makes pathetic reading when one see that a popular database like mySQL did not support stored procedures until now. And one reads again "MSSQL does not support prepared statements " Where do we stand on database technology now?

    Stored procedure is not a panacea.. though it is heavily used by all relational databases. Originally when SQL based systems were introduced in the mid 1980s, the claim was that, set-based SQL language was powerful enough to do anything in the world. But coding a mathemtically powerful was not easy, understanding it was even more difficult. Soon, client server computing forced the databases vendors to hide the complexities of SQL in the stored procedure. They wanted to reduce the traffic across application domain and the database domain! All they achieved was to hide the complex procedural code from the front-end programmer...

    How do we access a database system? Logic sharing across multiple domains or tiers can be disastrous. Java based applicatin development will be heading for a disaster unless we put a stop to buzzwords and start concentrating on a minimum working set. Else Microsoft will have their task simplfied!
  107. RDBMS is designed in PL neutral way and it is more a feature than a drawback. "mismatch with OO model" is very minimal and it not so important in practice if you know how to map ER model to relational model. OO model is a bit extended ER model with "hidden" attributes http://www.cs.sfu.ca/CC/354/zaiane/material/notes/Chapter1/node8.html there are some trivial differences, but there is no mismatch. ER or OO model is informal model and there is no way to define deterministic query language based on this model and this kind of databes failed for this reason.
  108. As for relational databases, they do have serious drawbacks in implementing Object Oriented models, which was overlooked during the heydays of relational technology. When OO became popular, it was very clear that relational databases were a big mismatch with OO model.

    I think this point is hugely exaggerated. I am right now running software that stores and retrieves large volumes of data, based on an object model, into a relational database. It works fine. Using a good quality commercial ORM, I don't find performance issues. In fact, the ORM-based system is replacing a hand-coded SQL approach, with no performance loss. I am able to easily map object models to legacy schemas, so that a DBA can work with my data without having to take into consideration my accessing of the data using an ORM.

    Maybe it is just a reflection of the quality of the tools I am using, but I don't, in practice, see much of mismatch.
  109. Java Stored Procedures[ Go to top ]

    You can write testable and reusable code by writing stored procedures in Java. The negative is that not all databases support this feature (we have great success using that with PostgreSQL and PL/Java). The benefit of this approach is that all the database logic resides in regular Java classes that use straight JDBC. The database logic can also be quite easily moved into the business processing layer (application server) by changing the connection source (easily abstracted through a Factory).

    This of course does not alleviate the scalability issue. The more logic you implement in the database, the more powerful your database server needs to be.
  110. It's the app guys against the db guys, once again. How about if we leave the app guys to develop the app, and the db guys to develop the db?
     
    As for the whole o/r mapping - Norbert is right on. Anyone who has coded large apps, knows that the primary driving force behind the analysis of requirements is the data. Likewise, the data must be modeled first. The data model will eventually support many applications and therefore must be normalized so that you can continue to build on it without constantly changing the model. Thus you have R/O mapping - the R comes first.
     
    XML is a scourge. If limited to data validation, when communicating between multiple, desperate apps - fine. Terrible for data storage and terrible for configuration, though I see it used frequently for both, and invariably the db developers have to go in and repair the mess. So it's good to see Amber.
     
    Stored Procedures are beautiful and they serve multiple purposes, very well. First, they let the database do what it's good at - they encapsulate SQL and allow db developers to do what they are good at - code database. They let the db engine pre-parse queries for reuse. They allow queries to be modified, on the fly, without bringing down the app. And most of all they abstract the data layer from the application team - who, from reading this thread, do not understand the db very well and therefore must be abstracted. SPs allow the database to expose an interface that the app team must comply with - and prevents the app guys from writing any sql - which they are notoriously bad at. And good lord - why would you ever want to let an app developer muck around with transactions? How many times have we seen them screw this up? The sp interface is easy to write tests against. And the SPs themselves are easily written and maintained by an experienced db group.
     
    Business logic in the database? When it makes sense - do it. This goes back to the client-server paradigm. One way of scaling is to balance the load across the tiers. Don't worry about the db scaling - the db team will take care of it. Just make sure that the app guys work with the db guys - and not in isolation.
     
    Portability? We spent close to a half million on db software, last year. Do you think we are going to port to something different? No one in their right mind would do that. Portability is an issue for the academics - not a real world problem.
     
    So what did we learn?
    1. let each team do what they are good at.
    2. don’t work in isolation
    3. don't let app guys have anything to do with the database, except query it (the db team writes the queries).
  111. rtability? We spent close to a half million on db software, last year. Do you think we are going to port to something different? No one in their right mind would do that. Portability is an issue for the academics - not a real world problem.

    Just because it is not a real world problem for you (yet) does not mean it is not a real world problem for many others. I am dealing with such problems right now, so I find your suggestion that it is merely 'academic' rather out-of-touch.

    There are many reasons why developers could still be sane and want portability.

    It allows database users to tender for alternative vendors when systems are being upgraded.

    It allows application vendors to produce products which give the customer freedom of choice about the database.

    It allows a much more flexible approach to development. Developers can prototype and test much of an application on lightweight or even free/open source databases before the final stages and deployment on more substantial products.
  112. Development is not so important task in "information system life cycle", probably it is less expensive to clone or to rewrite yet another "accounting system" than to retrain admins and to make politcal decisions and technical decisions, you need to migrate data anyway. Concurrency control implementation, performance are not equal too. IMO procedure portability doe's not make sence for "heavy weight" systems, "light weight" stuff do not need any procedures, it can use embedded database and any data access stuff for convinence or portability.
  113. Development is not so important task in "information system life cycle", probably it is less expensive to clone or to rewrite yet another "accounting system" than to retrain admins and to make politcal decisions and technical decisions, you need to migrate data anyway. Concurrency control implementation, performance are not equal too. IMO procedure portability doe's not make sence for "heavy weight" systems, "light weight" stuff do not need any procedures, it can use embedded database and any data access stuff for convinence or portability.

    Sorry, but my real-life experience directly contradicts this. I am right now involved in porting a medium-scale system that is stuck on a legacy system which is barely able to support it. This is a very long and costly process (especially as the system can't be taken down). One of the reasons we are now using ORM is to ensure that we don't end up trapped on yet another vendor-specific product.

    Migration of data is not a significant problem if you have a portable object model. Many ORM products come with tools to automate the process.

    You may have a point if you are talking about extremely large database systems, which require major administration and support tasks, but the majority of uses of databases aren't on that scale. However, even the smaller scale of projects can involve trying to port hundreds of thousands of lines of vendor-specific stored procedure code. The developer effort involved in porting this vastly exceeds any "retraining costs for admins", which is insignificant at this scale.

    Databases are not only mainframe-scale or embedded!
  114. I have worked with legacy systems too, but we have never ported or migrated legacy systems. It was integration in all of cases (JMS or custom stuff). Typical frontend never communicates with system using SQL (some of backends are not SQL databases anyway). Probably it is too expensive to drop and to buy new harware and software with support contracts and to retrain army of admins, stored procedure convertion form PL1 to PL2 must be trivial compared to this process.
    "Frontend databases" probably do not need any procedures (I use procedures and triggers for very exeptional cases). It can be an embedded database in theory, but I do not know any good embedded database and use servers just for this reason.
  115. I have worked with legacy systems too, but we have never ported or migrated legacy systems. It was integration in all of cases (JMS or custom stuff). Typical frontend never communicates with system using SQL (some of backends are not SQL databases anyway). Probably it is too expensive to drop and to buy new harware and software with support contracts and to retrain army of admins, stored procedure convertion form PL1 to PL2 must be trivial compared to this process. "Frontend databases" probably do not need any procedures (I use procedures and triggers for very exeptional cases). It can be an embedded database in theory, but I do not know any good embedded database and use servers just for this reason.

    You should re-read my posts. I am not in a situation that needs armies of admins and major support contracts, or expensive hardware changes. Procedure conversion is not trivial compared to the training of admins. You may have never ported legacy systems, but many of us have. Your situation is not ours, and I'm sorry, but I really don't think there is any point in commenting unless you are going to accept that.
  116. It looks Matt like talks about this stuff. I am sure there are many specific situation and it needs specific solution. I do not argue, I just share expierence.
  117. It looks Matt like talks about this stuff. I am sure there are many specific situation and it needs specific solution. I do not argue, I just share expierence.

    My view is that too often there are discussions of specifics that miss out on the general situations that thousands of developers are in. I believe that ORM is a general solution that works in a much wider range of situations than is often realised. For example, against the advice commonly put forward about ORM use, I use it for batch processing, with transactions consisting of hundreds of thousands of records. I find ORM highly effective and fast for this use; matching the performance of the hand-coded SQL it replaced. I may be proved wrong, but I think that labelling ORM as a specific solution for specific situations is to under-estimate a very powerful approach to handling data persistence.

    There is a quote attributed to Hoare and Knuth: "premature optimization is the root of all evil". I think this applies to persistence. Don't assume that you need stored procedures and hand-coded SQL for performance. Don't assume that you can't get portability. Don't assume that the often-mentioned "impedance mismatch" between OOP and relational stores will occur in practise. Use a quality ORM product the right way, along with a well-designed object model, and you will usually find that the amount of DB-specific optimisation is a lot less than you would expect. The potential benefits of this approach are huge.
  118. Don't assume that the often-mentioned "impedance mismatch" between OOP and relational stores will occur in practise.
    True. But using the ORM will allow for any mismatches and not force the db or OO code into each others model. Harking back to my electrical background, the mismatch is not something that cannot be solved. It just means there are two things that are not exactly alike and need some help to get along. And usually, in newly created systems, they are minor. ORM's provide impedence matching, where needed, and all the other things you mentioned.

    Maybe a better way to say it is ORMs provide a buffer. Doesn't sound so ominous.
  119. Don't assume that the often-mentioned "impedance mismatch" between OOP and relational stores will occur in practise.
    True. But using the ORM will allow for any mismatches and not force the db or OO code into each others model. Harking back to my electrical background, the mismatch is not something that cannot be solved. It just means there are two things that are not exactly alike and need some help to get along. And usually, in newly created systems, they are minor. ORM's provide impedence matching, where needed, and all the other things you mentioned. Maybe a better way to say it is ORMs provide a buffer. Doesn't sound so ominous.

    A good way to put it. I should not have said that the impedance mismatch does not occur; I should have said that it will most likely not turn out to be a problem.
  120. How about if we leave the app guys to develop the app, and the db guys to develop the db?
    Because the db is part of my app?
    Anyone who has coded large apps, knows that the primary driving force behind the analysis of requirements is the data.
    Well, then they did it wrong. It is data and process. (Don't make me whip out my Systems Analysis and Design book. :) ).
    The data model will eventually support many applications and therefore must be normalized so that you can continue to build on it without constantly changing the model.
    Yes, the data model never changes. :) Well, it better not if you have multiple applications accessing it. Hope you never wish to cache anything other than in the data tier, and hopefully the other apps wish to cache the same things.
    XML is a scourge
    Yes, XML is often misused. But have you ever tried passing around comma, or otherwise, delimited files?
    Stored Procedures are beautiful and they serve multiple purposes, very well.
    Yes. First to lock you into a DB ...

    As for the rest of your comments in that paragraph, I suggest you re-read some of the posts here, especially Steve's and Camerons. Sorry, but "db developers/dbas" are often just as bad as "app developers".
    Portability? We spent close to a half million on db software, last year. Do you think we are going to port to something different? No one in their right mind would do that. Portability is an issue for the academics - not a real world problem.
    If your apps where portable, it [probably] wouldn't cost you so much. Nothing like being able to negotiate.

    And it has happened. I know of one the large telecom that [the last I knew, when I left] was switching from Oracle to DB2 because of partnership with IBM.
    So what did we learn?
    1. let each team do what they are good at.
    2. don’t work in isolation
    3. don't let app guys have anything to do with the database, except query it (the db team writes the queries).
    Hmmm. That is not what I learned from your post. :)

    1. True
    2. This conflicts with #1 and #3
    3. But what if that conflicts with #1? And it conflicts with #2
  121. Good point[ Go to top ]

    May not be so populair on a J2EE middle-tier site but in many cases it would be better to leave out the middle-tier all together.
    Using a stateless front-end, storing user preferences in cookies and encapsulating sensitive data inside the database.

    A domain specific language can be as transparent layer over the lesser convenient stored procedures and the duplication problem.

    Using hardware load balancing and database replication for optimalization and failover, fast and simple.