4

If you want to check if there is record of ProcessInstance exists in the system or not, please suggest which one of the following approch is faster and best practice to use ?

Approch 1 - Query Object with Id field and check list has returned any rows or not.

Select TargetObjectId, Status From ProcessInstance where TargetObjectId='006g0000003AitI' and Status='Pending' limit 1

Approch 2 - Use Count SOQL to check if any records exists in the system or not as follows -

Select count() From ProcessInstance where TargetObjectId='006g0000003AitI' and Status='Pending'

I tested it in terms of timing there is no significant difference between both.

8

If you need count then you should use count(). There is no timing difference and effects on governor limit but It has difference in heap-size.

In your first approach heap will significantly more than your second approach as you are querying data and then counting. In Second, SOQL returns a Integer only (no data).

First approach is useful if you want to populate data or want to make some use in you code. Only for counting, why should query data and then count is programmatically ( adding more character in code), if SOQL has given a feature to count on his behalf.


Update (04-Feb-2016)
Stats on running query over 2,264,510 records:

  1. Simple Query enter image description here

  2. Count Query enter image description here

  3. Aggregate Query (Added filter to prevent 50000 limit) enter image description here

10
  • Sorry, there won't be a 'significant' difference in heap, maybe just a few bytes, in practice. Might even be zero difference in heap, depending on the precise code structure. Of course, if they optimized the query a bit, the field select query might even be arguably faster than the count query, but in the end, it just doesn't matter. – sfdcfox Sep 8 '13 at 7:44
  • What in the case: [SELECT id, name... and hundred more fields.. FROM Account] and [SELECT count() FROM Account]. Here one is bad practice and other is good. [SELECT count()...] remove that possibility. Also heap single byte matters in big project otherwise governor limit won't be as small to 6MB. And also By count() we can count rows by single line of code using count(), what is good with [SELECT id FROM Account] then calculate list size? @sfdcfox – Ashwani Sep 8 '13 at 7:57
  • There's an axiom about database queries: do not query more than you need. In that sense, the question's original query should have selected only an ID value. Querying 100 fields to determine the number of rows is clearly not optimal, but doesn't fall under the premise of this question. As it is written, this falls under the axioms "do not optimize any code before its time" and "premature optimization is the root of all evil." This answer has a decent premise, but its just not right. SELECT Count() vs SELECT Id is the focus, and there is no difference. – sfdcfox Sep 8 '13 at 8:07
  • Also, we're not querying Accounts, we're querying a specific type of object that can't use much heap, won't have many results, and is indexed. If you want to get down to it, you're not even looking for a number, but a Boolean value: [SELECT COUNT() ...] == 1 vs ![SELECT Id ...].isEmpty(). Virtually no difference in code size, performance, timing, etc (I count one byte more in total code). – sfdcfox Sep 8 '13 at 8:12
  • 2
    @RalphCallaway Added some stats. Hope it help you :) – Ashwani Feb 4 '16 at 6:26
1

The question is philosophical in nature, since the minute differences in terms of space and time costs are statistically insignificant. You should pick one mode of operation and then optimize any other code that's actually going to impact your application's performance.

The query time spent will be the virtually the same, and very close to zero (I'd guess less than 2ms with either query, and far less than 1ms difference, on average, between the two). The latency in the database would normalize any possibility of coming up with meaningful results.

Depending on the use case for this code, it's possible that the difference in heap space may also be zero, since heap space is only checked every few lines of code.

Personally, I would accept the possible few extra bytes of heap size from the first query, in case I later needed to modify the code to use some of the data that count() can't return.

Instead of worrying about a few bytes of heap and nanosecond timings, you should instead focus your concerns on things that do make an impact, such as exponential for-for loops, queries inside for loops, and so on.

Premature optimization is the root of all evil. -- Donald Knuth

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.