14

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?

2 Answers 2

10

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.

5
  • 3
    When doing tests like this I suggest you repeat the pair of queries several times to ensure that the second query is not just benefiting from data cached when the first query is made.
    – Keith C
    Oct 20, 2015 at 12:43
  • I just created two scripts. Both queried record by Id 99 times in the loop, with and without limit 1. Results were nearly the same each time. Maybe it would be good to wait a bit, until the cache will be flushed. Oct 20, 2015 at 12:56
  • 1
    @KeithC Good point. I hadn't even considered caching. I've just ran the same queries in a for loop against two different accounts and there actually isn't much difference. Both seemed to get the records after 2 or 3 milliseconds.
    – Dan Jones
    Oct 20, 2015 at 12:57
  • I guess the reliable test should look like: there should be two sobjects with equal number of rows. One should query about 100 ids from each first, and save it as strings. Then wait until the cache is flushed, and run tests for both sobjects (query 100 times in loop with where id = and where id = .. limit 1 clauses. Was your test similar @Poet ? There is also CUMULATIVE_PROFILING event type that shows overall execution time for some pieces of code. Maybe it's a better measure? Oct 21, 2015 at 8:11
  • @BartJuriewicz Admittedly no, my test didn't do this. I did try looking for some kind of function that might clear the query cache but surprisingly there isn't one. I would assume though you're correct in saying that that's a better measure. I did find something interesting though (albeit this is related to SQL), which I'll update my answer with.
    – Dan Jones
    Oct 21, 2015 at 8:24
4

I initially thought what Poet did, debug and get time in millisecond but if you will check both queries with query plan tool under developer console you will get better answer for this.

they come up with following results.

  1. First Query

enter image description here

  1. Second query

enter image description here

You can see that second query over all cost is much higher than first one. I assume that less cost is better than other one.

A reference video about query performance tips https://www.youtube.com/watch?v=uyyXdEIieN4

2
  • Are you assuming that this 'Other" overhead comes from limit clause ? Oct 20, 2015 at 12:59
  • it seems. you can run that query at your end.
    – Himanshu
    Oct 20, 2015 at 13:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .