Application runs slow when DB logging info is appl

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();

Answer 1

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.

Answer 2

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.

Answer 3

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:

1. Project builds very very fast. Previously it took around 1 min to build, which has now reduced drastically to 8-10 secs.

2. Any changes to the project, especially to reports gets saved much faster.

3. 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.

4. Crystal Reports are displayed faster on CrystalReportViewer and also objrpt.PrintToPrinter works 500 times faster.

I hope these points will help fellow programmers.