Recently I had the chance to do some tuning on a system that was built using Grails (formerly Groovy on Rails) and the short and hopefully accurate enough scoop for our purposes is that includes an ORM layer – a data abstraction layer. ORM’s excel at rapid development/time to market scenarios, and they can also be used to deliver platform independence. The downside – to use SQL people anyway – is that the abstraction can cause pain as it does vanilla level generation of SQL statements.

I haven’t coded in Grails. Looking at the code though, I see some of the attraction. Once you point an object at either a table or view you just apply a where clause. No SQL statements, no low level SQL details. I’m  not saying I’m changing my stance on hand coded and well implemented data access, just that I see that it generates the layer I’d typically code by hand (though perhaps not as well in a few cases!).

The first bit of strangeness is that it requires that each row have a unique id. Easy enough for a table, not always possible for a view, so the answer had been to use ROWNUMBER in the views to provide a key. Performance with RowNumber varies. In some cases it was fine, in some cases it would tip a query from 100 milliseconds to over four seconds, going from really nice seeks to pretty horrible scans (removing RowNumber would take us back to a nice plan). We fixed some of these with the trick of using ‘select 0’ in the over clause, some we replaced with checksum, and some were ok as they were. As I understand it the purpose of the keys is much like foreign keys in our world, used to tie various objects together. We tried using NewID, but hitting ‘back’ caused pain when we got an entirely new set of keys each time. Moving to stored proc calls is possible, but seems to not work well with the objects – and I’ll admit to not knowing nearly enough about what is possible here.

It can also be chatty, lots of inexpensive calls to the database to verify security, though I’m not sure if that’s a feature/limitation, or a side affect of how it’s implemented. Something to watch for.

It was using the JTDS driver, here’s one link I found. From what I could find the default setting is to send all parameters as Unicode, which in a lot of cases forced us into scans instead of getting seeks. It also builds a prepared plan (sp_prepexec) for each statement, which also adds some overhead. Both can be adjusted with settings in the connection string. Just moving back to ANSI was a very nice performance gain.

Is there a real productivity gain? Hard for me to tell, but the ‘complexity’ that it hides was basically moved to views, so that one view equaled one object. The resulting queries against the views were trivial, and the occasional update seemed fine as well. The big gain is that as you add or remove columns you don’t have to modify any data access code (you might have to change your views of course).

I’m not sold on it, but we’ve been able to wring good performance with a relatively few tweaks once we dug into it. If you use it just keep an eye on it as it gets built, don’t wait until too late to assess performance (which is true for any data centric project).