RDLC report generation takes so much time with large data to display

Question

1.00/5 (2 votes)

See more:

I am working with rdlc report on a C# windows application, while retrieving a large set of data (say 10, 000 records) from Sqlite database, it takes more than three minutes for the report to generate and render, i think that is too much of time, i have tried to do a little optimization by indexing all fields in my where clause, yet no improvement, i cant do a stored procedure since Sqlite does not have support for it.

What I have tried:

C#

private DataSet GetData()
    {

        string select = "SELECT DISTINCT OrganisationId AS OrgId,OrganisationName AS OrgName,TaxIdNumber AS TIN,StaffId,StaffName,Department AS Dept,Position,Month,Year,SUM(NoOfMonthsWorked) AS NMnth," +
                        "SUM(BasicSalary) AS BasicSalary,SUM(TransAllowance) AS Transp,SUM(UtilityAllowance) AS Utility,SUM(HousingAllowance) AS Housing,SUM(LeaveAllowance) AS Leave,SUM(ThirteenthMonthAllowance) AS ThirteenMnth," +
                        "SUM(OvertimeAllowance) AS Overtime,SUM(EntertainmentAllowance) AS EnterMnt,SUM(DomesticAllowance) As Domestic,SUM(EducationAllowance) AS Education,SUM(PersonalAllowance) AS Personal,SUM(OtherAllowances) AS Others," +
                        "SUM(HealthFund) AS NHIS,SUM(HousingFund) AS NHF,SUM(LifeAssurance) AS LA,SUM(Gratuities) AS Gratuities,SUM(Pension) AS Pension,SUM(OtherStatutoryDeductions) AS OtherDeducts," +
                        "SUM(ConsolidatedRelief) AS CRA,SUM(TotalRelief) AS TotalRelief,SUM(TaxableIncome) AS TaxableIncome,SUM(TaxExempts) As TTaxExempt,SUM(GrossIncome) AS GrossIncome,SUM(MonthlyTaxDue) AS TaxPayable " +
                        "FROM IncomeTax WHERE(OrganisationId = @OrganisationId AND Year = @Year) GROUP BY StaffId";
        using (SQLiteConnection con = new SQLiteConnection(connstring))
        {
            using (SQLiteCommand cmd = new SQLiteCommand(select))
            {
                using (SQLiteDataAdapter sda = new SQLiteDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandTimeout = 9000000;
                    cmd.Parameters.Add("@OrganisationId", DbType.String, 50).Value = txtOrgId.Text.ToUpper();
                    //cmd.Parameters.Add("@Month", DbType.String, 50).Value = cboMonthSearch.SelectedItem.ToString();
                    cmd.Parameters.Add("@Year", DbType.String, 50).Value = cboYearSearch.SelectedItem.ToString().ToUpper();
                    using (DataSet dsCustomers = new DataSet1())
                    {
                        sda.Fill(dsCustomers, "DataTable2");
                        if (dsCustomers.Tables[1].Rows.Count == 0)
                        {
                            reportViewer1.Visible = false;
                            MessageBox.Show("Records not found!");
                        }
                        else
                        {
                            reportViewer1.Visible = true;
                        }
                        return dsCustomers;
                    }
                }
            }
        }
    }

The button Click Event:

C#

private void btnShowReport_Click(object sender, EventArgs e)
    {
        errorProvider1.Clear();
        if (txtOrgId.Text != "")
        {
            if (cboYearSearch.SelectedIndex != 0)
            {
                DataSet dsCustomers = GetData();
                ReportDataSource datasource = new ReportDataSource("DataSet1", dsCustomers.Tables[1]);
                this.reportViewer1.LocalReport.DataSources.Clear();
                reportViewer1.ProcessingMode = ProcessingMode.Local;
                // the ReportPath is relative to the page displaying the ReportViewer
                reportViewer1.LocalReport.ReportPath = "Report2.rdlc";
                this.reportViewer1.LocalReport.DataSources.Add(datasource);
                this.reportViewer1.RefreshReport();
            }
            else
            {
                errorProvider1.SetError(cboYearSearch, "Select Year!");
                cboYearSearch.Focus();
            }
        }
        else
        {
            errorProvider1.SetError(txtOrgId, "Enter OrgId!");
            txtOrgId.Focus();
        }
    }

I will appreciate if anyone could assist on how to get it display a bit more faster. Thanks

Posted 3-May-17 4:24am

Uwakpeter

Updated 3-May-17 4:30am

CHill60

v2

Add a Solution

Comments

CHill60 3-May-17 11:01am

I don't understand how you are getting any results at all as you have items in the SELECT that are not aggregate functions nor are they in the GROUP BY clause

Uwakpeter 3-May-17 11:16am

Query finished in 0.046 second(s) while running on sqlite query window. i have another select statement that does not use group by clause, it still take the same amount of time to display!

CHill60 3-May-17 12:38pm

Ok, I've never used SQLite before but now I know it doesn't adhere to T-SQL standards for Grouping.
However if the query runs in 0.046 seconds then the problem must be with your report. Do you have any calculations or fancy formatting in your report?

Uwakpeter 3-May-17 13:46pm

The report has 35 columns, i used this: =IIF((Fields!Dept.Value ="Nill"), true,false) for column visibility and also:
=FormatNumber(Fields!Transp.Value,2) on 20 Textbox expressions, could this be the issue?

Richard Deeming 4-May-17 9:07am

Since you're returning dsCustomers from your GetData method, you don't want to put it in a using block.

Replace:

using (DataSet dsCustomers = new DataSet1())

with:

DataSet dsCustomers = new DataSet1();

Your query only returns a single table, so it's not clear why you're always accessing the second table? (Remember, the collections use zero-based indexing, so Tables[1] returns the second table.)

Is there anything odd going on in your custom DataSet1 class? Have you tried using a raw DataSet instead?

Uwakpeter 4-May-17 10:30am

i have tried using DataSet dsCustomers = new DataSet1(); but it still takes the same amount of time. I am using Table[1] because i have like six Datatables on one DataSet, the six Datatables are for different reports, no two reports can be accessed at the same time.

Richard Deeming 4-May-17 11:10am

You don't need a custom data set with tables for all of your reports. Try using a standard DataSet instead, with Tables[0] instead of Tables[1].

Uwakpeter 4-May-17 12:15pm

yes, i have tried it seems to display faster now, but when i added this lines of code: =IIF((Fields!TIN.Value ="Nill"), true,false),=Max(Fields!Transp.Value + 0) = 0 And Min(Fields!Transp.Value + 0) = 0.

To the report to hide some columns if they are Nill or zero, it became very slow again, Please, is there a better way to hide column than this?

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)