ColdFusion's cfquery failing silently

Question

I have a query that retrieves a large amount of data.

<cfsetting requesttimeout="9999999" >

<cfquery name="randomething" datasource="ds" timeout="9999999" >
    SELECT
        col1,
        col2
    FROM
        table
</cfquery>

<cfdump var="#randomething.recordCount#" /> <!---should be about 5 million rows --->

I can successfully retrieve the data with python's cx_Oracle and using sys.getsizeof on the python list returns 22621060, so about 21 megabytes.

ColdFusion does not return an error on the page, and I can't find anything in any of the logs. Why is cfdump not showing the number of rows?

Additional Information

The reason for doing it this way is because I have about 8000 smaller queries to run against the randomthing query. In other words when I run those 8000 queries against the database it takes hours for that process to complete. I suspect this is because I am competing with several other database users, and the database is getting bogged down.

The 8000 smaller queries are getting counts of col1 over a period of col2.

SELECT 
    count(col1) as count
WHERE 
    col2 < 20121109 
AND 
    col2 > 20121108

According to Adam Cameron's suggestions.

• cflog is suggesting that the query isn't finishing.

• I tried changing the queries timeout both in the code and in the CFIDE/administrator, apparently CF9 no long respects the timeout attribute, regardless of what I tried I couldn't get the query to timeout.

I also started playing around with the maxrows attribute to see if I could discern any information that way.

• when maxrows is set to 1300000 everything works fine
• when maxrows is 1400000 or greater I get this error
• when maxrows is 2000000 I observe my original problem

Update

So this isn't a limit of cfquery. By using QueryNew then looping over it to add data and I can get well past the 2 million mark without any problems.

I also created a ThinClient datasource using the information in this question, I didn't observe any change in behavior.

The messages on the database end are

SQL*Net message from client

and

SQL*Net more data to client

I just discovered that by using the thin client along with blockfactor1="100" I can retrieve more rows (appx. 3000000).

Answer 1

Is there anything logged on the DB end of things?

I wonder if the timeout is not being respected, and JDBC is "hanging up" on the DB whilst it's working. That's a wild guess. What if you set a very low timeout - eg: 5sec - does it error after 5sec, or what?

The browser could be timing out too. What say you write something to a log before and after the <cfquery> block, with <cflog>. To see if the query is eventually finishing.

I have to wonder what it is you intend to do with these 22M records once you get them back to CF. Whatever it is, it sounds to me like CF is the wrong place to be doing whatever it is: CF ain't for heavy data processing, it's for making web pages. If you need to process 22M records, I suspect you should be doing it on the database. That said, I'm second-guessing what you're doing with no info to go on, so I presume there's probably a good reason to be doing it.

Answer 2

Have you tried wrapping your cfquery within cftry tags to see if that reports anything?

<cfsetting requesttimeout="600" >

<cftry>
    <cfquery name="randomething" datasource="ds" timeout="590" >
        SELECT
            col1,
            col2
        FROM
            table
    </cfquery>

    <cfdump var="#randomething.recordCount#" /> <!--- should be about 5 million rows --->

    <cfcatch type="any">
        <cfdump var="#cfcatch#">
    </cfcatch>
</cftry>