function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Juan SpagnoliJuan Spagnoli 

Help needed to know what query is giving timeout

How can we identify which query is giving us timeout in a Batch Apex processing?  We have a Batch Apex that is aborting after N executions. The aborting is caused by timeouts in Oracle Database, and it happens randomly. We cannot see which query is causing it because SFDC doesn't allow you to see it in the UI. We checked them in the Query Plan Tool and none involves tables with more than 150.000 records (low volume). (We have already read about Query Optimizer and High Volumes.)

I already logged a case, but of course they sent me to the forum, even when the only people who can tell me what I need are them.

Sonam, could you help me find the query?, this issue has been burning for some time now, thank you!
Best Answer chosen by Juan Spagnoli
PratikPratik (Salesforce Developers) 
Hi Juan,

In the Batch "BatchApexPriorizacion" , it's calling a class "PriorizacionesUtil" and the method "armarGrupos" of this class contain a query which is causing timeout.

Query:
for(AggregateResult i : [SELECT Caso__r.AccountID Cuenta, MAFecha_y_Hora__c) FechaHora, Caso__r.Campana__r.Temporada__c Temporada FROM Interaccion__c WHERE Caso__r.AccountID IN :cuentasProcesando GROUP BY Caso__r.AccountID, Caso__r.Campana__r.Temporada__c])

Please otimize this query so it will not give time out issue.

Thanks,
Pratik

All Answers

Frédéric TrébuchetFrédéric Trébuchet
Hi,

If you're sure timeout are in Oracle Database, the 1st thing to do is to test these requests connected locally to Oracle.
Also, can you tell us how Salesforce speak to Oracle?

Regards,
Fred
Juan SpagnoliJuan Spagnoli

Fred, thanks for your reply. I'm not using oracle on my own, Salesforce.com uses Oracle as database. When you make a query, the Query Optimizer transforms the soql to an oracle's sql query. If that query runs for more than 2 minutes, it gives you a timeout and a message like "Aborted by user". 

We need to identify the query (there are several queries in the execute's method) to analize how we can optimize it.
Frédéric TrébuchetFrédéric Trébuchet
OK, I misunderstood the question and thought you were using Oracle as an external datasource.
As a suggestion, you can try to add an "order by id" to your query (https://help.salesforce.com/apex/HTViewSolution?id=000171421&language=en_US (https://help.salesforce.com/apex/HTViewSolution?id=000171421&language=en_US)).
Also, you can contact Salesforce Customer Support to create custom indexes if needed.

Hope this helps,
Fred

 
Juan SpagnoliJuan Spagnoli
Yes, that's the idea, but first I need to know what query is giving the timeout.

thanks.
Frédéric TrébuchetFrédéric Trébuchet
I think you have to change your log level to "finest" which will give you execution time for your queries.
Using developer console, then workspace manager and change Log Levels (I cannot try).
https://www.salesforce.com/us/developer/docs/apexcode/Content/code_setting_debug_log_levels.htm.

Let us know.
Fred
Juan SpagnoliJuan Spagnoli
The aborting happens randomly and not so frequently, that is because SFDC uses a cache, when the data is cached, works fine. We saw the error in not so friendly hours, je.. for example at 3 am. Is almost impossible to be there with the console opened if the error happens at any time. :)
Frédéric TrébuchetFrédéric Trébuchet
I'm afraid only Customer Support can help you in such a case, but I'd like to know the end of the story.
I'll come back if I have more ideas.

Fred
Juan SpagnoliJuan Spagnoli
Exactly, but they sent me to the forum because I mentioned "BatchApex" and we don't have premium support.

We are adding a custom logging to a document to know what query is the last executed (it's log before the query is executed). I think with that, we could know what query is failing, when it fails.
Frédéric TrébuchetFrédéric Trébuchet
If your document remains persistent, you should!
Frédéric TrébuchetFrédéric Trébuchet
Hi,

Any more informations since last time?

Fred
Juan SpagnoliJuan Spagnoli
Not yet, we´re still waiting to deploy in production the custom logger, we'll try first to reproduce it in a full sandbox instance. 

I'll get back to the topic when I get any news.

Regards
PratikPratik (Salesforce Developers) 
Hi Juan,

In the Batch "BatchApexPriorizacion" , it's calling a class "PriorizacionesUtil" and the method "armarGrupos" of this class contain a query which is causing timeout.

Query:
for(AggregateResult i : [SELECT Caso__r.AccountID Cuenta, MAFecha_y_Hora__c) FechaHora, Caso__r.Campana__r.Temporada__c Temporada FROM Interaccion__c WHERE Caso__r.AccountID IN :cuentasProcesando GROUP BY Caso__r.AccountID, Caso__r.Campana__r.Temporada__c])

Please otimize this query so it will not give time out issue.

Thanks,
Pratik
This was selected as the best answer
Juan SpagnoliJuan Spagnoli
Pratik, THANKS YOU!

We are going to work on this query right away, if i have any question I'll post in this thread. 

Thanks so much!
PratikPratik (Salesforce Developers) 
Glad it helped! Keep us posted if you need any help!

Thanks,
Pratik
 
Juan SpagnoliJuan Spagnoli
Pratik, I tested the query with 300 account's ids and I got:

Query: SELECT Caso__r.AccountID Cuenta, MAX(Fecha_y_Hora__c) FechaHora, Caso__r.Campana__r.Temporada__c Temporada FROM Interaccion__c WHERE Caso__r.AccountID IN :accountIds GROUP BY Caso__r.AccountID, Caso__r.Campana__r.Temporada__c

Result:
User-added image

The cost showed is from Case's table, but I don't sure if that is the real cost of executing the query over Interaccion__c, I think It's not.

So, I splited the query in two parts, first I query Case's object to get case's ids and then use them over Interaccion__c query:

Step 1 - Query: Select Id From Case Where AccountId IN :accountIds

Result:
User-added image

Step 2 - Query: SELECT Caso__r.AccountID Cuenta, MAX(Fecha_y_Hora__c) FechaHora, Caso__r.Campana__r.Temporada__c Temporada FROM Interaccion__c WHERE Caso__c IN :caseIds GROUP BY Caso__r.AccountID, Caso__r.Campana__r.Temporada__c

Result:
User-added image

Do you think this will help to avoid the timeouts that we were experiencing? We already reduced the account's scope from 500 to 200 records in the batch.

Thanks for your help.