11

Is there a good way to tell how long the query in a batch apex job is taking? I don't find anything in the debug logs. I can see each batch and how long it takes, but not how long the initial query to get the first batch is taking.

Here's why I ask: I have a managed package with a batch job that works everywhere we've installed it. It iterates over Campaign Member records. Except last week it failed multiple times in an org that had 14 million CM records. The err looks like this:

First error: SQLException [common.exception.SfdcSqlException: ORA-01013: 
user requested cancel of current operation

Which based on this, seems to be a query timeout: http://help.salesforce.com/HTViewSolution?id=000175555&language=en_US

I suspect the problem is in the ORDER BY, which is why I want to see how long it's taking to get the first batch.

The problem is that since then this has started working again, in the same org. Code hasn't changed. Some CM records have been deleted (in an unrelated process), so they're now at about 13 million. I don't know if SF is just executing faster today, or if it can handle 13 million but not 14 million.

I know I may need to optimize my query and make it more "selective", and I have some ideas for how to go about that. But if I can't get it to fail with regularity now, I won't know when I've optimized it enough. Unless I can profile how long it's taking, and then I can just try to get it under a particular execution time.

Anyone got advice here?

  • 1
    The logs include one entry for the start method. Check this log and you should get your answer. Also, consider sorting by an indexed field if you want to avoid the timeout. See if you can index the field in question. – sfdcfox Apr 24 '14 at 0:32
  • I don't see this. Here's my logs: imgur.com/4xYvcxE. The red line indicates where a previous batch job ends and the current one begins. I don't see anything about the start message or the batch query in that first log above the line. Am I missing something? – mscholtz Apr 24 '14 at 0:45
  • Log In this case, you see five entries: the first is the Exec Anon: Database.executeBatch(new B(), 5). The read entry is the start method, the next two are two execute methods with 5 entries a piece, and the final is the finish method. The class B simply sets up an iterator that counts 10 items. – sfdcfox Apr 24 '14 at 1:41
  • 1
    There's a bunch of good info here Matthew: developer.salesforce.com/blogs/engineering/2013/02/… – greenstork Apr 24 '14 at 3:19
  • 2
    @mscholtz Basically, SerialBatchApexRangeChunkHandler is a tool for handling really large data sets. Coincidentally, since it runs like a "pre-batch" before the main execute loop, the query is subject to a 15 minute timeout (I'm assuming, since long queries have that limit). Like I said before, try indexing the field you're sorting by, or like the blog entry from greenstork, try being more selective and/or batch chaining. I'm hesitant to actually write up a full-on answer right now because I feel there's not enough info. Also, try contacting support. – sfdcfox Apr 24 '14 at 3:29
1

In the Developer Console, there is an option to view the Execution Overview. The way I went about getting to it was opening my debug log in the console, then -> Edit -> View Log Panels (CTRL +P). A 'Select Panels' window appears and I checked Execution Overview. I selected Executed Units, then made only the Queries show as in the image below.

Execution Overview for Queries

Hope this helps!

0

First thing first, you can check individual query performance in workbench. Execute you query and you will get record count plus time taken. Batch job execution depends on resource availability rarely does it happen that salesforce server is down whivh you can check on trust.salesforce.com .

0

The solutions proposed above are great solutions (especially the Developer Console one), but if you're looking for long term telemetry on your application performance, you have to build this in. Use a Custom Object and insert records into it before and after each piece of code that you want tracked. Obviously, this isn't perfect since you are using the database, but most telemetry solutions always have some slight performance impact.

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.