The request has exceeded the allowable time limit error with CFQUERY tag

Question

I have started getting this error recently:

The request has exceeded the allowable time limit Tag: CFQUERY

It seems to be whenever the Bing bot visited my site. I get system notifications in the span of 5 minutes about the error. So I will get like 7 to 10 in a row. The error all point to the one query:

    <CFQUERY DATASOURCE="#datasource#" USERNAME="#username#" PASSWORD="#password#" NAME="queryname" CACHEDWITHIN="#CreateTimeSpan(0,0,2,0)#">
    SELECT products.field1, products.field2, products.field3, products.field4, products.field5, products.field6, products.field7, products.field8, products.field9, products.field10, products.field11, company.field1, company.field2, company.field3, company.field4, company.field5, company.field6, company.field7
    FROM products JOIN company
    ON products.field1 = company.field1
        WHERE products.field12 = <cfqueryparam value = "#catd#" cfsqltype = "cf_sql_integer" maxLength = "2">
        ORDER by products.field13 DESC
    </CFQUERY>

It basically select all the products in a category and get the associated company information with it and paginate them. The results returned for each category ranges from 2K to 30K records. There are up to 30 categories.

The errors that came in looks like this:

The request has exceeded the allowable time limit Tag: CFQUERY  <br>The error occurred on line 152.
Query: cat=18&page=1803
The request has exceeded the allowable time limit Tag: CFQUERY  <br>The error occurred on line 152.
Query: cat=2&page=211
The request has exceeded the allowable time limit Tag: CFQUERY  <br>The error occurred on line 152.
Query: cat=7&page=691
The request has exceeded the allowable time limit Tag: CFQUERY  <br>The error occurred on line 152.
Query: cat=6&page=451
The request has exceeded the allowable time limit Tag: CFQUERY  <br>The error occurred on line 152.
Query: cat=14&page=417

This indicates that the Bing bot is hitting many sections of the site at the same time on different categories and pages.

Two weeks ago I got

The request has exceeded the allowable time limit Tag: CFQUERY   

and

GC overhead limit exceeded null

So I increased the JVM memory from 1024 to 2048. But it may have only temporary fix the problem. Now two weeks later, the "exceeded the allowable time limit" error has come back.

Could this problem caused by the Bing bot, or is it something to do with my code, server memory or CF set up?

Thanks in advance.

Answer 1

If you're only showing 20 records per page, then your query should only ever return 20 per request. You would rather have a bot index 200 pages with 20 records each than trigger timeouts on a single page, which has performance impact on the rest of your application and live clients all day long.

The call to FOUND_ROWS() will return the total number of records that match your query. You can use this to create page links.

<cfparam name="url.p" type="numeric" default="1">
<cfset queryLimit = 20>
<cfset queryOffset = 0>
<cfif url.p GT 1>
    <cfset queryOffset = p * queryLimit>
</cfif>

<CFQUERY NAME="queryname" ...>
    SELECT 
        products.field1
        , products.field2
        , products.field3
        , products.field4
        , products.field5
        -- etc.
        , FOUND_ROWS() as total_rows
    FROM 
        products 
    INNER JOIN 
        company ON products.field1 = company.field1
    WHERE 
        products.field12 = <cfqueryparam value = "#catd#" cfsqltype = "cf_sql_integer" maxLength = "2">
    ORDER BY 
        products.field13 DESC
    LIMIT #queryLimit# 
    OFFSET #queryOffset#
</CFQUERY>

Update 2017-11-07

After better reading the documentation for FOUND_ROWS(), my assumption that it worked similar to the relative function in SQL Server was incorrect. You'll need to do something like this. Maybe you can run two select statements in a single <cfquery>, but I'm not sure if that will return two query objects for the single query name variable.

<CFQUERY NAME="queryname" ...>
    SELECT 
        SQL_CALC_FOUND_ROWS
        , products.field1
        , ...
    LIMIT #queryLimit# 
    OFFSET #queryOffset#
</CFQUERY>
<CFQUERY NAME="foundRows" ...>
    SELECT FOUND_ROWS() as totalCount;
</CFQUERY>

Answer 2

This is not exactly an answer, but this is too long for a comment

I am looking at this issue and it just screams to me that you are pulling back more data than you are expecting. Run something like

<CFQUERY DATASOURCE="#datasource#" USERNAME="#username#" PASSWORD="#password#" NAME="queryname">
SELECT COUNT(products.field1) AS productCount
FROM products JOIN company
ON products.field1 = company.field1
    WHERE products.field12 = <cfqueryparam value = "#catd#" cfsqltype = "cf_sql_integer" maxLength = "2">
    ORDER by products.field13 DESC
</CFQUERY>

See if you are getting too many rows. I try to keep the row count to below 1000, although I have done up to 3000 on a page.

Second Idea

Run an missing index query on the DB. Make sure you are properly indexed.

Third Idea

When I look at

products.field1, products.field2, products.field3, products.field4, products.field5, products.field6, products.field7, products.field8, products.field9, products.field10, products.field11, company.field1, company.field2, company.field3, company.field4, company.field5, company.field6, company.field7

I don't like all these columns. If you really have to return this into an HTML table, do the string building on the MySQL. Usually bad for SQL to build HTML, but maybe this is one of those times.