CFFile taking too much time to export csv

[Madhabdhk]

Hello everyone,

I'm facing significant performance issues after upgrading from ColdFusion 2018 to ColdFusion 2023. It's not really a migration, as I have both servers running CF 2018 and CF 2023. I recently created CF 2023 from scratch. Specifically, I'm facing much longer processing times for a task involving CSV exports. Interestingly, the same CF 2023 setup works well on my local computer (developer setup on Windows 10) but takes too much time on the CF 2023 Enterprise server.

• CF 2023 (Local): the export takes 20.42 seconds. 
• CF 2018 (DEV 01): the export takes 11.71 seconds.
• CF 2023 (DEV 02): The same export takes 4.9 minutes. 

Question? 

1. Are there specific configurations or settings in CF 2023 Enterprise that might be causing these delay?
2. Are there known issues or patches for CF 2023 Enterprise that address performance problems?
3. Any suggestions on additional steps I can take to troubleshoot and resolve these performance issues on the server?

Any insights or recommendations would be greatly appreciated!

Thanks

[Charlie Arehart]

I'll say that on the surface there's no setting I'd think of that's specific to generating csv's.

But given what you've shared, a first guess is that the heap size of your new cf2023 setup is the default of 1024MB,, but perhaps your instances where things work well it may be much higher. And perhaps your process of creating the csv is memory intensive, leading to excessive jvm garbage collection. That could lead to longer request times. But again that's just a guess. 

While your first couple of questions are typical of how most people go about trying to solve such things, I'd follow the lead of your third one and propose that the best way to solve such a problem is to use a tool to help see what IS going on inside of cf/the jvm.

For that, I find fusionreactor to be the best tool (with its free 14-day trial). I use that almost daily to help people find, understand, and resolve such cf (and Lucee) performance problems. Beyond that, cf2018 and above offers the cf PMT tool--which has a much larger footprint and configuration effort.

Either tool, or other Java APMs, or even lowly free jvm tools can help as well. I did a talk on these options in May, available at carehart.org/presentations. Or you can find more info about these various tools online, or perhaps someone else here will offer you other thoughts.

Let us know it goes. 

[Madhabdhk]

Hi Charlie,

Thank you for your detailed response. I have checked the heap size configuration, and it's the same on all my local setup, the CF 2018 Old Dev site, and the CF 2023 dev site. 

Regarding monitoring tools, I am currently using CF PMT but haven't found any leads on the performance issue so far. Given this, I will consider your recommendation to try FusionReactor for a more in-depth analysis. Please find the image attached. 

One thing that puzzles me is that the same setup works well on my local CF 2023 but not on the CF 2023 Enterprise server. I personally did the setup, and all configurations are the same on both CF 2023 servers. Is there anything I might be missing that could cause this discrepancy?

If anyone else has faced similar issues or has further suggestions, I’d love to hear your thoughts. 

Thanks again,

Madhab Dhakal

[Charlie Arehart]

No one else has raised an issue like yours, no. Not specifically. But generally, sure.

As for your issue, there could be any of many potential explanations for the slowness. You may be presuming there's some bug in cf, but I'd still be inclined that you'll find some environmental difference in that server, whether related to the csv file (perhaps a network issue) or something else.

Indeed, you shared a LOT of code in another reply here, including code that reads AND writes files, as well as call cfc's, and much more. As bkbk said, you may be able to put code in place around potentially slow code, but where to start?

Instead, since you are using the PMT already, you should just take advantage of that. And I see that you have the profile feature enabled. Look at the tag/functions tab on that request, which will tell you where the most time was spent. See also the other tabs, in case that may tell you/us more.

That said, trying to observe, interpret, and make recommendations here based on that can be challenging. Sometimes one answer will lead to a new question. Again, I can help far more effectively in a session together. Or maybe Adobe will step to offer that for free.

Otherwise, if the profile info or the results of bkbk's code doesn't help you readily solve things in your own, you can share the info here and we can all engage in back and forth to try to get to the bottom of your issue. There will be an explanation. 

[BKBK]

My bet is that the code needs to be fine-tuned. I conclude that from the information you have provided: relatively low data transfer, in bytes, and difference in behaviour between different ColdFusion 2023 servers.

Can you share the code? Before you do, remove or replace any sensitive or private information.

[BKBK]

Here is a suggestion and a trouble-shooting tip you could use in the meantime.:

Suggestion: Rather than write lumps of data separately to file, gather the data lumps into a variable and write the variable's content to file in one go.  

Trouble-shooting tip: Identify any blocks of code that you suspect may be taking too much time. Surround each such block with a timer, as follows:

<cfset block1_startTime=getTickCount()>

<!--- Block 1 code is here --->

<cfset block1_executionTime = (getTickCount()-block1_startTime)/1000>
<p> Block 1 execution time = <cfoutput>#block1_executionTime#</cfoutput> seconds </p>
...
...
...
<cfset block2_startTime=getTickCount()>

<!--- Block 2 code is here --->

<cfset block2_executionTime = (getTickCount()-block2_startTime)/1000>
<p> Block 2 execution time = <cfoutput>#block2_executionTime#</cfoutput> seconds </p>
...
...
...
etc

You can then proceed to fine-tune the blocks that are taking the most time. In fact, if you share the code with the forum, you will likely get useful suggestions on fine-tuning.

[Madhabdhk]

<cfparam name="datefrom" type="string" default="">
<cfparam name="dateto" type="string" default="">
<cfparam name="source" type="numeric" default="3">

<cfsetting requestTimeOut = "600">

<cftry>
	<cfinvoke component="cfc.iap" method="exportAllActiveOffences" pDateFrom="#datefrom#" pDateTo="#dateto#" pSource="#source#" returnvariable="qryAllActiveOffences"/>

	<cfset tempdir = GetTempDirectory() />
	<cfset filename = GetTempFile(tempdir, "AllActiveOffences_Export_#DateFormat(Now(),'yyyymmdd')#_#DateTimeFormat(Now(),'HHnnss')#.csv")>

	<!--- Header--->
	<cfset headerText = ArrayToList(qryAllActiveOffences.getColumnNames())>

	<cffile action="WRITE" file="#filename#" output="#headerText#" addnewline="yes">

	<!--- loop through each row --->
	<cfloop query="qryAllActiveOffences">
		<cfset data = ''>

		<!--- loop through each column in the row --->
		<cfloop list="#headerText#" index="col">

			<!--- wrap double quotes around text fields in case they contain , in the text --->
			<cfif #col# EQ 'ENABLINGOFFENCE' OR #col# EQ 'UPGRADEOFFENCE' OR #col# EQ 'OFFENDERNAME' OR
				  #col# EQ 'COURTOUTCOME' OR #col# EQ 'COURTOUTCOMEDETAIL' OR #col# EQ 'COURTOUTCOMECOMMENT'>
				<cfset data = data & "#Chr(34)##qryAllActiveOffences[col][currentRow]##Chr(34)#" & ",">
			<cfelse>
				<cfset data = data & "#qryAllActiveOffences[col][currentRow]#" & ",">
			</cfif>
		</cfloop>

		<!--- add the row to the csv file --->
		<cffile action="APPEND" file="#filename#" output="#data#" addnewline="yes">
	</cfloop>

	<cfheader name="Content-disposition" value="attachment;filename=AllActiveOffences_Export_#DateFormat(Now(),'yyyymmdd')#_#DateTimeFormat(Now(),'HHnnss')#.csv"/>
	<cfcontent file="#filename#" type="text/csv" deletefile="true">

	<!--- ERROR Handler  --->
	<cfcatch TYPE="Any">
		<!--- Include the Error Handler template --->
		<cfinclude template = "/includes/ErrorHandler.cfm" >
	</cfcatch>
</cftry>

[BKBK]

I can see one possible way to fine-tune the code. I actually described it as "Suggestion" in my previous post:

1.  Add the following line at the top

<!--- Variable to lump export data together --->
<cfparam name="exportData" type="string" default="">

2.  Replace the line

<cffile action="WRITE" file="#filename#" output="#headerText#" addnewline="yes">

with

<!--- Add header to export-data, ending with a return to next line --->
<cfset exportData = exportData & headerText & chr(10) & chr(13)>

3.  Replace

<cffile action="APPEND" file="#filename#" output="#data#" addnewline="yes">

with

<!--- Add row-data to export-data, ending with a return to next line --->
<cfset exportData = exportData & data & chr(10) & chr(13)>

4.  Immediately after the last </cfloop>, add the line

<cffile action="write" file="#filename#" output="#exportData#" charset="utf-8">

Explanation:
The negotiation between ColdFusion and the Operating System, to connect, gain access to a file and write to the file is expensive in CPU terms. In the code-suggestion here, ColdFusion has to negotiate only once with the Operating System. Whereas, when every row is appended, ColdFusion has to do that negotiation lots of times. 

[BKBK]

Hi @Madhabdhk , have you tried the suggestion? It should reduce the export time by a significant percentage.