mysql需要花费太多时间来执行，如何减少执行时间或更快地执行它

问题描述

我已经为mysql数据库编写了一个查询。结果是显示在Crystal Report中。

问题是，执行时间太长（超过30分钟）。

如何减少执行时间。我们热烈欢迎任何指南。

以下是c＃代码...

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;//60 minutes
                da.Fill(ds1, "saletaxreport1");
                CRSaletaxReport_Initial pr = new CRSaletaxReport_Initial();
                pr.SetDataSource(ds1);
                pr.VerifyDatabase();
                crystalReportViewer1.ReportSource = pr;

以下是strQry变量的值......

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

以下是我的解释计划......

id，select_type，table ，type，possible_keys，key，key_len，ref，rows，Extra

1，'PRIMARY'，'A'，'ALL'，''，''，''，' ，102746，使用其中;使用临时;使用filesort'

4，'依赖子请求'，'B'，'全部'，''，''，''，''，102746，'使用位置'

3，'依赖子信息'，'B'，'全'，''，''，''，''，102746，'使用位置'

2，'相关子信息'， 'B'，'ALL'，''，''，''，''，102746，'使用where'

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'

推荐答案

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

这篇关于mysql需要花费太多时间来执行，如何减少执行时间或更快地执行它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！