I don't know anything about the EXCEL COM object, but there is nothing suspicious in the CF code that I can see. No bottle necks that is.
I could start putting things like : "DONE" <cfabort /> at differt parts of the code (of course don't actually update the database). See if you can narrow down which part of the algorithm is taking a long time. ...................... Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ -----Original Message----- From: Robert Harrison [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 07, 2006 4:34 PM To: CF-Talk Subject: HELP: Speeding up a Collection Below is an excert from a page that uploads an Excel spreadsheet parses it into a collection and outputs the data from the spreadsheet (actually it updates a data base, but output is OK for testing). Anyway, it works OK for twenty or thirty records, but it takes forever to process when it gets more records and times out when it gets to a couple of hundred. AM I DOING SOMETHING WRONG? ANY IDEAS? Thanks, Robert Harrison <cftry> <cfobject action="connect" class="Excel.Application" Name="objExcel" type="COM"> <cfcatch> <cfobject action="create" class="Excel.Application" name="objExcel" type="COM"> </cfcatch> </cftry> <cfset objBooks = objExcel.Workbooks> <cfset objBook = objBooks.Open("#rootpath#\admin\html_mail\mail_list\upload\#cffile.serve rFil e#")> <cfset objBook.Activate()> <cfset objSheets = objBook.WorkSheets> <cfset objSheet = objSheets.Item(Val(1))> <cfset Records = objSheet.UsedRange.Rows.Count> <cftransaction> <cfloop from="2" to="#records#" index="cnt"> <cfset record_count = record_count + 1> <CFSET objRange = objSheet.Range("A#cnt#,B#cnt#,C#cnt#,D#cnt#,E#cnt#,F#cnt#,G#cnt#,H#cnt#, I#cn t#,J#cnt#,K#cnt#,L#cnt#,M#cnt#")> <cfset collection_cnt = 1> <cfset record = structnew()> <cfloop collection="#objrange#" item="this"> <cfset record[#listgetat(column_headers,collection_cnt,",")#] = this.text> <cfset collection_cnt = collection_cnt+1> </cfloop> <cfoutput>'#record.email#', '#URLEncodedFormat(record.website)#', '#record.firstname#', '#record.lastname#', '#record.title#', '#record.company#', '#record.phone#', '#record.fax#', '#record.address1#', '#record.address2#', '#record.city#', '#record.state#', '#record.zip#', 'Record Added from Upload Merge File on #dateformat(now())#', #now()#, '#session.user#', 0, 1, 2<br></cfoutput> </cfloop> </cftransaction> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259529 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
