Similar queries: Better to query twice or process via Apex?

Question

I'm building a Visualforce page in which I want to display two similar (almost identical) lists of the same sObject -- one for objects with an Open status and one for Closed objects. I can think of two ways to do this in the controller -- From an application performance perspective, which approach is better? (In general, there will be anywhere from 2 to 20 objects returned total by the two queries.)

I know it's always good to reduce the number of database queries, but it's also important to reduce the number of code statements executed!

1. Query Twice

List<Custom_Object__c> closedObj = [SELECT Id, Whatever__c, Status__c, Date_Opened__c
                                    FROM Custom_Object__c
                                    WHERE Status__c == "Closed"
                                    ORDER BY Date_Opened__c DESC];
List<Custom_Object__c> openObj = [SELECT Id, Whatever__c, Status__c
                                  FROM Custom_Object__c
                                  WHERE Status__c <> "Closed"
                                  ORDER BY Date_Opened__c DESC];

2. Query Once and Process in Apex

List<Custom_Object__c> openObj = new List<Custom_Object__c>{};
List<Custom_Object__c> closedObj = new List<Custom_Object__c>{};

for (Custom_Object__c theObj : [SELECT Id, Whatever__c, Status__c
                                FROM Custom_Object__c
                                ORDER BY Date_Opened__c DESC]) {
   if (theObj.Status__c == "Closed") {
     closedObj.add(theObj);
   } else {
     openObj.add(theObj);
   }
}

Answer 1

Great question!

Given your data volumes and the overlap in the fields you require. I would go with a single query and thus single result set. Yes your burning some statements but not a huge amount given your volumes.

So I would allow yourself to leverage a single query (which is far more constrained resource than statements) and benefit from having a single place to maintain the fields you need, especially given the high level of overlap between the two lists.

Performance wise, given your volumes there is likely to be marginal difference between one vs two queries. However I always try and maximise my requests to the database in general.

A single query will also allow you to better build a shared VF component to view (or whatever else you need) the cases as well. Since you will be safe in the knowledge the rows in either list have been populated via the same set of fields selected. Do you need to include Date_Opened__c in your field list btw?

Large Volumes: If you expect larger data volumes then statements will of course become more of a critical issue vs number of queries (depending on what your doing with the data). For larger volumes, keep an eye on viewstate size (particularly if your page is interactive) and consider a paged approach to manage whats stored in it at any given time. Further than this you might want consider stateless pages utilising JavaScript Remoting or REST API. Note that this requires quite a shift in approach and requires you to do more Javascript and formatting etc in the page. Make sure you use the Developer Console tools to monitor your viewstate size against the volumes your testing with from your users.

Answer 2

Best practice for Visualforce controllers is to minimize the number of SOQL queries in the controller and ensure they are performant. It does make it marginally less maintainable though, as if you need to change the behaviour between the two lists then you have to break them out into separate queries at that time.

The best resource I've found regarding Visualforce and performance is this session from Dreamforce 11:

http://www.youtube.com/watch?v=qCF8ItUI8Fs

I'd recommend watching it as there's a few things in there that may not have occurred to you - for example caching data doesn't necessarily help performance as it increases the viewstate size.