How to run a very large query (SQL Server and ColdFusion)

Question

I've got a reasonably simple query (this time) that I need ALL the results back from (I'm storing them in an Excel spreadsheet). The query itself times out the server, so how do I go about running it without that happening?

Answer 1

You can increase the request timeout for the page:

<cfsetting requestTimeout="3600" />

This will make sure that you have time to process all of the entries.

You may also want to break the list up into "chunks". It will require some tuning to find out what the optimum chunk size is, but you could grab the results 100 or 1000 lines at a time, and use <cfflush> to push the results out to the screen as they become available. This approach also has the advantage of using less memory on the coldFusion server, as every row pulled back from the SQL server gets loaded into CFML memory, and sits there until the query object variable is overwritten or goes out of scope (at the end of the page). This means you can easily fill up coldFusion memory reading several hundred thousand rows, especially if the rows are "wide" (i.e. containing big varchars or texts).