Share

Recently I was tasked with doing some investigations as to the best route to go. Now before you go getting all excited I am not going to be posting performance comparisons or declaring an outright winner. What I am going to point out is how to make the decision based on other factors.

As I was looking for feed back on the respective technologies it became very clear that this is a holy war that no one can win due to the emotional attachment to our egos and having to be right and the lack of really clear distinctions between the two.

First lets look at some basic best practise in writing maintainable software:

1. Make code readable
2. Use automated testing
3. Use version control
4. Ensure software is well designed
5. Use less code
6. Encapsulate
7. DRY – do not repeat yourself
8. Loose coupling
9. Write unit tests

This is the essence of what I feel the articles in the reference section encapsulate. The primary reason for writing maintainable code (asides from having to maintain it) is to facilitate change. Businesses are becoming more dynamic and cannot afford to wait for months or years for the implementation of a vision they had. First to market is more important than ever with smaller businesses finding it easier to compete due to software and the internet.

Now the generally preferred structure of a software application is view layer, business logic layer and data layer. If designed properly one can very easily attach multiple views for different platforms to the solution without having to reengineer the business logic. The data stores can also be swapped out with relative ease or perhaps extended to include other data stores.

So what is a stored procedure? According to wikipedia: “A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a proc, sproc, StoPro, StoredProc, or SP) are actually stored in the database data dictionary.”

Now the benefits claimed with using stored procedures have always been related to performance. It is a common belief that stored procedures run quicker than generated SQL. While this might be the case with an experienced writer, I have had the distinct displeasure of seeing it go horribly wrong as well. This does not mean that I have not seen it happen in code but generally it is easier to fix the code than the stored procedure due to the unit tests. When changing a stored procedure inevitably you are going to have to change code. When changing DB structure you will have to change all the procedures that use that dataset and the code that maps to it.

Now let us get out of the emotional stuff and start comparing apples with apples.

If we have a look at the the description above on how to write maintainable code let see how stored procedures match up.

1. Make code readable – Well no, it is a structured query language. While it looks like bad English sometimes it can be difficult to read.
2. Use automated testing – I haven’t seen a way to automate the testing of stored procedures
3. Use version control – I have not seen a way to handle versioning of stored procedures with ease
4. Ensure software is well designed – Being procedural in nature there is very little design that can happen
5. Use less code – There are certain things you can do in code you can’t do in SQL. So you might end up having to write far more SQL to facilitate it.
6. Encapsulate – While some might argue that the procedure is encapsulated in the database I would argue that the logic is not encapsulated where it should be.
7. DRY – do not repeat yourself – With having to name tables and operations continually there is a great deal of replication happening
8. Loose coupling – Can stored procedures be interchanged between database vendors? Well yes, if you haven’t used vendor specific functions. It is also tightly coupled to the database unfortunately.
9. Write unit tests – I would if I could! Haven’t seen this in Stored procedures.

I am not going to run the code through the same assessment as we all know that code supports all the above. Right lets get into the next point. While stored procedures might perform better, does the saving from the performance increase compliment the additional cost of maintenance attached by using stored procedures? The next question we need to ask is this. How safe is it to have business logic reside inside the database as opposed to the code base? What the you had specific rules for the same entities in a database? You would have to replicate the initial procedure and fine tune it for each entity. Now should the shared logic change you have multiple places you need to go and change. Not good!

I am not going to go into data parity mismatching either as that is a seperate article all toghether! Just know that it is there.

Lets look at it from the other side. Yes generating SQL to query a database has a certain amount of overhead. That is the only concern that people have. Let me say that again, the only con that using code over using stored procedures has is the performance aspect. So what do we do? Well lets have a look at another definition: “"Premature optimization" is a phrase used to describe a situation where a programmer lets performance considerations affect the design of a piece of code. This can result in a design that is not as clean as it could have been or code that is incorrect, because the code is complicated by the optimization and the programmer is distracted by optimizing.”

Is this not what we are doing when we allow the decision to use stored procedures affect our system designs? How about we try this from now on. Lets write the application first, get it working properly (even if it is a single feature) and release it. Once we identify bottle necks we begin to optimise the bottle necks. This might very well include using stored procedures! Lets get out of the dark ages folks. There is no right or wrong in this realm. There is only deliver on time or don’t. Lets deliver on time Perfection is generally a refining process any way, expecting it on the initial iteration is absurd.

Just to clear up any ambiguity. This article does not serve to prove or disprove the use of SQL and stored procedures. The point of the article is to make you aware that using stored procedures needs to be handled the same as writing code, carefully, with a liberal serving of caution and most of all making sure that what you are gaining is worth what you spending to gain it. Store procedures have their place, as do ORM, procedural languages and hot dogs (in my tummy!)

References:

• http://www.basilv.com/psd/blog/2006/how-to-create-maintainable-software
• http://www.advogato.org/article/258.html
• http://www.crazysquirrel.com/computing/java/basics/maintainable-code-part-1.jspx
• http://haacked.com/archive/2007/01/09/Writing_Maintainable_Code.aspx
• http://codebetter.com/jeremymiller/2006/12/06/on-writing-maintainable-code/
• http://en.wikipedia.org/wiki/Stored_procedure
• http://en.wikipedia.org/wiki/Premature_optimization#When_to_optimize

Tweet

Share

Ok here we go again. Now I am struggling get Hibernate working with the persistence unit declaration.

The reason I am writing this is more a pointer to myself should I ever have to do this again. Oh, check out my project on github. It is an implementation of a repository pattern using hibernate. It is extendable if you download the source and implement other providers. It is defined for standalone instances, not the full Java 5 EE stack although I am pretty sure with a bit of tweeking it can be used in that instance. Available here https://github.com/RabidDog/Java-Repository-Definition

First I was getting the dreaded "javax.persistence.PersistenceException: No Persistence provider for EntityManager named”. After a little testing I figured out that the properties file contained an inverted commas wrapped persistence unit name where it should not have been wrapped

datastore.database.persistanceunit = "PU1" -> wrong!
datastore.database.persistanceunit = PU1 -> resolved correctly.

Ok so yeah I am rusty but bare with me. After getting that right I started running into Unable to build EntityManagerFactory. Drilling down a bit further it came down to not having an initial context. So I went and manipulated the persistence.xml file to no avail. Then I started digging deeper and found a ClassNotDefined exception (doh!). Seems I had forgotten to include the Postgres driver jar file (this is one feature I really like in C#, if you reference an assembly that references another assembly you get a warning if you haven’t referenced the dependency. Although I can see how this falls through using an XML configuration when there is no type checking happening. So the driver is obviously being created using some sort of reflection. Note to the Hibernate and JPA developers – please provide more verbose or smarter messages. Perhaps I just need to wake up!

Ok well, now the persistence.xml looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
    <persistence-unit name="CommunityPlatformPU" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
            <property name="hibernate.connection.username" value="xxx"/>
            <property name="hibernate.connection.driver_class" value="org.postgresql.Driver"/>
            <property name="hibernate.connection.password" value="xxx"/>
            <property name="hibernate.connection.url" value="jdbc:postgresql://localhost:5432/database"/>
            <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider"/>
            <property name="hibernate.hbm2ddl.auto" value="update"/>
        </properties>
    </persistence-unit>
</persistence>

Right, new exception to deal with. For primary keys I prefer using UUIDs or GUIDs as they are always unique. Yes I know indexing issues blah blah blah speed related issues blah blah blah. I use it for a reason. When I transform the data into XML I want globally unique Ids so I can link via Ids. Now I usually got round this with the @PrePersist annotation (because the implementations only supported the integer values) but wanted to see if there had been any improvements since my last run in with JPA. Turns out there has been.

This is the way you use UUIDs as PrimaryKeys

@Id
    @GeneratedValue(generator="system-uuid")
    @GenericGenerator(name = "system-uuid", strategy = "uuid2")
    @Type(type = "pg-uuid")
    private UUID id;

    public UUID getId() {
        return id;
    }

    public void setId(UUID id) {
        this.id = id;
    }

Cool!  Next …

This little rig didn’t seem to like the jdbc3 drivers so switching to the jdbc4 drivers seemed to resolve that.

So that is that! Finally my test is passing and I am able to go to bed   Well almost. Next it is time to configure the caching for the database and the connection pooling. Seems most of the libraries are included in the hibernate distribution. So the final persistence.xml file looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
    <persistence-unit name="CommunityPlatformPU" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
            <property name="hibernate.connection.username" value="dev"/>
            <property name="hibernate.connection.driver_class" value="org.postgresql.Driver"/>
            <property name="hibernate.connection.password" value="dev"/>
            <property name="hibernate.connection.url" value="jdbc:postgresql://localhost:5432/communityplatform"/>
            <property name="hibernate.hbm2ddl.auto" value="update"/>
            
            <property name="hibernate.cache.provider_class" value="org.hibernate.cache.EhCacheProvider" />
            <property name="hibernate.cache.use_second_level_cache" value="true" />
            
            <property name="c3p0.min_size" value="5" />
            <property name="c3p0.max_size" value="20" />
            <property name="c3p0.timeout" value="300" />
            <property name="c3p0.max_statements" value="50" />
            <property name="c3p0.idle_test_period" value="3000" />
            
            <property name="current_session_context_class" value="thread" />
        </properties>
    </persistence-unit>
</persistence>

Green light on the tests, creating the database structure and persisting the information. Cool, now it is definitely time for bed, big day tomorrow, Skye turns 6

References:

http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/mapping.html#d0e5294

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/session-configuration.html#configuration-hibernatejdbc

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/performance.html#performance-cache

Tweet