>- see footer for list info -< Thanks Simon, Sorry but have been out of the office all day and only just picked up email, will give it a go.
Lee Fortnam -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Simon Baynes Sent: 11 September 2006 07:37 To: Coldfusion Development Subject: Re: [CF-Dev] Import of 10k records from a tab delimited txt >- see footer for list info -< I have not used CSVToQuery to but I can telll you that using a loop over a list to convert a CSV is very slow and can be improved a great deal by sing java. If you use imagine your CSV file is in a variable called myCSV then this will prodice an array of each line:- <cfscript> CrLf = chr(13) & chr(10); csvArray = createObject("java", "java.lang.String").init(myCSV).split(CrLf); </cfscript> Then you can loop through the array. I refactored an import I had that was using cfloop over a list delimited by line breaks to use this method and it went from taking 2 minutes to 3 seconds. Arrays are superfast and lists are very slow, well at least this has been the case since MX. As Russ suggests you should not go CSV > Query > XML > SP I would just go CSV > XML > SP as it will be much quicker. On 9/10/06, Lee Fortnam <[EMAIL PROTECTED]> wrote: > >- see footer for list info -< > Okay, > > So I thought I was getting somewhere. The following page > http://www.appointmentm8.com/samplescripts/test/xmlStoredProcInsert2.c > fm is the one I am playing with. > > The problem seems to be that the CSVToQuery is taking a long time (for > 1054 records approx 10 or 11 seconds), the number manipulation that follows, i.e. > converting 0121 287 2343 to 441212872343 takes only a second > considering it is looping through each of the records in the query and > performing an update. The conversion to XML is taking approx 3 or 4 > seconds per 1000 rows so that is not too bad and the DB insert is taking very little. > > I have been setting the timeout of the page using cfsetting to just > over the 20 second mark which is the rough time to cater for 1000 > records. If there are more records in the file, this timeout setting > is automatically adjusted to go to 40 seconds for 2000 records etc. > > I would therefore expect the page to take about 200 seconds for 10K > records but the page is failing if I use a file this big. > > Have I just reached a limitation or is there something I am doing wrong. > > This really is proving to more of a pain in the bum than I expected. > > 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 -< > -- Simon Baynes www.simonbaynes.com _______________________________________________ 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 -<
