5 ms·

Amen to everything you and arnarbi wrote. However, I've come to dislike building business rules using the proprietary languages of the RDBMS vendors. In partic

by bpyne 9y ago

Amen to everything you and arnarbi wrote. However, I've come to dislike building business rules using the proprietary languages of the RDBMS vendors. In particular, I use Oracle and have been making a case with my employer to stop creating ties to Oracle by using PL/SQL as our house language. While it provides some efficiencies and can sometimes make coding easier, it can stifle development as easily. I'm all for expressing data relationships in the RDBMS and using efficiency mechanisms like indexes and cost-based optimization. However, I now prefer business rules to be expressed outside the database. [EDIT: Corrected anarbi to arnarbi.]

aNoob7000 9y ago
As someone working at a company that decided to write most of their SQL code at the application layer, there are definite tradeoffs. Our application performance suffers because of the fear of vendor lock-in and the flexibility others thought that writing code at the application level would provide. The question I pose to anyone consider this type of architecture is, how often do you switch database platforms?
- fulminatorz68 9y ago
  Never. You have sufficient expertise to make the solution work and enough exposure to the code base to modify core if necessary. Oracle: never an option.
- bpyne 9y ago
  The answer is almost never because the cost in time is too high for such a switch. It's the same question I posed 10 years ago. My view changed since coming to my current employer. I work for a moderate-sized university. The applications I support require up-to-date information in order to test modifications. They are read-intensive with few writes within the database. We can afford only one test database for the entire university. We refresh the database from production only once a quarter, at most. My applications would benefit greatly by being able to switch URL's to the production database in order to test a mod. Since our code, written in PL/SQL, gets stored in the database that it runs on, we can't do such a thing without significant effort: dblinks or modifying package names to store in production for testing or a test schema or whatever else you can think up. Another issue is that PL/SQL is not a robust language for modern development. Its type-system was designed for strictness, which is great in life-critical systems. But, there is no notion of inheritance and no ability to write generic collections. To mimic inheritance, you can create objects backed by tables and inherit one from the other. It has the feel of being bolted on and yet another tie to Oracle. One final issue is that PL/SQL is highly subject to the resource management of the Oracle kernel. Often, the kernel is extremely efficient. But, there are times when you need code running in a separate address space, preferably on a separate server. For instance, one application I wrote is a batch system that has a lot of processing rules built into it. Our database is tuned for OLTP. My application is categorized by the DBA's as data warehouse-oriented. Yet my application has to run hourly throughout the day and compete with resources necessary for the OLTP stuff. Putting it onto a separate server, away from Oracle's kernel management, would more than likely help a performance issue we have. So, the answer to your question is the one you were looking for. But, my concern isn't necessarily switching database vendors.
  - cmkrnl 9y ago
    I understand the cost of switching now. Was Postgres never an option? Do the uptime requirements call for RAC etc.?
    - bpyne 9y ago
      Our Banner student information system is tied to Oracle. My applications all go against Banner data. Given the licensing cost for Oracle and our current round of cost-cutting, if Postgres was an option, we'd probably take it.
      - jlgaddis 9y ago
        Several years ago at an .edu I had to deal with an upgrade from our current system to Banner. I truly feel for you. :)
- gcb0 9y ago
  postgre got a lot of adoption being a drop in replacement for mysql a while ago.

by bpyne 9y ago

aNoob7000 9y ago

As someone working at a company that decided to write most of their SQL code at the application layer, there are definite tradeoffs. Our application performance suffers because of the fear of vendor lock-in and the flexibility others thought that writing code at the application level would provide. The question I pose to anyone consider this type of architecture is, how often do you switch database platforms?

fulminatorz68 9y ago
Never. You have sufficient expertise to make the solution work and enough exposure to the code base to modify core if necessary. Oracle: never an option.
bpyne 9y ago
The answer is almost never because the cost in time is too high for such a switch. It's the same question I posed 10 years ago. My view changed since coming to my current employer. I work for a moderate-sized university. The applications I support require up-to-date information in order to test modifications. They are read-intensive with few writes within the database. We can afford only one test database for the entire university. We refresh the database from production only once a quarter, at most. My applications would benefit greatly by being able to switch URL's to the production database in order to test a mod. Since our code, written in PL/SQL, gets stored in the database that it runs on, we can't do such a thing without significant effort: dblinks or modifying package names to store in production for testing or a test schema or whatever else you can think up. Another issue is that PL/SQL is not a robust language for modern development. Its type-system was designed for strictness, which is great in life-critical systems. But, there is no notion of inheritance and no ability to write generic collections. To mimic inheritance, you can create objects backed by tables and inherit one from the other. It has the feel of being bolted on and yet another tie to Oracle. One final issue is that PL/SQL is highly subject to the resource management of the Oracle kernel. Often, the kernel is extremely efficient. But, there are times when you need code running in a separate address space, preferably on a separate server. For instance, one application I wrote is a batch system that has a lot of processing rules built into it. Our database is tuned for OLTP. My application is categorized by the DBA's as data warehouse-oriented. Yet my application has to run hourly throughout the day and compete with resources necessary for the OLTP stuff. Putting it onto a separate server, away from Oracle's kernel management, would more than likely help a performance issue we have. So, the answer to your question is the one you were looking for. But, my concern isn't necessarily switching database vendors.
- cmkrnl 9y ago
  I understand the cost of switching now. Was Postgres never an option? Do the uptime requirements call for RAC etc.?
  - bpyne 9y ago
    Our Banner student information system is tied to Oracle. My applications all go against Banner data. Given the licensing cost for Oracle and our current round of cost-cutting, if Postgres was an option, we'd probably take it.
    - jlgaddis 9y ago
      Several years ago at an .edu I had to deal with an upgrade from our current system to Banner. I truly feel for you. :)
gcb0 9y ago
postgre got a lot of adoption being a drop in replacement for mysql a while ago.