Comments

• #1 by James Moberg on 9/30/11 - 7:55 PM

  

  That's not a good enough test... use a bigger dataset or loop over it 100+ times. Try making the initial list an array and loop over that too to see bigger improvements. I quit using lists when looping because it is so slow compared to a struct or array. Once you increase the sample data, you'll notice a huge difference in performance.

• #2 by ppshein on 9/30/11 - 10:33 PM

  

  What you said is true. I gotta test huge amount of data to compare between looping and listoarray. But I'm sure listoarray will be more faster.

• #3 by Jon Briccetti on 10/2/11 - 11:06 AM

  

  If you have to read a file and loop through it, look at <cfloop file="" /> it'll read the file a line at a time - then you wont have to convert anything to an array/list etc.... Should boost preformance significantly. I'm pretty sure this would require CF 9

• #4 by ppshein on 10/2/11 - 8:21 PM

  

  Yap, thanks. But my UAT server is CF9 and Production server is CFMX7. That's why I cannot use <cfloop file=".."> attribute. On the other hand, I also need ListToArray to get single value of each line.

• #5 by Jon Briccetti on 10/3/11 - 7:14 AM

  

  yes, <cfloop file="" /> wont work on the cf7 server. however you can use a few javaclasses to do the same: java.io.LineNumberReader, java.io.BufferedReader & java.io.FileReader - check out Ben Nadel's blog entry about it: http://www.bennadel.com/blog/2011-Reading-In-File-...
  
  also , check out cfhttp - you can "slurp" a csv file directly into a query object using the name attribute. here is another blog entry on doing that from Ben http://www.bennadel.com/blog/1903-Parsing-CSV-Data...
  
  but hey, your way is great too and i know you were simply exploring the benefits of converting the list to an array for performance reasons... so i'm only usggesting these alternative methods so you can find out which way is faster!!!
  
  good luck
  Jon

• #6 by ppshein on 10/3/11 - 7:58 AM

  

  Yap, both links are useful. Frankly, the data file is in our local web-root hence I don't wanna use cfhttp because I feel it's not needed. Note with thanks about <cfloop file=""> and either remind me. Well, I'll use this one when our client wanna upgrade their server to CF9.

• #7 by James Moberg on 10/3/11 - 10:40 AM

  

  Reading a CSV file using CFHTTP is insanely fast & automatically converts your CSV file into a query object. If your CSV file allows it (some exported by Excel are not generated properly) and you can live with your CSV file being in the webroot for a moment, I recommend using CHTTP over CFFILE (especially when it comes to performance.)
  
  If your CSV file has formatting issues, OpenCSV will allow you to read it. It doesn't need to reside in the webpath (like CFHTTP.) I've previously used it with CF7 and highly recommend it. If you are able to install it on the server, it's a better solution when reading client-provided CSV data where you have no control over the format.
  http://www.bennadel.com/blog/1903-Parsing-CSV-Data...
  
  When timing performance, I usually use GetTickCount() (to save the execution time as a variable) or use CFTimer.
  http://cfdocs.org/cftimer

• #8 by James Moberg on 10/3/11 - 10:48 AM

  

  [Import, not array-related]
  
  Another import technique I've been using using is SQL BULK IMPORT. I have to load multiple large tab-delimited files on a schedule and Microsoft SQL Server actually handled the import fast, better and used less code than ColdFusion. (CFFile read and looping was causing timeouts and memory issues.) I had to create a temporary VIEW in order to import the un-typed data safely.
  http://cfsearching.blogspot.com/2008/08/ms-sql-sel...
  
  Here's some sample code:
  CREATE VIEW TempImport AS SELECT #ColumnHeaderstoImport# FROM MyDatabaseTable
  
  BULK INSERT TempImport FROM 'c:\importfile.txt' WITH(CODEPAGE='65001', FIELDTERMINATOR='\t', ROWTERMINATOR='\n', KEEPNULLS, FIRSTROW =2)
  
  DROP VIEW TempImport

• #9 by ppshein on 10/3/11 - 8:58 PM

  

  @James,
  
  Thanks. The first post will be kinda useful for me using with cfhttp. I haven't noticed before using cfhttp to parse CSV file will be faster than normal looping. I'll try to use soon.
  
  For second post, I cannot use SQL BULK IMPORT because we're using Oracle instead. Anyway, thanks.