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

Question

I have a fairly simple query (this time) in which I need ALL the results (I store them in an Excel spreadsheet). The request itself leaves the server, so how do I run it without this?

Answer 1

The easiest way is to break the request domain into several parts. For example, add an expression in the WHERE clause that selects only the first half of the key range, then run the second query to select the lower half. Then combine the output.

Answer 2

You can increase the request timeout for the page:

<cfsetting requestTimeout="3600" />

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

"". , , , 100 1000 <cfflush> . coldFusion, , SQL, CFML- , ( ). , coldFusion, , "" (.. varchars ).

Answer 3

, .

, . , , , . - , . .

, , .

. , , -.

. , java.io.BufferedWriter . CF , BufferedWriter - . CFC, , .

<!--- init --->
<cffunction name="init" access="public" returntype="Any" output="false">
    <cfargument name="name" type="string" required="true">
    <cfset var local = {}>

    <!--- name of file when downloading --->
    <cfset variables.name = arguments.name & ".xls">
    <!--- name of temp file --->
    <cfset variables.filename = CreateUUID() & ".csv">
    <!--- full path to temp file for downloading --->
    <cfset variables.fullfilename = expandpath("/_temp") & "\" & variables.filename>
    <!--- file write java object --->
    <cfset variables.filewriter = CreateObject("java","java.io.FileWriter").init(
            variables.fullfilename
            ,JavaCast("boolean","true")
        )>
    <!--- buffered writer java object --->
    <cfset variables.bufferedwriter = CreateObject("java","java.io.BufferedWriter").init(
                variables.filewriter
            )>
    <!--- row delimeter --->
    <cfset variables.row = chr(10)>
    <!--- col delimeter --->
    <cfset variables.col = chr(9)>
    <!--- header container --->
    <cfset variables.headers = []>
    <!--- data container --->
    <cfset variables.data = []>
    <cfset newrow()>
    <cfreturn this>
</cffunction>


<!--- addheader --->
<cffunction name="addheader" access="public" returntype="void" output="false">
    <cfargument name="str" type="string" required="true">
    <cfset arrayappend(variables.headers, arguments.str)>
</cffunction>

<!--- newrow --->
<cffunction name="newrow" access="public" returntype="void" output="false">
    <cfset arrayappend(variables.data, arraynew(1))>
    <cfset variables.data_counter = arraylen(variables.data)>
</cffunction>

<!--- adddata --->
<cffunction name="adddata" access="public" returntype="void" output="false">
    <cfargument name="str" type="string" required="true">
    <cfset arrayappend(variables.data[variables.data_counter], arguments.str)>
</cffunction>

<!--- flush --->
<cffunction name="flush" access="public" returntype="void" output="false">
    <cfset var local = {}>

    <!--- write headers --->
    <cfset local.counter = 0>
    <cfset local.headers_count = arraylen(variables.headers)>
    <cfloop array="#variables.headers#" index="local.header">
        <cfset local.counter++>
        <cfset variables.bufferedwriter.write(local.header & variables.col)>
    </cfloop>

    <cfif not arrayisempty(variables.headers)>
        <cfset variables.bufferedwriter.write(variables.row)>
    </cfif>

    <!--- write data --->
    <cfloop array="#variables.data#" index="local.data">
        <cfloop array="#local.data#" index="local.cell">
            <cfset variables.bufferedwriter.write(local.cell & variables.col)>
        </cfloop>
        <cfset variables.bufferedwriter.write(variables.row)>
    </cfloop>

    <cfset variables.bufferedwriter.close()>
    <cfsetting showdebugoutput="No">
    <cfheader name="Content-Description" value="File Transfer">
    <cfheader name="Content-Disposition" value="attachment;filename=#variables.name#">
    <cfcontent type="application/vnd.ms-excel" file="#variables.fullfilename#" deletefile="true" reset="true">
</cffunction>

Answer 4

, , , , . CF , , , 5- .

SQL Server Oracle, , CFQUERY , . , .

, , , . , :

• . DB, XML .. , .

• (, Java, DTS CF), . , , , . CF-, , , , .

• , , - , . , , , , CSV , .

, . , :

• . T-SQL?
• , , .
• 2-3 , .

Answer 5

, .

<cfsetting 
enableCFoutputOnly = "yes|no" 
requestTimeOut = "value in seconds"
showDebugOutput = "yes|no" >

Answer 6

. , . db.

, , group by where, having . , .

, , db.

Answer 7

I would throw the request in a separate thread, loading it into a constant area (like a session). Go to the page that checks for the availability of the request. Repeat the check until a request appears, and then go to the page that displays / processes / cancels it.