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:
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("@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:
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;
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