What condition triggers a QUERY_TOO_COMPLICATED error?

[ColoradoMike]

I'm calling the Retrieve statement in which I'm grabbing quite a few fields, many of which are formula fields and lookup fields to other objects.

Lately I've been getting this error (QUERY_TOO_COMPLICATED) but the API documentation doesn't say exactly what makes the query too complicated.

Is there a hard number of calculated fields (or relationship queries) that can be included in a single Retrieve call?  Does it depend on the complexity of the formulas within the calculated fields?

If anyone can shed more details about what contributes to this error, I would be really grateful.

Mike

[RickyG]

Mike -

I've done a bit of internal research, and there is no easy way to determine when you are crossing the line into the realm of too complex.  I recommend that you call customer support, who can help you understand how this applies to your particular situation and what they might be able to do to help alleviate it.

Hope this helps.

[pfarrell]

Can we reopen this issue?  I've encountered similar problems and because of the type of app I'm supporting, I can't look at the query itself (at this time). 

I've seen that an "order by" is limited to 32 fields.

Is there a limit to the number of fields you can include in a select itself?

[wyvern]

I'm also hitting this issue with retrieve(). Can we get some clarification on what causes this in general?

[daniph1 · Accepted solution]

There actually isn't any explicit limit on the number of fields that can be selected in a query (other than the 500 field limit). Oracle has a limit on the length of a SQL query, which is 64k. The query too complicated error  means that Salesforce has hit Oracle's query length limit.

If calculated fields mean formula fields; then formula fields generate SQL expressions based on the formula expression, which can get pretty large if the formula is complicated, selecting more complex formula fields will increase the chances of hitting the Oracle 64k limit. Formulas are in itself complex expressions and hence compile to some non-trivial SQL. The SQL size per formula field is limited to 5k.

Currently it is possible for the SQL size of formula fields to go over the 5k limit but that may change in a future release to go back to 5k.

[wyvern]

I've retrieved more than 500 fields at once, so maybe the 500 field limit only applies to the query call.

This is the first I've heard about the cause of this issue being an Oracle limitation; has this been documented anywhere?

[daniph1]

No it not currently documented. This information was discovered from an open case with Support.

When were you able to query more than 500 fields from an object? Did it include related fields? 

[wyvern]

Just to avoid confusion, it was technically a retrieve() and not a query() as far as the API call goes. I'm not positive that I had more than 500 fields but my recollection is that I had about 520. I was debugging this QUERY_TOO_COMPLICATED issue so I set up a developer account with hundreds of custom fields and retrieve()'d them all. I don't think any of them were related fields.

I'll try setting something up with a large number of related fields and see if I can figure out where the breaking point is.

This is such an easy issue to avoid, though -- it seems incredible that the Salesforce backend can't be bothered to split up a list of fields into chunks that can fit inside Oracle's query limit. I'm doing that already to fit inside the SF API's 10,000 char limit: I'm splitting very large field lists for retrieve() into multiple retrieve calls that are then re-assembled. It's not hard.

[daniph1]

I agree its not difficult to split up the query to avoid the 64k limit however performance issues need to be considered. In addition to governor limits in an Apex environment. Having a user make one query call where the backend needs to make 2 or more calls doesn't scale well especially in bulk processes.   

[wyvern]

True, but given that we have no idea what will or will not hit the QUERY_TOO_COMPLICATED limit, there aren't many options. At least if we had a metric that we could use to determine the threshold, we could work around it on the client side.