>- see footer for list info -<
failing the ability to use any of that (e.g. assuming you can only use
Coldfusion as the data entry interface) then you'll need to set a long
timeout on the template doing the download and process. If you're kicking it
off manually, use cfthread to cart the processing off into an asynchronous
thread so the user doesn't have to sit around.
Then, running individual insert or update statements for each and every
single row is very intensive and labourious. Take a look running multiple
statements in a cfquery rather than running individual ones.
e.g. run over your file, appending a SQL query to a variable. Every X number
of rows, send the SQL variable to the DB via cfquery and clear it, ready to
start again for the next X number of rows. Now. There's a limit to how long
a cfquery can be, so you'll need to play with how many rows you can run at a
time according to how long each SQL statement is.
Also, we discovered when processing many rows (we have an application that
needs to import massive XML files for schools containing many thousands of
pupil academic results) that merely appending lots of text to a string is
quite a strain on the server when done normally (e.g. <cfset strSQLStatement
&= 'INSERT INTO dataTable......'>).
Instead, look at harnessing the underlying java string functions. We use the
following quite a bit and it runs much faster than normal string appending..
<cfset sqlText = createObject("java","java.lang.StringBuilder").init()>
<cfloop ...over your dataset>
<cfsavecontent variable="strSQL">
<cfoutput>INSERT INTO dataTable(yourCols)
VALUES ('#yourValues#')</cfoutput>
</cfsavecontent>
<cfset sqlText.append(strSQLl)>
<cfif NOT currentrow MOD 50 (your number of rows may vary here)>
<cfquery name="qryRunBatch" datasource="#application.dsn#">
#preserveSingleQuotes(sqlText)#
</cfquery>
<cfset sqlText.setLength(0)>
</cfif>
</cfloop>
Don't forget you may have some odd rows left at the end of the loop, and to
execute those too.
Hope this helps.. remember, you should only be doing this when all other
avenues of possibility are closed to you - ie using native SQL Server bulk
import methods.
Cheers,
Rich
On 14 October 2010 07:16, Steve Powell <[email protected]> wrote:
> >- see footer for list info -<
> It would be much easier and more efficient to us SQL Server Integration
> Services and write a small package that would import the csv into your DB
> you don't need direct access to the server a DB login will do. Though you
> will need SSIS running somewhere.
>
>
>
> Sent from my iPhone so please excuse the brevity, poor spelling and any bad
> grammar.
>
> On 14 Oct 2010, at 00:43, "Lee Fortnam" <[email protected]> wrote:
>
> >> - see footer for list info -<
> > Hi All,
> >
> >
> >
> > I know this might be off topic for a CF discussion group but as CF is the
> > language I would be potentially handling this in I'll give it a shot.
> >
> >
> >
> > I have a service which regularly (once every 24 hours) needs to retrieve
> a
> > number of files from an FTP site. The issue is the file is 12MB in size
> as a
> > zipped CSV file. When unzipped the file is some 78MB in size. I need to
> > import all of this CSV file into an existing table, all fields will be
> > imported etc.
> >
> >
> >
> > Is there a GOOD efficient way to do this without killing the DB server or
> > having CF time out. This is on a dedicated instance of CF on a semi
> > dedicated box at CFMX hosting. I have no native access to the MS SQL 2005
> DB
> > file system.
> >
> >
> >
> > Thanks in advance.
> >
> >
> >
> > Lee Fortnam
> >
> >
> >
> > _______________________________________________
> >
> > For details on ALL mailing lists and for joining or leaving lists, go to
> http://list.cfdeveloper.co.uk/mailman/listinfo
> >
> > --
> > CFDeveloper Sponsors:-
> >> - cfdeveloper Hosting provided by www.cfmxhosting.co.uk -<
> >> - Lists hosted by www.Gradwell.com -<
> >> - CFdeveloper is run by Russ Michaels, feel free to volunteer your help
> -<
>
> _______________________________________________
>
> For details on ALL mailing lists and for joining or leaving lists, go to
> http://list.cfdeveloper.co.uk/mailman/listinfo
>
> --
> CFDeveloper Sponsors:-
> >- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -<
> >- Lists hosted by www.Gradwell.com -<
> >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
>
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go to
http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
>- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -<
>- Lists hosted by www.Gradwell.com -<
>- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
