Sign Up ›

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

Skinny Table Needed

I'm posting in the forum because I asked usign a case and I got kicked.

I need the creation of a skinny table because I'm getting timeout in a scheduled query. I'm getting the message:

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

select /*Apex.Class.BatchApexMetricSummarization.start: line 31*/ *
from (select "Cust_ID__c",
"Id"
from (select t.deleted "IsDeleted_...

That is happening because is taking more than 10 minutes to proceed.

The query is simple:

Select Cust_ID__c From Metrica_Diaria__c Where Fecha_de_Metrica__c >= :dateFrom AND Fecha_de_Metrica__c <= :dateTo AND Summarization_Date__c = \'N/S\' Order By Cust_ID__c limit 50000

When:
  • dateFrom = today - 3 months
  • dateTo = today - 1 day

Fecha_de_Metrica__c and Summarization_Date__c are indexed field, so I don't have much to do here.

Metrica_Diaria__c is a custom object with 1.942.440 records and I'm query 500k approximately (overall target records).

The fields that I need are:
Cust_ID__c, Fecha_de_Metrica__c, Summarization_Date__c, Cuenta__c

The question is:
How I get the Skinny Table created?

Thanks
Fahad-AkhtarFahad-Akhtar

Hi Juan Spagnoli,
As a first step can you please check if your query is selective, As you just mentioned you are using indexes on two fields, can you please check selectivity using Query plan tool  (https://help.salesforce.com/apex/HTViewSolution?id=000199003), steps mentioned in the link attached. if you query is not selective, splitting it in two batches also wouldnt help keeping in mind the size of the object.

Thanks,

Fahad Akhtar
Juan SpagnoliJuan Spagnoli
Hi Fahad, yes I checked with the query plan. The thing is that the amount of records may change every day, therefore also the cost. The query is limited to 50.000, reducing that limit will makes that I'll have more iterations of the same batch, and with this more processing time. 
What I see here is that neither of the indexes are helping the query. The final solution is to have a Skinny with only the fields that I'm using in the query.

Thanks for you help