>- see footer for list info -< oh yea, and if you do use cfthread to kick the whole thing into a non-attended thread, or you're running this from a cfschedule etc, then judicious use of <cflog> or <cfmail> to let you know of any problems, or when it succeeds etc is very very useful.
Personally, I'd set a DB column to denote when a process has started, and then use simple polling to check the status of the column. If it hasn't completed after a set amount of time and no error has been received, then send a <cfmail> to warn you, or do something equally notifiable. If it's an attended process and you're using cfthread, then set the progress of a) the import's status and b) each iteration to an application variable and use JS ajax polling on an otherwise static page to check the application status and return that status to the user via JSON. We use this method to present status/progress bars of import information to the user, so they can see how far their import has progressed, but don't have to sit around waiting for it, they can get on with other stuff. Cheers, Rich On 14 October 2010 09:35, Rich Wild <[email protected]> wrote: > 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 -<
