CFFILE performance problems- any ideas?

[rtefft]

We use CF as a front end to Oracle. We have a report subsystem which generates the report output files on the DB server. To get them to the CF server, we use a little trick... we have Oracle write the file's contents to a temp table, which CF then queries and writes line-by-line to a file on the CF server using CFFILE. The major benefit of this approach is that it handles the security and I/O protocols internally; there is no FTP or other special connection setup required. (very hand given the CF and DB servers might have a firewall between them, and how sticky NMCI folks are about FTP).

Oracle can load even larger files (7mb) quickly, and CF can retrieve the results from the temp table well. However, the terrible performance of CFFILE kills us. Since it does the whole open-write-close thing every time, it wasts a huge amount of overhead when you write larger files. For our 7mb file, it did the open-write-close thing 95,000 times, once for each line of text.

Does anyone know any tweaks to CFFILE or better ways to write a file on the DB server? We have CF 4.5.1, does CF 5.0 do it better?

Thanks in advance,
Rich ____________________________
Rich Tefft
PL/SQL Programmer

 

[webmigit]

May I see the file writing code? I think I can help...

thicks@wisernet.com

 

[pcorreia]

I have experienced similar problems with ColdFusion's inefficient way of writing to files. Instead of writing a line to the file for each record in the query, try keeping the text to be written in a variable and only writing the contents of that variable to the file when you are done looping over the query? Like this:

<cfquery name=&quot;myQuery&quot;>
  Your SQL here
</cfquery>
<cfset textToWrite = &quot;&quot;>
<cfloop query=&quot;myQuery&quot;>
  <cfset textToWrite = textToWrite & myQuery.queryColumn>
</cfloop>
<cffile action=&quot;WRITE&quot; file=&quot;myFileName&quot; content=&quot;#textToWrite#&quot;>

This way of doing it is much more memory-intensive, but if you have enough memory, it provides much better performance than writing the file line-by-line.

If your CF server is on Windows, you can also use <CFOBJECT> to call the FileSystemObject ActiveX object, which provides you with the capability to open the file once, write multiple lines to it, and then close the file. I haven't personally done this so I can't vouch for performance. But in theory it should be faster than a ton of <cffile action=&quot;write&quot;> tags.

 

[rtefft]

PCorreia,

You were right, writing to a buffer works much faster... up to a point. We got optimal performance when we wrote 100 lines to a variable and then wrote the variable to the file. Note we had to manually put newline chars after each line when we appended to the variable. As I undestand it, CF doesn't see a char string as a single variable, but as an array of 1-byte elements. If so, this would explain the performance drop when using larger strings; the size of the array made it unwieldy for CF to handle efficiently. Oh for the efficiency and elegance of the Old Days...

Thanks for the help,
Rich ____________________________
Rich Tefft
PL/SQL Programmer

 

[CFDude]

I don't have much to add, but I did notice in the release notes of CF 5 that cffile was fixed to allow larger file uploads. I don't know if this will help a cffile write. Now that CFMX is out, I don't know if that would be better.

The only other suggestion I would make is perhaps add some more ram to the cf server.

 

[rtefft]

CFDude,

Thanks for the info - we're getting copies of 5 and MX for eval (using 4.5 now). When we moved to the 100-line method instead of 1-line at a time, the difference was great. The 1-line method took about 20+ minutes to upload an 8mb file, but the 1oo-line method took only 3.

Rich ____________________________
Rich Tefft
PL/SQL Programmer