Skip to main content
C
coatimundiAZ1
Participant
February 4, 2011
Question

Large Text Import to SQL

  • February 4, 2011
  • 1 reply
  • 1371 views

I was hoping someone could offer some insight on this issue, because I have the feeling that it's not a terribly rare one.
I have a relatively large text file in fixed-width format, roughly 40,000 rows, that needs to be uploaded and imported into a table. This works fine for other tables but, for one table with roughly 15 fields, the script times out consistently. If I cut it down to roughly 20,000 rows, it runs fine.
I'm executing this through a file loop and doing individual insert queries on each iteration. At the end, a stored procedure also has to be run, and that takes several seconds to execute.
Simply changing the request timeout in CF Admin is not an option.
I'm not overly familiar with threading, so I'm not sure if that could help or not. I did notice that it maxes out at 5,000.
Maybe I could loop through parts of the file and that would help mitigate the timeout?

Any help appreciated.

Thanks.

    This topic has been closed for replies.

    1 reply

    Owainnorth
    Owainnorth
    Inspiring
    February 4, 2011

    If it's timing out then almost certainly your methodology is wrong.

    ColdFusion is not really made for jobs like this, I'd  take a look into using something like SSIS to import the data. If a web  interface must be involved, use CF to upload and save the file, then  call a stored proc or SSIS packsge to import the data.

    CF will take forever then crash, a native database method will suck it all up in seconds

    R
    Reed_Powell-ttnmOb
    Inspiring
    February 4, 2011

    I agree that it is best to use the database ETL tools like SSIS whenever possible.  They will alwasy be faster.  But sometimes you don't have a choice. But to help you we need more information about what you are doing.  What database product and version?  What format is the incoming file (CSV, XML, ...).

    I do a lot of this sort of thing,  and my experience is that the problems are usually related either to the fact that the CF code ends up doing individual database INSERTs for each row, which makes it run long (but not crash, so long as the request timeout is high enough), or due to memory problems in parsing XML data (which can crash the job). 

    I sounds like you are getting a delimited file, since you say that you are processing it line by line.  So try this to narrow down the field of problems and solutions: Replace the CFQUERY tag that does the INSERT with a CFFILE that appends the data to a file.  You'll need a dummy CFFILE before the loop to write out column headers.  How long does this run?  It will probably run pretty fast. 

    Now look into what options you have for your database product for doing bulk inserts (hint, if it is SQL Server then it's called BULK INSERT).  If you have that option, then try doing a bulk insert of the file you just created.  It is very finicky in terms of all columns being populated, text delimiters, etc., so it might take some trial and error.  But it will be fast.  You will need to be sure that both CF server and the database server have access to wherever you store the file, and that may have some implications on the login account that each of their services are running under (see other threads for repeated discussions of that topic).


    Good luck,

    -reed

    C
    coatimundiAZ1Author
    Participant
    February 4, 2011

    Thanks for getting back to me so quickly.

    To answer some follow-up questions:

    - Yes, it's SQL Server (sorry, I meant to put that in the subject line)

    - Yes, it has to be through a web interface

    - It is a fixed-width text file

    Per bulk insert, I've never done it with fixed-width, so I wasn't sure how to even go about it, but I think that's a good place to start. However, a couple of potential problems:

    - The SQL Server instance is not on the same server as the web server

    - The SQL Server likely is not able to connect out, so I couldn't write to the web server, then have SQL Server pick that file up. But I'm really not sure and could probably try that (I'm a very short-term contractor with these people).

    I'll post the relevant code and that may help narrow down what needs to happen. I don't know that I'm doing everything as efficiently as it could be done.

    <cffile action="upload" destination="#ExpandPath(uploadDir & filename)#"

    fileField="theUpload" accept="text/plain,text,application/zip,application/x-zip-compressed" result="uploadStr">

    <cfif uploadStr.contentType NEQ 'text/plain' AND uploadStr.contentType NEQ 'text'>

    <cfzip action="list" file="#ExpandPath(uploadDir & filename)#" name="qryZip">

    <cfset unzipFilename = "">

    <cfloop query="qryZip">

    <cfif Attributes.currentTable EQ ListFirst(tableList) AND qryZip.name EQ 'add'>

    <cfset unzipFilename = 'add'>

    <cfelseif Attributes.currentTable EQ ListLast(tableList) AND qryZip.name EQ 'pl'>

    <cfset unzipFilename = 'pl>

    </cfif>

    </cfloop>

    <cfif Len(unzipFilename) EQ 0>

    <cfset errorCode = 'zipNoFile'>

    <cfelse>

    <cfzip action="unzip" file="#ExpandPath(uploadDir & filename)#" destination="#ExpandPath(uploadDir)#">

    <cffile action="delete" file="#ExpandPath(uploadDir & filename)#">

    <cffile action="rename" source="#ExpandPath(uploadDir & unzipFilename)#" destination="#ExpandPath(uploadDir & filename)#">

    </cfif>

    </cfif>

    <cfcatch type="any">

    <cfset errorCode = 'upload'>

    <cfset errorMsg = cfcatch.message>

    </cfcatch>

    </cftry>

    <cfif Len(errorCode) EQ 0>

    <cftransaction>

    <cftry>

    <cfquery result="qryDelete" datasource="#Application.dsn#">

    DELETE FROM #Attributes.currentTable#

    </cfquery>

    <cfset lineNum = 1>

    <cfset zipsDone = "">

    <cfloop file="#ExpandPath(uploadDir & filename)#" index="line">

    <cfif lineNum GT 1>

    <cfquery result="qryInsert" datasource="#Application.dsn#">

    INSERT INTO #Attributes.currentTable# (

    <cfloop array="#fields#" index="f">

    <cfif f[1] NEQ fields[1][1]>,</cfif>

    #f[1]#

    </cfloop>

    ) VALUES(

    <cfloop array="#fields#" index="f">

    <cfif f[1] NEQ fields[1][1]>,</cfif>

    <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Trim(Mid(line,f[2],f[3]))#" maxlength="#f[4]#">

    </cfloop>

    )

    </cfquery>

    </cfif>

    <cfset lineNum++>

    </cfloop>

    <cfstoredproc datasource="#Application.dsn#" procedure="dbo.sp_BatchUpload">

    <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#Attributes.currentTable#">

    <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#DateFormat(Now(),'mmddyyyy')#-#TimeFormat(Now(),'HHmmss')#-BATCH-AFTER">

    <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#Application.userInfo.userID#">

    </cfstoredproc>

    <cfcatch type="any">

    <cfif cfcatch.type EQ 'database'>

    <cfset errorCode = 'db'>

    <cfset errorMsg = cfcatch.sql & '<br/>' & cfcatch.queryError>

    <cfelse>

    <cfset errorCode = 'db'>

    <cfset errorMsg = cfcatch.message>

    </cfif>

    <cftransaction action="rollback">

    </cfcatch>

    </cftry>

    <cfif Len(errorCode) EQ 0>

    <cffile action="delete" file="#ExpandPath(uploadDir & filename)#">

    <cftransaction action="commit">

    <cfset showForm = false>

    <cfelse>

    <cftransaction action="rollback">

    </cfif>

    </cftransaction>

    </cfif>

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices