Report with subreport is slow to save in the CR Designer within VS 2010

[Former Member]

I have a Visual Studio 2010 VB.NET application that includes embedded .rpt files.  One of the rpt files includes several subreports.  Saving the main report in Visual Studio takes about 100 seconds.  After several hours of analysis and trying the suggestions found at this forum (make sure the Verify on Every Print is unchecked, Page Setup No Printer is checked, update to 13.0.5, etc.) I've not been able to resolve the problem.

The delay seems to be related to a large number of parameters in the main report's stored procedure AND the main report includes at least one subreport.  I've re-created the above with a simplified report and stored procedure that includes a single subreport.  The main report displays the current date, page number and the embedded subreport.  The subreport is blank and does not accept parameters.  The main stored procedure includes ~135 parameters (none of which are actually used in the sp).  With this scenario, save operations of the main report in visual studio take 100 seconds.  If I reduce the number of parameters to ~105, save operations take about 50 seconds.  If  I reduce the number of parameters to ~75, save operations take about 20 seconds.  If I reduce the number of parameters to ~45, save operations take about 7 seconds.

If I remove the subreport from the main report, the main report saves in less than 2 seconds (stored procedure has ~135 parameters).

If I reduce the number of parameters (~15) in the stored procedure and include the subreport, the main reports saves in ~2 seconds.

At runtime the report encounters a similar delay.  In the debugger the entire delay occurs on the setting of the first parameter value (ReportClass.SetParameterValue).  The first parameter is an integer.  There is no delay when setting the other 134 parameters (integers, varchars and datetimes).

Any suggestions?

Regards

Kevin

[former_member11696 · Accepted solution]

Hi Kevin,

Turn on the DB Logging, may show you what is happening.

Try loading a dummy report first, it takes time for your app to load the CR runtime. Common work around and known.

What happens if you load the report again, same delay?

Don

[Former Member · Accepted solution]

Don,

Thank you for the response.

I enabled Database logging.  Attached is the log file that recorded the save (Visual Studio save) of the main report.

The save operation had two errors

2013-5-8-6-59-23 5800 ..\cserrinf.cpp 523 Error 31809 (..\..\src\data\queryenghelp.cpp, 554): Unknown Database Connector Error:  1

2013-5-8-6-59-23 5800 ..\cserrinf.cpp 523 Error 31853 (..\..\src\data\datainterface.cpp, 7785): Invalid Argument provided.:  1

The first error appears once in the log.  The seconds appears 276 times (136 stored procedure parameters x 2 = 276).

At runtime, the report prints slow every time.  I implemented a thread to load the CR runtime at program startup a few years back. 

If I remove the subreport from the main report, the log file shows three errors (one 32809 and two 31853) and saves in ~2 seconds. 

Regards

Kevin

devenv_5800_2013-5-8-6-58-43_runtime.txt.zip

[former_member11696 · Accepted solution]

Hi Kevin,

Can you attach the part of the log leading up to the errors? That's where all the good info is, those error numbers are being returned by Oracle. Bu tI can't find any info on them....

Enable Oracle logging also to see what it is reporting.

If you can attach the report that may help also.

Don

[Former Member · Accepted solution]

Don,

That was the complete log file.  My database server is SQL Server 2008 R2.  I'm working on the server side logging now...

For the moment here are the reports and stored procedure (two posts as I can only attach three files).  Remove the .txt on each file name.

Regards

Kevin

DummyMain.rpt.txt.zip

DummyMain.vb.txt.zip

osp_proj_search_params2.sql.txt.zip

[Former Member · Accepted solution]

The remaining files...

DummySubreport.rpt.txt.zip

DummySubreport.vb.txt.zip

[former_member11696 · Accepted solution]

Hi Kevin,

I had a look at the SP, CR expects the data to be in the last SELECT Statement, from the looks of it this may be part of the issue/cause.

Also, you have the subreport in the detail section so this is how it works, for each record returned in the main report the subreport runs. 10000 rows in the main report = 10000 runs of the subreport.

Do you need to have a subreport using a SP? Why not get all of the data in the main report and use Grouping to sort/filter/display the data... And Drill down to make it even quicker...

Don

[Former Member · Accepted solution]

Don,

>I had a look at the SP, CR expects the data to be in the last SELECT Statement, from the looks of it this may be part of the issue/cause.

There is only one SELECT statement in the stored procedure.  Perhaps you saw the commented-out code as a second SELECT?  NOTE: the files I attached are not the real report. Rather, I created a simple example that exhibits the slow save problem which I believe is related to number of parameters in the main report stored procedure AND that report includes one or more subreports.  Thanks to your assistance with the CR logging suggestion, I now see there are "hidden" errors that the normal designer UI does not expose to the developer (me).  If I can determine the cause of the errors then I hope the problem can be solved. 

Additional information:  This application was originally developed under VS 2005 and SQL Server 2005.  That version of the application does not exhibit the "slow save" problem.  I am in the process of porting the application to VS 2010 and SQL Server 2008 R2.  I am now (see the "hidden" errors above)suspicious of the new Active Directory environment.  Perhaps there is some sort of authentication problem.  Finding the meaning of the error codes may be helpful here...

>Also, you have the subreport in the detail section so this is how it works, for each record returned in the main report the subreport runs. 10000 rows in the main report = 10000 runs of the subreport.

>Do you need to have a subreport using a SP? Why not get all of the data in the main report and use Grouping to sort/filter/display the data... And Drill down to make it even quicker...

This is the intended design (user driven).  The production reports may have dozens of records not thousands.  The subreports are associated with each row in the main report and they are not intended to be interactive (output is a PDF).  As a test I moved the subreport in my example to the report footer and observed no change in the slow save problem.

Regards

Kevin

[Former Member · Accepted solution]

Don,

Using the SQL Profiler, I monitored the database activity during the saving of the report.  The Profiler reported no activity.  If I perform a Verify Database operation, the Profiler shows 5 statements executed.  Should a report save operation access the database?  I don't know why it should.  However the first error message in the CR log is labelled "Unknown Database Connector Error."  So perhaps the save operation is attempting to access the database and the error processing is causing the delayed operation completion.  If this is correct, what credentials is save using?  Verify Database works Save doesn't (shouldn't they both use the same credentials?).  If Verify Database and Save are using the same credentials, what is Save doing differently that would cause the "Unknown Database Connector Error." ?

The funny thing about this problem is that after the long delay, the report works.  This it true for Save operations and the report generation at runtime.  However, the delay will be unacceptable to the end user.  It is driving the developer crazy as well... 

I'm chasing down the CR log error codes but so far no luck.

Regards

Kevin

[former_member11696 · Accepted solution]

Hi Kevin,

These are very strange results.... We are missing info here and I think I see now what is happening.

To explain first though, Crystal will NEVER save the password in the RPT file so to verify the DB info is correct CR must log in to the server and because you are using a Trusted Connection it has to do this to confirm that user does have access.

The reason you are seeing 5 select statements is 1 for the main report and one for each record returned which is passed to the subreport, even though there is no DB connection in the subreport CR will still generate the SQL and query for each value returned. But this is odd because there is no subreport data connection...

In your main report you are using Trusted Authentication. With MS SQL 2008 Microsoft changed security rules big time. So what it is doing is validating the users against all of the table referenced in the SP.

Your DBA needs to verify that use does have access to the DB and SP and all tables referenced in ths SP.

Also, when you have a report with no fields in it CR will pass nulls to the SP. Try adding one field, you'll see that we prompt for all parameters. And in the record Seelction formula filter it so it returns at lease one row.

So if you don't need those PM's why are they in there?

I beleive this has to do with the Trust Auth, what happens if you set the report to use a user name and password?

Don

[Former Member · Accepted solution]

Don,

I'm sorry for the delay with a response.  This issue was pushed to the back burner while I had some software go into test. 

Ok...

I've created a stand alone Visual Studio 2010 project that you should be able to build that demonstrates the slow printing problem.  This application has two reports that access the same stored procedure. One of the reports includes a subreport, the other does not.  The application can display the reports in the CrystalReportsViewer control or with Adobe Reader (assuming you have that installed).  The application can also connect to the database using Windows or SQL Server authentication provided you have the accounts configured appropriately.  The Visual Studio solution is attached as well as the stored procedure and an installation/configuration document.

I have deployed this application here at work and also on my home development machine.  The office environment is on a Active Directory network with a SQL Server 2008 R2 machine and a separate Windows 7 workstation that runs the application.  I added SQL Server authentication to the database server and have tested connecting to the server with Windows and SQL Server authentication (via different user accounts - one authenticated with AD, the other SQL).

To rule out issues related to the network enviroment, I tested this application at home.  There I have a SQL Server 2008 Express database server running on Windows 7.  In this environment, the application and database are run on the same machine.  I enabled SQL Server and Windows authentication on the server and configured two accounts (one for each authentication method).  There is no Active Directory in this environment. 

The slow printing problem exists in both environments.  A report (sp has ~75 parameters) that includes a subreport runs approximately 10 times slower than a report based on the same stored procedure that does not have a subreport. If I increase the number of parameters, the time difference widens (significantly).

To answer your questions...

1) all users in both of my test environments have access to the database and execute permission to the stored procedure.  The stored procedure in the test scenario does not access tables.

2) the reports now include 3 fields in the test application. (the stored procedure for this test returns a single row).

3) While the unused parameters in the test application are not technically needed, they are used in my real application.  I have reduced the variables in the test application to try a rule out things that might require processing (and expend time).  That is why the test stored procedure does virtually nothing.  I wanted to make sure the slow printing was not induced by a complicated stored procedure.  In the real application I have ~135 parameters and the delay is much worse.  I shorten the number of parameters in the test stored procedure so that I would only have to wait 10-20 seconds for the slow report to generate rather than 2-3 minutes.

4) My test program shows no difference between Trusted Authentication and SQL Authentication (where I supply the user name and password).

One final note. The real application was ported to Visual Studio 2010 and SQL Server 2008 R2 (with some slight modification not related to printing).  The application was originally development under  Visual Studio 2005 and SQL Server 2005 (using SQL Server Authentication).   The 2005 version of the application does not have this slow printing problem.

Let me know if there is any additional information I can provide.

Regards,

Kevin

CR_test.zip.txt.zip

CR_Parameters_Problem.docx.txt.zip

osp_proj_search_params2.sql.txt.zip

[former_member11696 · Accepted solution]

Hi Kevin,

Thanks for doing all of that debugging. It does appear to be a Cr issue. Unfortunately I don't have time right now to test this.

If you want to purchase a single case a rep can duplicate it and I'll likely end up getting it anyways.

Wait a bit, SP 6 is coming out soon and see if that resolves the issue.

I'll see if Ludek has time to test this...

Thanks

Don