My Adventures in Coding

October 24, 2009

Migrating Data to JSON In SQL Server

Filed under: SQL — Brian @ 10:44 pm
Tags: , ,

Recently I was working on a task that required migrating some existing data (Phone Numbers) from being stored as separate columns in a separate table (e.g., dbo.FriendPhoneNumbers), to being stored as a single JSON document in one column of the table containing all related data (e.g., dbo.Friend).

My task was only to complete a one-time migration, so I did not want to spend a lot of time writing a custom application to complete this work. Just for fun I decided to see if I could first accomplish this migration in SQL. I was also curious if such a SQL statement would be efficient enough to be run in a live production environment.

Using the SQL UPDATE statement below I was able to create 100,000+ JSON documents in just a few seconds.

SET PhoneNumber = phoneJSON.JSON 
from dbo.Friend f 
	select FriendID, REPLACE( 
		CASE WHEN Home IS NOT NULL THEN '"Home":"'+Home+'",' ELSE '' END +
		CASE WHEN Work IS NOT NULL THEN '"Work":"'+Work+'",' ELSE '' END +
		CASE WHEN Fax IS NOT NULL THEN '"Fax":"'+Fax+'",' ELSE '' END +
		CASE WHEN Cell IS NOT NULL THEN '"Cell":"'+Cell+'",' ELSE '' END
		+'}', ',}', '}') as 'JSON'
	from dbo.FriendPhoneNumbers
) as phoneJSON ON phoneJSON.FriendID = f.FriendID

While writing the script I ran into two issues:

  1. Nullable Columns: Wrapped each column in a SQL CASE statement so when a phone number was null, nothing was added to the JSON
  2. Trailing Comma: Added a SQL REPLACE to replace “,}” with “}” to handle the case where the last phone number in the list was null

That was all! It took only a few minutes to write this script.


October 11, 2009

Second Level Caching for Hibernate with Terracotta

Filed under: Caching,Hibernate — Brian @ 4:54 pm
Tags: , , ,

Traffic on our system has been increasing, putting more pressure on our database causing the response time of our application to degrade. Since we are currently using Hibernate in our project for a Java app that surfaces a REST API, we decided to look into caching strategies available. After looking around and trying a few technologies we decided to tested out the open source version of Terracotta.

To start let’s just explain the difference between First Level Caching and Second Level Caching in Hibernate.

First-level Cache

First level caching is associated with the Session object and is caching on a per transaction basis. An example would be an object where you make several updates to it as part of a single transaction. Hibernate, rather than submitting several update statements to the database, will pool the group of updates to the object into a single update statement.

Second-level Cache

Second level caching is associated with the SessionFactory object and maintains a cache of previously loaded objects accross transactional boundaries. These cached objects are available to any query for the same object, not just a single transaction. So in this case the entire application has access to retrieve any cached object. For example, if my app performs a lookup for a Product object describing a product, if the application later makes a request for the same product, this object can be retrieved from the Second-level Cache rather than making an additional trip to the database.

Why Terracotta?

Handles asynchronus queueing of writes. This feature offers some failover capabilities for when our database is not available. Originally we attacked the problem separately figuring we needed a caching strategy for reads and a fault tollerant queuing strategy for writes to deal with our current single point of failure (our database). Fortunately Terracotta has functionality that allows us to cover both of these cases.

The Developer Console. This tool allows you to monitor the traffic between your app, the Terracotta server, and your database. With the Developer Console we were able to test out different caching strategies to see which ones gave us the highest percentage of cache hits. For us developers, this was a major plus, having such a useful tool to aid us in tuning our cache settings. It made Terracotta feel much less like a black box.

Simple to install and configure. Since Terracotta just plugs in to Hibernate, there is no real development work required to get it up and running. Setting up a Terracotta server is easy, adding it to the application just meant minor additions to our pom.xml file and our hibernate settings file, and tuning our cache settings was relatively simple using the provided Developer Console.

Install and Start the Terracotta Server

  • Download Terracotta
  • Install by running the command: java -jar terracotta-3.1.0-installer.jar
  • Start the Terracotta server: ~/terracotta/terracotta-3.1.0/bin/
  • Start the developer console: ~/terracotta/terracotta-3.1.0/bin/

Add Terracotta support to your Maven/Java/Hibernate/Spring Application

  • Add the Terracotta dependencies to your Maven pom.xml file
  • <dependency>
  • Add the following properties to the hibernate properties section of the sessionFactory bean in your Spring database settings file (e.g., app/src/main/resouces/sping/database/database.xml)
  • <bean id="sessionFactory">
        <property name="dataSource" ref="dataSource"/>
        <property name="hibernateProperties">
                <prop key="hibernate.cache.use_second_level_cache">true</prop>
                <prop key="hibernate.cache.provider_class">
  • Add a cache annotation to any Hibernate Entity in your application that you wish to have cached: @Cache(usage=CacheConcurrencyStrategy.READ_WRITE)
  • Create a Terracotta cache settings file. This is important because Terracotta by default NEVER evicts an object from cache. Once the memory allocated to Terracotta has been filled, Terracotta will start to write to disk unless eviction settings have been put in place. In the “Resources” folder of your application, create a file called “tc-hibernate-cache.xml”. To create the content for this file you can either look at the Terracotta Hibernate Configuration Reference Guide or use the Developer Console to generate a cache settings file using the GUI tool.

Running your project with Jetty

In our local development environments we use Jetty as our web server (Tomcat on production). To run your app from Maven using the Jetty plugin make sure you first do the following:

  • Set the MAVEN_OPTS environment variable with the “javaagent” parameter
  • export MAVEN_OPTS="-javaagent:/path/to/terracotta-hibernate-agent-1.0.0.jar -cp /path/to/terracotta-hibernate-cache-1.0.0.jar -Xmx512m"
  • NOTE: We appended to the MAVEN_OPTS environment variable the value “-Xmx512m” to tell Jetty to use 512mbs (the default is 128). We have found that with an application using Hibernate, 128 makes performance of the application sluggish.
  • Now start the app with the command: mvn jetty:run


Overall we have found Terracotta to be a useful tool. It requires very little effort to update an existing project using Spring/Hibernate to use it. Terracotta offers more than just Second Level Caching, but also handles queuing of writes and ensuring data is written to the SOR (System or Record) in the event the database is not available for a brief period. Also, stale cache entries are not an issue as well since Terracotta updates the cache immediately on any write made to the SOR. As a final note, the Developer Console is a really nice addition!

I hope this post helps!

October 8, 2009

University of Saskatchewan Programming Contest

Filed under: Conferences — Brian @ 10:58 pm
Tags: ,

UofSProgrammingContestMarcos and I attended the University of Saskatchewan Programming Contest where local students were competing for prizes and the chance to attend the Regional Programming Contest at the U of S on Oct 30, 2009.

The contest had eight coding problems to solve. To win you just needed to correctly solve the most questions. However, in the event of a tie the team who solved the problems in the shortest amount of time wins. The questions ranged from very simple to extremmely difficult. If you are interested in seeing some sample questions (Or would like to test yourself!) you can check the U of S Programming Contest sample page.

Students competing in the contest ranged from novice to advanced, however there is also an opportunity for anyone who wants to, to form a team and compete for fun! (count us in for next year). The contest is not just about writing code, but more about focusing on problem solving where you prove your solution with a small program. Of course the competitors who are able to solve each problem with small simple applications definitely have an advantage.

While at the programming contest we had the chance to meet the co-ordinator of the programming contest, professor Christopher Dutchyn. He gave us an overview of the contest, how scoring was handled, and walked us through some of the problems teams were attempting to solve. During our discussion, Marcos chanllenged the winners of the U of S contest to a programming contest with Point2 employees.

During our weekly professional development time we plan to put together a team to practice similar problems to the ones solved in the programming contest. We hope to prepare ourselves for a fun challenge. We know the competition will be tough, but we hope to give students a fun practice session before they go on to another competition. We hope to set something up some time over the next few months.

At the end of the contest Point2 presented some prizes to winners and also we told the students a little bit about Point2. We hope to see many of those students applying for positions offered through the U of S Internship Program for next spring.

After having a chance to spend a day on campus it sure makes me miss my time their as a student. The faculty and staff were great hosts.

Create a free website or blog at