It’s often the case that we want to check to see if a record exists and depending on the outcome perform differing actions – often resulting in either an insert or an update.  Thankfully we have the database upsert to help us in this quest however I feel as though we’re then let down by the syntax that is available to us to get to the point of an upsert.

We often see what I call the limit 1 syntax in use to help us here – and why wouldn’t you?  After all it seems like a sensible plan however complications arise when using this as, if the record doesn’t exist we will receive an exception.  This complication then generally leads us down a very dark path and we start to see the try/catch blocks being used to control the flow

Custom_Object__c co;
    co = [SELECT Id FROM Custom_Object__c WHERE Id =:oneId];
} catch( QueryException ex ){
    co = new Custom_Object__c(...);

This construct generally makes me shudder.  We’re using the try/catch to test whether the record exists – in this case the record not existing isn’t really exceptional behaviour – in fact depending on the use case it made even be the more likely behaviour.  To me this test should be evaluated using an appropriate language construct, such as an if statement.

OK, so we should use an if – but how.   Well it’s fairly straight forward just assign the results to a collection as you normally would.  Then you have a collection that you can test the size of and therefore test to see if the record exists.  In my eyes this is much cleaner and actually conveys the purpose of the code much more clearly.

Custom_Object__c co;
List<Custom_Object__c> objs = [SELECT Id FROM Custom_Object__c WHERE Id =:oneId];
if( objs == null || objs.size() == 0 ){
    co = new Custom_Object__c(...);
} else{
    co = objs[0];

As well as looking nicer and reading better the if technique has another advantage: it is quicker.  The try/catch construct in most languages is an expensive one and SalesForce is no exception.  Well, no exception in this case, I have actually discovered some interesting results whilst testing this that will probably crop up in another post.  Nevertheless, in this case it turns out to be faster – between 300 to 600 ms faster in fact.  Now that’s definitely an improvement but the optermizer seems to be doing some fancy tricks, for example this doesn’t multiple up if you put it in a loop.  As I say I think there are some interesting things around how try/catch effects performance but I’ll save that for another day.  Let’s just say for now that it’s faster.

So, how could SalesForce improve this for us?  Should they?  Well, given that we have the upsert then I believe that there should be better support for this – otherwise they’ve just exposed database functionality because they could.  How?  Well, I think it requires two changes.  When a query that has a limit 1 on it returns no records then simply assign null to the object, otherwise assign the returned object.  That’s ok, that works.  The problem comes in the fact that SalesForce allows you to use the same syntax on a query that may potentially return many records; it basically hopes that you know you’ll only return one record if you don’t (be that zero or many) then we get an exception.  My extreme view would be to make this completely illegal; to me it’s really unclear an presumptuous code anyway.  The alternative is that we still throw an exception if there are many records returned by the query but according to my first change assign null if there are none.

Two simple changes that make a lot of semantic sense and in my opinion shore up the language and provide a more consistent and robust place to work.  However, given the amount of code that these simple changes would make this is purely pontification.

Given the destructive nature of my desires I know that I’m essentially arguing that “the difference between a duck is that one of it’s legs is both the same”.  Having said that I believe what I’m saying is valid so I thought I’d layout a couple of the arguments laid before me in the past against this point of view.

One of the non-believers favourite arguments is that the syntax exists therefore it must be right to use it.  I take issue with a couple of points here… firstly just because something exists in a language doesn’t mean we should use it, I don’t I need to say much more on this one than GOTO.  The second point is that I’m not actually against the limit 1 syntax as such, more the way in which it is currently implemented and how people decide to use it.

Another point put to me is about how I suggest we work around the current deficiencies.  “But it’s bad practice to take the first element from the array”.  Well yes, it is, to an extent but we don’t go taking it blindly – we have the whole set of results there, we can see how many we had, none? one? a thousand?  And we can take the appropriate action.  Think about what the limit 1 syntax is doing. It’s taking the first record out of a result set and returning only that one record to you.  Hmmm, this sounds awfully familiar… oh yes, it’s essentially doing [0] but in the database, without giving you the whole picture.  The if syntax gives you more information with which to make a better decision about how your code reacts.

The final accusation that I have levelled at me, that I feel like writing about is that I’m removing the exception handling from the code.  I’m not.  I’m actually making the exception handling focus on exceptions.  With the limit 1 syntax you presume that is the query throws an exception then it’s because the record doesn’t exist and so you react to this and alter the flow of your code accordingly.  However, what if the exception had been thrown for some other reason?  With the limit 1 syntax you don’t know this, you just presume that it doesn’t exist and go on creating more records.  What fi there was a problem with your data integrity and the query you “knew” would only return one result actually returned 100 and couldn’t assign all of those to one object – you’d be oblivious, thinking instead that there were no matching records; oh how wrong you were.  I haven’t shown exception handling in my code other than for the limit 1 construct as I didn’t want to clutter things up but trust me you can (and should) add it in and it will provide you with a source of much richer information than before. And I hasten to add it wont impact performance as much as it does on the limit 1 syntax… why I have no idea, I can only report my findings.

So, that’s that.  Try/catch and limit 1: bad.  Always assigning your query results to a list: good.