Delay in Excel opening when Exporting reports using ExportToDisk()

[jennifer_nalley]

We have a Performance issue exporting reports to Excel using ExportToDisk() in a RemoteApp environment.

When the dataset is fairly large, there is a long delay between the report being loaded and to Excel opening. When using the same database on a local PC, there is no delay of the opening of Excel at the end of the report load. It takes approximately 3 times as long in the RemoteApp environment.

The logs seem to show that Creating the xlsx is done 4 times in the RemoteApp environment and only once on a local PC. It looks like its looping thru this process a total of 4 times, hence the 3 times as long.

Environment: RemoteApp setup consisting of 2 Connection brokers, 2 gateway servers, 1 database server, 1 domain controller, and 2 Session host servers. All Windows Sever 2019 VMs. All in the same domain. Database is SQL Server 2019 instance. 32-bit C++ application using Crystal Reports Runtime Engine for .NET Framework (32 bit) version 13.0.31.4010, compiled in Visual Studio 2019. Excel 2016 installed on Session Host servers.

We used to have a similar setup on Windows 2008 R2 VMs (sans the Connection Broker and Gateway Server VMs required for Win Server 2019 RemoteApp setup) and did not experience this same delay.

The code to Export is being done in a thread and a Progress window is viewable for the Report Load.

mytype = ExportFormatType.Excel;

private void excel_todisk()

{

cryRptArry[selectedreportnum].ExportToDisk(mytypetr, myFilename);

}

private void excel_outandopen(ExportFormatType mytype)

{

. . .

Thread t = new Thread(new ThreadStart(excel_todisk));

t.Start();

while ((t.IsAlive) && (!reportCanceled))

{ //kill time showing progress bar till report finishes loading

System.Windows.Forms.Application.DoEvents();

} //while t is alive and not cancelled

Process.Start(myFilename);

if (myFormProgress != null)

{

myFormProgress.Visible = false;

myFormProgress.Dispose();

}

. . .

}

We have tried disabling the terminal server EasyPrint and installed the print driver from the user’s PC accessing the RemoteApp. That did not speed up anything.

We have tried 32-bit and 64-bit versions of Excel, but that did not have any affect either.

There are no sub reports in the report.

During the export two Crystal temp files get created (.ctm files).

On the local PC, the first file does some big sized Writes then does small size Reads then closes the file. The second file does lots of small Writes then a bunch of small Reads then closes the file.

On the RemoteApp environment, the first file does big Writes and only two Big sized Reads. The Reads end with an Offset of 262,144 then again at 524,288, kind of like it’s doing the whole process twice. The second file does lots of small Writes, no Reads, then closes the file.

I think it may be trying to open the file before it is fully created? or a Windows back ground process is not releasing the reports?

Does anyone have any recommendations on what could be causing the extended delay of Excel opening post Report Load?

[DellSC]

Hi Jennifer,

I removed the SAP Crystal Reports and SAP Crystal Reports Viewer tags from your question because they were unnecessary. This left the SAP Crystal Reports, version for Visual Studio tag.

For more information about what the Crystal tags are used for, see Using SAP Crystal Reports Tags | SAP Blogs.

-Dell

[jennifer_nalley · Accepted solution]

We stumbled upon multiple people disabling 'TSFairshare'. : https://learn.microsoft.com/en-us/windows-server/remote/remote-desktop-services/fair-share-enabled-b...

It made a significant performance increase. Report went from approx. 3 hr 45 min to 1 hour.

We are currently testing to see if there is any downside to disabling it in our multi user configuration. I will update again once our testing is complete.

[DonWilliams]

HI Jennifer,

Try adding the exact key I posted.

It also requires a restart of the app, CR will only load the over ride keys on first load.

Don

[jennifer_nalley]

Don,

The Registry location for InProcServer on our Win 2019 server was slightly different. It was at Computer\HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Crystal Reports for .NET Framework 4.0\Report Application Server\InprocServer

The TempDir key was not there so I created it and add a path to the C drive on the app server. I re-ran the export report and the .ctm files were still created on the other server so no change in processing time was observed. The GPO being applied has folder redirection still in place so maybe that is overriding the TempDir value, I don't know.

I guess I will see if we can remove that GPO setting and try it again unless you have another suggestion.

Thanks again for all the help.

[DonWilliams]

Hi Jennifer,

Here's some more info and reply from DEV:

For CR .Net SDK & Inproc RAS.Net SDK application, by using below registry key value could specify the temporary directory for temporary files.

[HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Report Application Server\InProcServer]

"TempDir"="<PathToUse>"

However, the Excel(includes xls, xlsx, page base and data-only) exporting will write extra temp files(.ctm) to the user’s temp foldr: %temp%. This could not be changed.

Also, .Net application itself might also generate the temporary files to user’s temp folder…

Don

[DonWilliams]

Hi Jennifer,

Good sniffing to see what's happening in the backend.

I don't recall if we can set the CR's \temp folder. I do know we do read the system variables as you noted and use it but for TS services I think it has the ability to redirect I think, it's been a while since I've run into this request.

If there is one check the App.config per user, it may be specified in there, but it could be a registry key also.

I'll ping the Developers and see if CR for VS has the ability to specify a unique location.

Check the Logs to see if it does read a reg key for the temp folder.

Don

[jennifer_nalley]

Thanks for the reply Don. I tried compiling and using a V34 Crystal DLL and having the runtime engine V34 on the app server but it did not make a difference in the performance. Our IT states that there is nothing "scanning" files and if it was a permissions issue, it wouldn't work at all, and the firewall is disabled, and that the antivirus in place is NextGen AV Falcon and that it doesn't need exceptions.

PROCMON shows the report processing (Read/Writes) is on the Terminal Services user’s profile, which is redirected to a share on the Connection Broker server in the same domain. Crystal itself is installed on the Application Server. I am thinking if there is a way to keep the processing on the app server it will reduce the I/O. Then when the report is done save it to the user’s home directory. PROCMON shows that on a local install its utilizes FASTIO_READ and FASTIO_WRITES operations, whereas in the RDWeb environment it utilizes ReadFile and WriteFile. So caching seems to be in play.

Is there a way to specify a separate “temp” file just for Crystal to use? A way other than the location specified in the TEMP environmental variable? Thanks.

[DonWilliams]

Hi Jennifer,

That is normal behaviour you are seeing on your local PC.

CR checks permissions, writes a temp file to the c:\windows\temp folder, then deletes it to verify it has permissions, then begins exporting if all goes well.

The temp files are normal, CR creates them to hold data while processing the report, based on available System resources.

I suspect your Server system AV software is either scanning the temp files or permissions are limited.

Be sure to give your app.exe full read/write permissions and Trusted in both AV and Firewall settings. As well the the System\Temp folder.

Also I suggest upgrading to SP 34, latest release, SP 31 had issues:

https://wiki.scn.sap.com/wiki/display/BOBJ/Crystal+Reports%2C+Developer+for+Visual+Studio+Downloads

You could use Process Monitor to see/compare dll access and temp folder read/writes, it may show where the delay is exactly.

Don