i have written a query for mysql database. the result is to display in a Crystal Report.
The problem is, the execution time is too much(more than 30 minutes).
how can i reduce this execution time. Any guidelines is warmly welcome.
below is the c# code...
string strQry = "select distinct category,pay_mode,coalesce((select sum(amount) from sold_items where invoice_date between '" + dtfrom1 + "' and '" + dtto1 + "' and category = A.category and pay_mode = A.pay_mode and tax_percent = '0.00'),0.00) as taxFree,coalesce((select sum(amount) from sold_items where invoice_date between '" + dtfrom1 + "' and '" + dtto1 + "' and category = A.category and pay_mode = A.pay_mode and tax_percent = '5.00'),0.00) as amountSub5,coalesce((select sum(amount) from sold_items where invoice_date between '" + dtfrom1 + "' and '" + dtto1 + "' and category = A.category and pay_mode = A.pay_mode and tax_percent = '14.00'),0.00) as amountSub14 from sold_items A where invoice_date between '" + dtfrom1 + "' and '" + dtto1 + "'";
MySqlDataAdapter da = new MySqlDataAdapter(strQry,con);
DataSet2 ds1 = new DataSet2();
da.SelectCommand.CommandTimeout = 3600;
da.Fill(ds1, "saletaxreport1");
CRSaletaxReport_Initial pr = new CRSaletaxReport_Initial();
pr.SetDataSource(ds1);
pr.VerifyDatabase();
crystalReportViewer1.ReportSource = pr;
below is the value of strQry variable...
select distinct invoice_date,coalesce((select sum(amount) from sold_items B where B.invoice_date = A.invoice_date and B.pay_mode=A.pay_mode and tax_percent = '00.00'),0.00) as taxFree,coalesce((select sum(amount) from sold_items B where B.invoice_date = A.invoice_date and B.pay_mode=A.pay_mode and tax_percent = '5.00'),0.00) as amountSub5,coalesce((select sum(amount) from sold_items B where B.invoice_date = A.invoice_date and B.pay_mode=A.pay_mode and tax_percent = '14.00'),0.00) as amountSub14,pay_mode from sold_items A where invoice_date between '2013/11/24' and '2013/11/26' ORDER BY invoice_date
below is my explain plan...
id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'PRIMARY', 'A', 'ALL', '', '', '', '', 102746, 'Using where; Using temporary; Using filesort'
4, 'DEPENDENT SUBQUERY', 'B', 'ALL', '', '', '', '', 102746, 'Using where'
3, 'DEPENDENT SUBQUERY', 'B', 'ALL', '', '', '', '', 102746, 'Using where'
2, 'DEPENDENT SUBQUERY', 'B', 'ALL', '', '', '', '', 102746, 'Using where'