Application runs slow when DB logging info is applied to each Crystal Reports Sections

Question

Currently I use the following method to assign connection info to all the report sections. But as I have many sections in the report, the report is displayed after almost 10 seconds. Which looks really slow. Is there some other method by which we can set logon information to each CR once and for all when it is installed at client side.

JFYI: All the CRs connect to same DB, with same login credentials. Thank you in advance.

   readDiamondBillReport = new RealDiamondBill();
                        crConnectionInfo.ServerName = db.Connection.DataSource;
                        crConnectionInfo.DatabaseName = db.Connection.Database;
                        crConnectionInfo.UserID = "client";
                        crConnectionInfo.Password = "client";
                        crConnectionInfo.IntegratedSecurity = false;

                        CrTables = readDiamondBillReport.Database.Tables;
                        foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)
                        {
                            crtableLogoninfo = CrTable.LogOnInfo;
                            crtableLogoninfo.ConnectionInfo = crConnectionInfo;
                            CrTable.ApplyLogOnInfo(crtableLogoninfo);
                        }

                        Sections crSections2 = readDiamondBillReport.ReportDefinition.Sections;
                        // loop through all the sections to find all the report objects 
                        foreach (Section crSection in crSections2)
                        {
                            ReportObjects crReportObjects = crSection.ReportObjects;
                            //loop through all the report objects in there to find all subreports 
                            foreach (ReportObject crReportObject in crReportObjects)
                            {
                                if (crReportObject.Kind == ReportObjectKind.SubreportObject)
                                {
                                    SubreportObject crSubreportObject = (SubreportObject)crReportObject;
                                    //open the subreport object and logon as for the general report 
                                    ReportDocument crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName);

                                    Tables SubCrTables = crSubreportDocument.Database.Tables;
                                    foreach (CrystalDecisions.CrystalReports.Engine.Table SubCrTable in SubCrTables)
                                    {
                                        crtableLogoninfo = SubCrTable.LogOnInfo;
                                        crtableLogoninfo.ConnectionInfo = crConnectionInfo;
                                        SubCrTable.ApplyLogOnInfo(crtableLogoninfo);

                                    }
                                }
                            }
                        }

                        readDiamondBillReport.Refresh();

Out of curiosity, have you tried timing the report's DB update as compared to its refresh? — Ryan, Apr 13 '12 at 16:10
I mean time how long it takes for your code to update the reports' data sources versus refreshing the report via `readDiamondBillReport.Refresh()`. My guess is that a large majority of the 10 seconds is going to the refresh and not the update. — Ryan, Apr 14 '12 at 19:18
Yes, Refresh takes a long while, but without refreshing, new data is not reflected in the report. — Marshal, Apr 15 '12 at 05:12
Yes, I know that. Your question is suggesting that the process of updating the data source is what is taking so long, but what I am saying is that that 10 seconds may be unavoidable due to the refresh. — Ryan, Apr 15 '12 at 13:59

score 3 · Accepted Answer · edited May 23 '17 at 12:27

I finally found that, neither applying logon info was the issue nor refreshing the report was. But it was my large picture object which I used for setting a watermark in crystal reports.

I had 10 reports which used this Image as watermark. I removed the watermarked image and now following problems are solved:

Project builds very very fast. Previously it took around 1 min to build, which has now reduced drastically to 8-10 secs.
Any changes to the project, especially to reports gets saved much faster.
I used to get "Not enough storage is available to complete this operation" after one or two builds. I had to restart VS and cross my fingers for each of the build.
Crystal Reports are displayed faster on CrystalReportViewer and also objrpt.PrintToPrinter works 500 times faster.

I hope these points will help fellow programmers.

score 3 · Answer 2 · answered Apr 20 '12 at 00:59

Although you have answered your own question I will point out an alternative approach that can be used with Crystal Reports. Typically people use the "pull" approach in which the connection is set on the Crystal Report and the data set is "pulled" based upon queries embedded within the report.

However, it is possible to use a "push" approach as well. In this scenario you simply bind your Crystal Report data source to an XSD schema and set the data set on the Crystal Report. In .NET you can easily generate the XSD from your data set so this approach is straight forward. You can therefore bind any sub report to the specific table you desire from the passed data set.

The advantage here is that the data can come from any DBMS (database agnostic) and can be manipulated as necessary before being passed to the report (implement custom security, joins, etc).

The caveat being that you would not implement this approach for reports that have larges amounts of data as .NET data sets can be memory intensive.

However, since your performance issue was related to an image this approach would not have helped.

score 2 · Answer 3 · answered Apr 14 '12 at 06:06

Every report has a subreports collection.

You can apply login info to the tables of each subreport instead of searching for the subreports in each section.

Here is some code

private void showrep(string repName)
        {
            rd = new ReportDocument();
            rd.Load(pth+"\\"+repName);
            LogInInfo();

            crv.ReportSource = rd;  // crv is the reportviewer
            crv.Show();
        }

        private void LogInInfo()
        {
            MyApp.Properties.Settings s = new MyApp.Properties.Settings();
            TableLogOnInfo linfo = new TableLogOnInfo();
            linfo.ConnectionInfo.DatabaseName = s.dbname;
            linfo.ConnectionInfo.UserID = s.usr;
            linfo.ConnectionInfo.Password = s.pw;
            linfo.ConnectionInfo.ServerName = s.svr;

            foreach (Table t in rd.Database.Tables)
            {
                t.ApplyLogOnInfo(linfo);
            }
            foreach (ReportDocument sr in rd.Subreports)
            {
                foreach (Table t in sr.Database.Tables )
                {
                    t.ApplyLogOnInfo(linfo);
                }
            }
        }

Hope it helps.

Here only tables are applied with logon info. What if we have RecordSeletionFormula and GroupSelectionFormula in sections. Or Suppress or other formulas. They also very much depend on logon infos. And hence all the elements must be applied with logon information. — Marshal, Apr 14 '12 at 06:34
your question did not explain that and neither were you doing the same in your previous code. Because every report / subreport has been applied login info by default those info are available to all sections. Recordselection formulas and suppress formulas hardly need logininfo !! In push model binding you can even render the report with out any login info at all. And Reports created using XML schema dont have any database login info. We just pass a dataset to them. — Deb, Apr 14 '12 at 07:00

Application runs slow when DB logging info is applied to each Crystal Reports Sections

3 Answers3

Linked