CRAXDRT.Application.OpenReport - VERY slow

[nancy_budd]

One of our customers is experiencing this same issue as mentioned in the link below:

[CRAXDRT.Application.OpenReport - VERY slow|http://www.codeguru.com/forum/archive/index.php/t-338155.html]

We are using the Crystal XI RDC components to execute the report, so I would assume that we would have the Crystal 9 hotfix for this. However, the problem still exists. And, this is not happening to all of the reports, just some.

An example. I compared two of our simplest reports. Each executes one SQL stored procedure. The stored procedure just selects data from one table, it does nothing more. However, the problem isn't with SQL. The 'slowness' occurs before the RDC even accesses the database. The problem definitely occurs on the .OpenReport() method.

These two reports each have two subreports. Neither subreport accesses a database. However, both reports' link to the subreports from the main report. Result: one report takes about 40 seconds to run, the other takes 10 seconds.

I tried a few things with the 'slow' report:

1. I verified database - same issue

2. I removed the subreport links, saved the report, then added the subreport links back in, saved the report. Same issue.

3. I re-created the report from a template we use for our reports that has the two subreports in it.

Ran the report - now it is fast.

Why? I need to know why this fixes the problem. And, what is a faster way to fix this. We have 527 reports!

[Former Member · Accepted solution]

Check to see if you have the option "Re-import subreport when opening" check on.

right click on the subreport select format subreport then go to the Subreport tab..

If it is checked on uncheck the option.

[nancy_budd · Accepted solution]

I checked both reports. The Re-import When Opening is NOT checked for either sub-report.

What is interesting is 'Re-import When Opening' checkbox (although unchecked) is disabled on the 'slow' (original) report. I can't even get to it to check it. Why is this?

I ran ProcessMonitor for both reports, but I am having trouble 'translating' what is happening in these logs.

[nancy_budd · Accepted solution]

Through Process Monitor, I've found that the Crystal function WriteFile looks like it is taking 18 seconds for this report. The WriteFile operation is trying to write to a TMP file, and it happens on the last instance of WriteFile for the .TMP file.

The ProcessMonitor detail for this WriteFile operation shows;

Offset: 4,096, Length: 8,192, I/O Flags: Non-cached, Paging I/O, Synchronous Paging I/O

Then ProcessMonitor shows 18 occurrences of Process Profiling operation (at 1 second each) before the next Operation CreateFile of C:\Program Files\Common Files\Business Objects\3.0\bin

with the ProcessMonitor detail of this CreateFile showing as:

Desired Access: Read Data/List Directory, Synchronize, Disposition: Open, Options: Directory, Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult: Opened

This is happening at our customer's site, and we have no explanation for them. We really need SAP/BO to help us out here, as this is code that is produced by SAP/BO from the OpenReport method.

Thanks.

[nancy_budd · Accepted solution]

Can anyone help on this issue?

[Former Member · Accepted solution]

Hello Nancy,

These forums are for community assistance with general questions and not a technical support venue.

People will try and help with technical support issues, but there is no guarantee that you will be able to get a resolution for technical issues. There is also no way to control how fast or slow responses from users come or how much/little help they will be.

If you have technical support questions that can not wait for assistance on the forums or are not being answered to your liking, you have the option to purchase support.

You can call 1-800-877-2340 Option 4 then Option 1 to purchase single case incidents.

Each question would need a seperate case.

You will need to be using Crystal Reports XI Release 2, Crystal Reports 2008, or one of the bundled versions that come with Visual Studio as those are the only versions of the product that are still supported.

Sincerely,

Dan Kelleher

[Former Member · Accepted solution]

Moving thread to the "Legacy Application Development SDKs" forum for this RDC issue.

[nancy_budd · Accepted solution]

Our reports have been upgraded to Crystal 2008 (standalone). We are using the RDC to execute our reports until we resolve issues we are having with the Crystal for .NET methods (CR2008 SP1).

I just don't know what Crystal is trying to do with the OpenReport() method of the CRAXDRT.Application. Why is it slow at our customer's site, but the same report, executed on my machine is very fast.

As mentioned, it is not re-importing the subreport. Other ideas are welcome.

Thank you.

[ido_millet · Accepted solution]

Hi Nancy,

Problem occurs even when opening the rpt file with saved data?

If so, send me the rpt with saved data. I may be able to identify the issue.

Cheers,

ido

[nancy_budd · Accepted solution]

We never save data with our reports.

[former_member11696 · Accepted solution]

Hi Nancy,

Check the reports and our registry keys, not sure what version of the RDC you are using since CR 2008 does not ship with it. In the Reports uncheck all options to Verify Database options and also in the registry. Your reports are likely trying to connect to the original DB they were designed against and which typically there is a 1 minute timeout while the DB client trys to connect.

RDC does not know how to handle CR 2008 reports, updated format and it is not aware of Cascading parameters. The RDC has not been updated since version 9 which did not have Cascading Parameters.

What is the issue with .NET you are having? HAve you posted your .NET question in the .NET forum?

Thank you

Don

[nancy_budd · Accepted solution]

Thanks for your reply.

All reports we create through Crystal, as our standard, do not have the Verify on First Refresh

or Verify Stored Procedure on First Refresh checked. So, these reports do not have this checked.

Can you tell me what registry entries, on the client's machine having this problem, to check?

I have submitted our .NET issue, and have even purchased an incident. So, that is a work in process.

Thank you.

[former_member11696 · Accepted solution]

Hi Nancy,

Are all reports having this problem or just these 2, one now since you re-built one of the reports?

Is this a windows or WEB app and what DEV language are you using?

When RDC opens a report in code you should be using the Open As Temp Copy:

Set crReport = crApplication.OpenReport(App.Path & "\Report1.rpt", 1)

What this does is copy the RPT file into the TEMP or TMP folder so the original report can be used by someone else.

Also, you said you updated all your report? You updated them to CR 2008 format or XI R2 format?

Have you applied the latest CR XI R2 patches? There were issues with Oracle Stored Procedures which the patches should resolve. If not what DB are you connecting to?

Thank you

Don

[nancy_budd · Accepted solution]

Not all reports are having this issue. There may be more than these 2 reports, however, we are focusing on them as a starting point.

This is a Windows application. We use an MSSQL database. Our main application (written in eDeveloper/Magic) executes our VB6 .exe (no forms involved...just running the executable which will use the Crystal RDC to run the report).

We were opening the report using the following code:

Set crReport = crApplication.OpenReport(App.Path & "\Report1.rpt")

I changed it to Set crReport = crApplication.OpenReport(App.Path & "\Report1.rpt", 1) assuming here that the ",1" designates it as a TEMP copy. Compiled the vb6 .exe...same results.

Yes, we opened each of our reports and saved them under Crystal 2008.

Have you applied the latest CR XI R2 patches? Is this to update references in the vb6 app? If so, I don't believe so...how can I check?

There were issues with Oracle Stored Procedures which the patches should resolve. If not what DB are you connecting to? We use MSSQL Server, not Oracle to connect to the database, but where the vb6 program is hanging (at OpenReport) it hasn't even gotten around to accessing the database, so I don't believe that is the issue.

[Former Member]

Hello, Nancy;

As mentioned, Cyrstal Reports 2008 no longer ships with RDC (CRAXDRT.DLL). You have now saved these reports to Crystal Reports 2008 (12.0) but what references are in your Visual Studios application to actually run them?

One of the things that slows a report when loading is to look for the printer the report was created on when it is running through the application. Try changing the report to "No Printer" if it displays a printer in the designer.

Elaine

[nancy_budd]

We are aware that the RDC is no longer shipped w/newer versions of Crystal Reports.

We are in the process of re-writing our vb6 RDC program in VB.NET 2005 using the Crystal for .NET

methods shipped with Crystal 2008 (SP1). We create/maintain our reports in the Crystal standalone edition (we do not use the Crystal reports embedded in VB.NET).

We had tested running Crystal 2008 reports with our VB6 program here before shipping to customers. And the reports ran fine, and are running fine for most of our 250+ customers. This one particular customer is having an issue, and not with all reports.

I've tried the No Printer option, and that does not seem to work. It is bizarre...I don't understand it.

I don't know if there is something on their machines that would conflict with this OpenReport. But, if there were, then why doesn't it happen to all reports?

[former_member11696]

Hi Nancy,

This is bizaar... How many reports don't work? You said when you recreated the reports they started working. It may be easier to simply re-create those few reports:

I tried a few things with the 'slow' report:

3. I re-created the report from a template we use for our reports that has the two subreports in it.

Ran the report - now it is fast.

If not how are you connecting - ODBC or OLE DB? Typically if using ODBC if the DB source doesn't exist then ODBC has a 1 minute timeout which appears to be a delay opening the report. 30 second difference doesn't appear to be this issue though.

On the customers PC check these registry key:

HKEY_CURRENT_USER\Software\Business Objects\Suite 11.5\Crystal Reports\DatabaseOptions

and compare them to:

HKEY_CURRENT_USER\Software\Business Objects\Suite 11.5\Crystal Reports Designer Component\DatabaseOptions

If they don't exist export the key's and then change the location and import. And compare them to your PC to see what's missing or different.

You could try using Process Monitor from www.sysinternals.com and step through the opening to see what reg keys are being accessed etc. It may be MS client software needs to be updated also. Compare versions MS runtime also. Not sure what version of MS you are using? If it's MS 2000 then MDAC may need to be updated, if it's MS 2005 then you need to install the Client tools. You can compare your PC and the bad PC to see if the are exactly the same. Try using Modules also, it may show the differences.

Not sure how much time you want to spent on this, it appears you have a work a round by re-importing the subreports using your template report. It may be just easier to do this rather than try to explain why.

Thanks again

Don

[nancy_budd]

I couldn't say how many of our reports are having the issue, but re-writing them really is not a viable option for us. Some of our reports are very complicated and re-writing them could cause issues (with placement of images, etc depending upon the report).

We are using an OLE DB connection to SQL Server.

I will log into our customer's system (may not be today) and check the registry keys you mentioned below.

I have run ProcessMonitor on the machine with the issue, but I am not familiar with 'reading' the log returned from ProcessMonitor.

I will have to get back to you with regard to the version of MS. I'll see what I can gather up and then respond back.

Thanks for your help. We would really like to get down to understanding what the true issue is (rather than re-writing) should any of our other customers run into this issue.

[nancy_budd]

Don,

Okay...dialed into our customer's system this morning and checked their registry (compared it to mine). Now, they do not have Crystal Developer, I do.

Neither machine had the HKEY_CURRENT_USER\Software\Business Objects\Suite 11.5\Crystal Reports\DatabaseOptions

Customer machine does not have the HKEY_CURRENT_USER\Software\Business Objects\Suite 11.5\Crystal Reports Designer Component\DatabaseOptions

But it does have HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports Designer Component\DatabaseOptions

My machine has

HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports Designer Component\DatabaseOptions

HKEY_CURRENT_USER\Software\Business Objects\Suite 11.5\Crystal Reports Designer Component\DatabaseOptions

HKEY_CURRENT_USER\Software\Business Objects\Suite 12.0\Crystal Reports Designer Component\DatabaseOptions

We did not distribute the CRXIR2 runtime components to our customers, we did not upgrade our vb6 program to use the CRXIR2 runtime components. The main reason was that we were only using the CRXIR2 components to write our .NET program (to replace our vb6 program). However, we found the CRXIR2 runtime components took up an inordinate amount of disk space. And, we could not pass that on to our customers. We found that CR2008 runtime components took up less space, so we began writing our .net app with that. As mentioned, in an earlier post, we are having performance issues with our .net app, but I have an open call for that.

In the meantime, we have this one customer site having this issue. I ran ProcessMonitor on the machine, just before the OpenReport() method executes, and then stopped it right after (I have breakpoints in my code). What I discovered was this:

Through Process Monitor, I've found that the Crystal function WriteFile looks like it is taking 18 seconds for this report. The WriteFile operation is trying to write to a TMP file, and it happens on the last instance of WriteFile for the .TMP file.

The ProcessMonitor detail for this WriteFile operation shows;

Offset: 4,096, Length: 8,192, I/O Flags: Non-cached, Paging I/O, Synchronous Paging I/O

Then ProcessMonitor shows 18 occurrences of Process Profiling operation (at 1 second each) before the next Operation CreateFile of C:\Program Files\Common Files\Business Objects\3.0\bin

with the ProcessMonitor detail of this CreateFile showing as:

Desired Access: Read Data/List Directory, Synchronize, Disposition: Open, Options: Directory, Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult: Opened

You mentioned checking the MS client software, comparing versions of MS rutnime, what version of MS are we using? Could you please clarify what exactly I need to look for? And, what is Modules?

We really want to get to the crux of this problem. I appreciate your helping me.

[former_member11696]

Hi Nancy,

The MS I was referring to is the database access parts. As noted if you are using MS SQL Server 2000 then MDAC is used, if using MS SQL Server 2005 then it has a client install which should be installed on the end users PC.

MDAC is Microsoft's Data Acess Components which they install by default on all PC's, or is usually a requirement for various applications.

I can't say which parts of our registry is required or being accessed. On your PC, and I believe Process Monitor has the ability to log registry entries. Look for the RDC and database keys to see if those keys are being used and compare them to the runtime PC. Then export that whole registry tree, it will be just the CR keys, and import them on the users PC. This will rule out if it is keys that are required and used but don't exist on the end users machine.

The TMP file rights are from the open method. When specifying to open a temp copy we write the RPT to the users TMP folder. We also right database files there for internal links and filters but those files should be released/deleted when the report is closed. It may be we are trying to connect to the database which could be creating those temp files because the registry key that tell RDC not to do so are missing.

Thanks again

Don

[nancy_budd]

From the registry key, I right clicked on the HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0 path on my machine (which runs quickly) and on the customer's machine. Here are the results (when I open the results in WordPad). They look the same to me:

My Machine:

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0]

[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports Designer Component]

[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports Designer Component\DatabaseOptions]

[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports Designer Component\DatabaseServer]

[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports Designer Component\Export]

[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports Designer Component\FetchOptions]

[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports Designer Component\FormatOptions]

[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports Designer Component\NewObject]

[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports Designer Component\QueryEngine]

[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports Designer Component\SavedData]

The customer's machine:

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0]

[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports Designer Component]

[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports Designer Component\DatabaseOptions]

[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports Designer Component\DatabaseServer]

[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports Designer Component\FetchOptions]

[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports Designer Component\FormatOptions]

[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports Designer Component\NewObject]

[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports Designer Component\QueryEngine]

[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports Designer Component\SavedData]

Both are an MS-SQL 2005. However there's is 64bit, ours is 32bit.

Our version of SQL Server:

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)

Standard Edition on Windows NT 5.2(Build 3790: Service Pack 2)

Customer version of SQL Server:

Microsoft SQL Server 2005 - 9.00.3042 (X64)

Standard Edition (64-bit) on Windows NT 6.0(Build 6001: Service Pack 1)

I'll try and look through the Process Monitor logs I ran a couple of weeks ago to see if I see any registry settings/entries.

Thanks

[former_member11696]

Hi Nancy,

That explains it. RDC is 32 only which means all of it's dependencies must also run in 32 mode. What MS is doing is "thunking" their OLE DB API's from 64 to 32 bit mode which takes time.

You should look into if there is a 32 bit cleint install for MS SQL Server. Also apply any patches for SQL, apparently there are quite a few of them

Thanks again

Don

[nancy_budd]

Why, then, does the slowness not happen with all reports...only some. Again,we've only run 4 of our 520+ reports (as examples). We know two are slow, two are not...and the ones that are slow are not necessarily the complex reports.

Thanks

[ido_millet]

Perhaps the slow report use a Crystal function in the record selection formula, causing filtering to occur in the client (instead of in the server). You can investigate that aspect by running the reports in Crystal and then selecting Report, Performance Information...

[nancy_budd]

I will look at this in the next few days to see if that is the case & then get back here.

Thanks very much.

[nancy_budd]

Our customer does not have Crystal Reports installed, therefore, I cannot run the report in Crystal on their machine.

I did run the report in Crystal on my machine (which does not have the slow issue), and I saved the report performance information to a text file. Here it is...is there something specific I should be looking for? Is there any way to get this information from their machine (without Crystal Developer installed)?:

**************************
L_SY_COMPINFO_orig.rpt.rpt
************************** 

Report Definition
-----------------
 Number of Database Fields:   18    
 Number of On-Demand Formulas:   4    
 UFLs in use:   None    
 Number of Subreport Objects:   2    
 Page N of M Used:   No    
 File Format Schema:   12.0.0    

Saved Data
----------
 Number of Data Sources:   1    
 Unicode Saved Data:   Yes    
 Total Number of Records:   6    
 Selected Number of Records:   6    
 Recurring Database Record Length:   1007 bytes   
 Recurring Formula Record Length:   9 bytes   
 Constant Formula Record Length:   43 bytes   
 Size of Saved Records:   5 KB   
 Size of Saved Memo Fields:   0 bytes   
 
Processing
----------
 Grouping Done on Database Server:   No    
 Sorting Done on Database Server:   Yes    
 Record Selection Done on Database Server:   Yes    
 Require Two Passes:   No    
 Require the Total Page Count:   No    
 Number of Nodes in Group Tree:   7    
 Number of Summary Values:   0    
 Size of Saved Group Tree:   310 bytes   
 Built the Group Tree in Progress:   No    
 
Latest Report Changes
---------------------
 Recurring Database Fields:   Changed    
 Report Groups:   Changed    
 Constant Formulas:   Changed    
 Recurring Formulas:   Changed    
 Print Time Formulas:   Changed    
 Database Table Linking:   Changed    
 Record Sort Order:   Changed    
 
Performance Timing
------------------
 Open Document:   0 ms   
 Run the Database Query:   27 ms   
 Read Database Records:   59 ms   
 Format First Page:   224 ms   
 Number of pages formatted:   1    
 Average time to format a page:   224 ms   
 
***************
Sub_PageFtr.rpt
*************** 
Report Definition
-----------------
 UFLs in use:   None    
 Page N of M Used:   No    
 File Format Schema:   12.0.0    
 
Saved Data
----------
 Number of Data Sources:   1    
 Unicode Saved Data:   Yes    
 Total Number of Records:   0    
 Selected Number of Records:   0    
 Recurring Database Record Length:   4 bytes   
 Recurring Formula Record Length:   0 bytes   
 Constant Formula Record Length:   0 bytes   
 Size of Saved Records:   0 bytes   
 Size of Saved Memo Fields:   0 bytes   
 
Processing
----------
 Grouping Done on Database Server:   No    
 Sorting Done on Database Server:   No    
 Record Selection Done on Database Server:   Yes    
 Require Two Passes:   No    
 Require the Total Page Count:   No    
 Number of Nodes in Group Tree:   0    For all instances
 Number of Summary Values:   0    For all instances
 Size of Saved Group Tree:   82 bytes   
 Built the Group Tree in Progress:   No    

Latest Report Changes
---------------------
 Print Time Formulas:   Changed    

Performance Timing
------------------
 Run the Database Query:   2 ms   For all instances
 Read Database Records:   0 ms   For all instances
 Format First Page:   0 ms   For all instances
 Number of pages formatted:   1    For all instances
 Average time to format a page:   0 ms   For all instances
 Number of subreport instances formatted:   1    
 Time to format all subreport instances:   11 ms   
 
*******************
Sub_ListPageHdr.rpt
******************* 
Report Definition
-----------------
 Number of On-Demand Formulas:   1    
 UFLs in use:   None    
 Page N of M Used:   No    
 File Format Schema:   12.0.0    
 
Saved Data
----------
 Number of Data Sources:   1    
 Unicode Saved Data:   Yes    
 Total Number of Records:   0    
 Selected Number of Records:   0    
 Recurring Database Record Length:   4 bytes   
 Recurring Formula Record Length:   0 bytes   
 Constant Formula Record Length:   31 bytes   
 Size of Saved Records:   0 bytes   
 Size of Saved Memo Fields:   0 bytes   
 
Processing
----------
 Grouping Done on Database Server:   No    
 Sorting Done on Database Server:   No    
 Record Selection Done on Database Server:   Yes    
 Require Two Passes:   No    
 Require the Total Page Count:   No    
 Number of Nodes in Group Tree:   0    For all instances
 Number of Summary Values:   0    For all instances
 Size of Saved Group Tree:   82 bytes   
 Built the Group Tree in Progress:   No    
 
Latest Report Changes
---------------------
 Constant Formulas:   Changed    
 Print Time Formulas:   Changed    
 
Performance Timing
------------------
 Run the Database Query:   2 ms   For all instances
 Read Database Records:   0 ms   For all instances
 Format First Page:   0 ms   For all instances
 Number of pages formatted:   1    For all instances
 Average time to format a page:   0 ms   For all instances
 Number of subreport instances formatted:   1    
 Time to format all subreport instances:   41 ms

[former_member11696]

Hi Nancy,

No sorry but that would be a great set of API's but it may be a set built into crw32.exe and therefore no equivolent set of API's. I'll suggest this to the SDK team as an enhancement request.

They could go to our download site and download a trial version. It would require they un-install CR and then re-install your app, just incase the Cr un-install removes updated files.

You may also want to upgrade to CR XI R2, it's free and supported platform and your XI keycode will work with it.

Thanks again

Don