Is SOQL query with ID condition optimized to work as limit 1?

Is there any performance difference between these two queries?

select name from Account where id = :myId

select name from Account where id = :myId limit 1

Or, I guess more general: does salesforce stop searching after first hit when there’s a unique value as a condition?

Answer

I’ve just done a short mundane test on this. In the Workbench I’ve used the following code to see what the time differences are:

System.debug(Datetime.now() + '-' + Datetime.now().milliSecond());
List<Account> first = [select name from Account where id = '001D000000mtphx'];
System.debug(Datetime.now() + '-' + Datetime.now().milliSecond());
List<Account> second = [select name from Account where id = '001D000000mtphx' limit 1];
System.debug(Datetime.now() + '-' + Datetime.now().milliSecond());

I got the following output:

11:35:44.034 (34924592)|USER_DEBUG|1|DEBUG|2015-10-20 10:35:44-530

11:35:44.049 (49634341)|USER_DEBUG|[3]|DEBUG|2015-10-20 10:35:44-545

11:35:44.056 (56649867)|USER_DEBUG|[5]|DEBUG|2015-10-20 10:35:44-552

So for the first query it took 15 milliseconds to move onto the next step. However, using LIMIT 1 did it in 7 milliseconds which is quite a difference.

It would seem using a LIMIT on your seems to have an impact on performance, but I couldn’t tell you exactly why. Presumably this would be because once it’s found one it doesn’t need to continue iterating to find any other potential matches and I’d expect the WHERE clause doesn’t necessarily know Id is unique.

So, to answer your question:

Is SOQL query with ID condition optimized to work as limit 1?

I would say the answer is no.

Edit:
Following Keith C’s comment on caching I re-ran the script against two different accounts and actually found very little difference. Sample below:

13:54:28.028 (28942996)|USER_DEBUG|[2]|DEBUG|Run: 0
13:54:28.029 (29120711)|USER_DEBUG|[3]|DEBUG|2015-10-20 12:54:28-349
13:54:28.032 (32221103)|USER_DEBUG|[5]|DEBUG|2015-10-20 12:54:28-352
13:54:28.034 (34609810)|USER_DEBUG|[7]|DEBUG|2015-10-20 12:54:28-354
13:54:28.034 (34683961)|USER_DEBUG|[2]|DEBUG|Run: 1
13:54:28.034 (34896409)|USER_DEBUG|[3]|DEBUG|2015-10-20 12:54:28-354
13:54:28.036 (36853255)|USER_DEBUG|[5]|DEBUG|2015-10-20 12:54:28-356
13:54:28.039 (39205884)|USER_DEBUG|[7]|DEBUG|2015-10-20 12:54:28-359
13:54:28.039 (39280981)|USER_DEBUG|[2]|DEBUG|Run: 2
13:54:28.039 (39466276)|USER_DEBUG|[3]|DEBUG|2015-10-20 12:54:28-359
13:54:28.042 (42008179)|USER_DEBUG|[5]|DEBUG|2015-10-20 12:54:28-362
13:54:28.044 (44292278)|USER_DEBUG|[7]|DEBUG|2015-10-20 12:54:28-364
13:54:28.044 (44341631)|USER_DEBUG|[2]|DEBUG|Run: 3
13:54:28.044 (44448971)|USER_DEBUG|[3]|DEBUG|2015-10-20 12:54:28-364
13:54:28.046 (46634406)|USER_DEBUG|[5]|DEBUG|2015-10-20 12:54:28-366
13:54:28.048 (48722705)|USER_DEBUG|[7]|DEBUG|2015-10-20 12:54:28-368
13:54:28.048 (48800339)|USER_DEBUG|[2]|DEBUG|Run: 4
13:54:28.048 (48936750)|USER_DEBUG|[3]|DEBUG|2015-10-20 12:54:28-369
13:54:28.050 (50946443)|USER_DEBUG|[5]|DEBUG|2015-10-20 12:54:28-371
13:54:28.053 (53892405)|USER_DEBUG|[7]|DEBUG|2015-10-20 12:54:28-373

There’s literally 2 or 3 millisecond differences between them.

Edit 2:
I found an interesting answer here although keep in mind this is related to SQL, not SOQL, so there may be some differences but I’d imagine the concept would be the same.

The optimizer must decide how to find all the rows satisfying the
condition id = 15. It may know, from the statistics, that values in
the column are unique (num distinct = num rows in the table), however,
that doesn’t provide any information on where the row with id = 15 is
physically located on the disk. So, the database has no choice but to
scan the entire table to find the matching row. It can’t even stop
once it finds the first row with id = 15, since there’s no guarantee
that there aren’t more such rows.

Now, we create a unique index on column id, and repeat the same query.

Things are radically different now. The optimizer now knows that there
are only two possibilities: either there is exactly one row that
satisfies the condition, or there are no such rows. So, a cheap index
seek is all that is needed to find the required row if it exists; the
same index seek will return no results if there are no such rows. So,
the query will run fast.

So I would say, based off of this, if the Id is indexed, using LIMIT 1 shouldn’t make a difference as the SQL query will already know that Id is unique and it either exists or it doesn’t. So, is it indexed? Yes. In fact, the following fields are indexed:

  • RecordTypeId
  • Division
  • CreatedDate
  • Systemmodstamp (LastModifiedDate)
  • Name
  • Email (for contacts and leads)
  • Foreign key relationships (lookups and master-detail)
  • The unique Salesforce record ID, which is the primary key for each object

Although not all will necessarily be unique.

Attribution
Source : Link , Question Author : Bart Juriewicz , Answer Author : Community

Leave a Comment