>- see footer for list info -< I did originally have all of the CFScript in 1 block but it was simply timing out when I ran it with larger record numbers. In order to see what was going on and how long each stage was taking with different record sizes I split them out. That is the only reason.
Unfortunately I am now going to be in the car for the next 6 or 7 hours, of what run so if I don't respond to anyone, not that I am ignoring you, just haven't picked up the email. Thanks in advance Lee Fortnam -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Duncan Cumming Sent: 12 September 2006 14:48 To: [email protected] Subject: RE: [CF-Dev] Import of 10k records from a tab delimited txt >- see footer for list info -< you seem to be using CFScript at various places when doing array functions. Why not convert the whole thing to cfscript? I didn't notice anything that wouldn't work in a script block; the saving would probably be minimal, but might still shave a few seconds if you're looping 10000 times. And, you seem to loop 4 times, using the same loop attributes. Would it be possible to loop once? Duncan Cumming New Media Developer Customer Relations Management / Education Fife Council 700 4105 / 01592 414105 >>> [EMAIL PROTECTED] 12/09/2006 14:35 >>> >- see footer for list info -< Okay so the Java array conversion has helped heaps but it still appears that the conversion of the 3 fields in each array record is taking the time and causing the cfloop to time out when the record number increases. The page http://www.appointmentm8.com/samplescripts/test/xmlStoredProcInsert.cfm?reco rdsToDo=1000 willnow take a 'recordsToDo' variable so I can test it without uploading the file each time. There are some 10530 records in the actual file Start = 14:24:01 File Read In = 14:24:01 Array Created = 14:24:01 recordCount = 10530 Rows Created = 14:24:01 PAT ID Created = 14:24:03 NumberType Created = 14:24:05 Number Created = 14:24:06 Database Updated = 14:24:06 In order to see where the time was being taken, I split the page into elements, i.e. create the array from the file, create Rows in XML, create PAT_IDs for each row, create NumberType for each row, create Number for each row (as this includes the manipulation of the number itself to ensure correct formatting) and then finally the call to the DB. The relevant code for each section is below: Create Array from File variable: <cfscript> CrLf = chr(13) & chr(10); csvArray = createObject("java", "java.lang.String").init(inboundTextFile).split(CrLf); </cfscript> Create Basic XML File: <cfset MyXMLDoc = xmlNew()> <cfset MyXMLDoc.xmlRoot = xmlElemNew(MyXMLDoc,"Contacts")> <cfset MyXMLDoc.Contacts.xmlAttributes["columns"] = 3> <cfset MyXMLDoc.Contacts.xmlAttributes["rows"] = #recordsToDo#> Loop through array doing row creation: <cfloop from="1" to="#recordsToDo#" step="1" index="Count"> <cfscript> arrayAppend(MyXMLDoc.Contacts.xmlChildren,xmlElemNew(MyXMLDoc,"row")); </cfscript> </cfloop> Loop through array doing Pat_ID Creation: <cfloop from="1" to="#recordsToDo#" step="1" index="Count"> <cfscript> arrayAppend(MyXMLDoc.Contacts.row[count].xmlChildren,xmlElemNew(MyXMLDoc,"Pa t_ID")); MyXMLDoc.Contacts.row[count]["Pat_ID"].xmlText = #ListGetAt(csvArray[count],1,chr(9))#; </cfscript> </cfloop> Loop through array doing NumberType Creation: <cfloop from="1" to="#recordsToDo#" step="1" index="Count"> <cfscript> arrayAppend(MyXMLDoc.Contacts.row[count].xmlChildren,xmlElemNew(MyXMLDoc,"Nu mberType")); MyXMLDoc.Contacts.row[count]["NumberType"].xmlText = #ListGetAt(csvArray[count],2,chr(9))#; </cfscript> </cfloop> Loop through array doing Number Creation <cfloop from="1" to="#recordsToDo#" step="1" index="Count"> <cfset tempNumber = #REReplace(ListGetAt(csvArray[count],3,chr(9)),"[^0-9]","","ALL")#> <cfif #ListGetAt(csvArray[count],2,chr(9))# EQ "Mobile Phone"> <cfif LEFT(tempNumber,1) EQ "7"> <cfset tempNumber = "44" & #tempNumber#> <cfelseif LEFT(tempNumber,2) EQ "07"> <cfset tempNumber = "44" & #RIGHT(tempNumber,LEN(tempNumber) - 1)#> </cfif> <cfelse> <cfif LEFT(tempNumber,4) EQ "0121"> <cfset tempNumber = #attributes.defaultSTDCode# & #RIGHT(tempNumber,LEN(tempNumber) - 4)#> <cfelseif LEFT(tempNumber,2) EQ "07"> <cfscript> MyXMLDoc.Contacts.row[count]["NumberType"].xmlText = "Mobile Phone"; </cfscript> <cfset tempNumber = "44" & #RIGHT(tempNumber,LEN(tempNumber) - 1)#> <cfelseif LEFT(tempNumber,3) EQ "021"> <cfset tempNumber = #attributes.defaultSTDCode# & #RIGHT(tempNumber,LEN(tempNumber) - 3)#> <cfelseif LEFT(tempNumber,1) EQ "0"> <cfset tempNumber = "44" & #RIGHT(tempNumber,LEN(tempNumber) - 1)#> <cfelse> <cfset tempNumber = #attributes.defaultSTDCode# & #tempNumber#> </cfif> </cfif> <cfscript> arrayAppend(MyXMLDoc.Contacts.row[count].xmlChildren,xmlElemNew(MyXMLDoc,"Nu mber")); MyXMLDoc.Contacts.row[count]["Number"].xmlText = #tempNumber#; </cfscript> </cfloop> Know it is a lot to ask but this is really proving to be frustrating as the typical number or records we will be sent is 10K, all help so far has been great! Lee Fortnam -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Simon Baynes Sent: 12 September 2006 11:40 To: Coldfusion Development Subject: Re: [CF-Dev] Import of 10k records from a tab delimited txt >- see footer for list info -< Not that I am aware of, structs are not native to Java so you would hae to do that in CF. But once you have broken the import into an array of lines, deconstructing further should be pretty painless. On 9/12/06, Lee Fortnam <[EMAIL PROTECTED]> wrote: > >- see footer for list info -< > Oh my god!!! > > It converts not only the 1K records to array quick it was doing the > 10K records in less then a second! Huge thanks > > Will the same work for cycling through the array records to split them > into a structure, reason being I need to convert the line (currently > 1{tab}Telephone - Home{tab}### ####) to allow me to run some > conversion on the number shown in #'s at the moment. > > Is it possible to change the code you gave to do a structure creation? > > 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 -< > -- 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 >-< ********************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed and should not be disclosed to any other party. If you have received this email in error please notify your system manager and the sender of this message. This email message has been swept for the presence of computer viruses but no guarantee is given that this e-mail message and any attachments are free from viruses. Fife Council Tel: 08451 55 00 00 ************************************************ _______________________________________________ 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 -<
