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).