异常详细信息是这样的:System.Data.SqlClient.SqlException: 超时时间已到。在操作完成之前超时时间已过或服务器未响应。页面主要是完成统计功能,由于统计跨了好几个表,表里的数据也比较多有个十多万条吧,sql语句采用的是连接形式:
方法是这样的,有几十行:
public static DataTable GetShuiFeiBaoBiao(string year, string month)
{
StringBuilder sb = new StringBuilder();
sb.Append(" left join (select count(*) as shichaoshu,meterbook from SF_WaterVolumn ,(select meterbook,meterNum from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0) SF_sysWaterMeter where SF_WaterVolumn.WaterMeterNum=SF_sysWaterMeter.meterNum and year='" + year + "' " + month + " group by SF_sysWaterMeter.meterbook) b on a.number=b.meterbook ");
sb.Append(" left join (select count(*) as baotingshu ,meterBook from SF_TempMeter where year='" + year + "' " + month + " and MeterState=17 group by meterBook) c on c.meterBook=a.number "); sb.Append(" left join (select sum(watervolumn) as jumin,meterbook from (select WaterMeterNum,WaterVolumn from SF_ReceivableLibrary where SF_ReceivableLibrary.PriceListID='1' and year='" + year + "' " + month + " and not exists (select distinct(meterNum) from SF_AddReducePrice where SF_ReceivableLibrary.WaterMeterNum=SF_AddReducePrice.meterNum and year='" + year + "' " + month + " ))as Volumn,(select meterbook,meterNum,waterType from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as SF_sysWaterMeter where SF_sysWaterMeter.meterNum=Volumn.WaterMeterNum and SF_sysWaterMeter.waterType='1' group by meterBook )d on d.meterBook=a.number ");
sb.Append(" left join (select sum(watervolumn) as gongshang,meterbook from (select WaterMeterNum,WaterVolumn from SF_ReceivableLibrary where SF_ReceivableLibrary.PriceListID='1' and year='" + year + "' " + month + " and not exists (select distinct(meterNum) from SF_AddReducePrice where SF_ReceivableLibrary.WaterMeterNum=SF_AddReducePrice.meterNum and year='" + year + "' " + month + " ))as Volumn,(select meterbook,meterNum,waterType from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as SF_sysWaterMeter where SF_sysWaterMeter.meterNum=Volumn.WaterMeterNum and SF_sysWaterMeter.waterType='7' group by meterBook )e on e.meterBook=a.number ");
sb.Append(" left join (select sum(watervolumn) as jiguan,meterbook from (select WaterMeterNum,WaterVolumn from SF_ReceivableLibrary where SF_ReceivableLibrary.PriceListID='1' and year='" + year + "' " + month + " and not exists (select distinct(meterNum) from SF_AddReducePrice where SF_ReceivableLibrary.WaterMeterNum=SF_AddReducePrice.meterNum and year='" + year + "' " + month + " ))as Volumn,(select meterbook,meterNum,waterType from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as SF_sysWaterMeter where SF_sysWaterMeter.meterNum=Volumn.WaterMeterNum and SF_sysWaterMeter.waterType='3' group by meterBook )f on f.meterBook=a.number ");
sb.Append(" left join (select sum(watervolumn) as jingying,meterbook from (select WaterMeterNum,WaterVolumn from SF_ReceivableLibrary where SF_ReceivableLibrary.PriceListID='1' and year='" + year + "' " + month + " and not exists (select distinct(meterNum) from SF_AddReducePrice where SF_ReceivableLibrary.WaterMeterNum=SF_AddReducePrice.meterNum and year='" + year + "' " + month + " ))as Volumn,(select meterbook,meterNum,waterType from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as SF_sysWaterMeter where SF_sysWaterMeter.meterNum=Volumn.WaterMeterNum and SF_sysWaterMeter.waterType='4' group by meterBook )g on g.meterBook=a.number ");
sb.Append(" left join (select sum(watervolumn) as tezhong,meterbook from (select WaterMeterNum,WaterVolumn from SF_ReceivableLibrary where SF_ReceivableLibrary.PriceListID='1' and year='" + year + "' " + month + " and not exists (select distinct(meterNum) from SF_AddReducePrice where SF_ReceivableLibrary.WaterMeterNum=SF_AddReducePrice.meterNum and year='" + year + "' " + month + " ))as Volumn,(select meterbook,meterNum,waterType from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as SF_sysWaterMeter where SF_sysWaterMeter.meterNum=Volumn.WaterMeterNum and SF_sysWaterMeter.waterType='5' group by meterBook )h on h.meterBook=a.number "); sb.Append(" left join (select sum(watervolumn) as heji,meterbook from dbo.SF_WaterVolumn,(select meterbook,meterNum from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') SF_sysWaterMeter where meterNum=WaterMeterNum and year='" + year + "' " + month + " group by meterbook )i on i.meterbook=a.number "); sb.Append(" left join (select sum(ItemAmount) as putongyingshu,meterbook from dbo.SF_ReceivableLibrary, (select meterbook,meterNum from dbo.SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as sysWaterMeter where sysWaterMeter.meterNum=WaterMeterNum and year='" + year + "' " + month + " and PriceListID='1' and DiscountPrice='-1.00' group by meterbook )j on j.meterbook=a.number ");
sb.Append(" left join (select sum(DiscountPrice) as teshuyingshu,meterbook from dbo.SF_ReceivableLibrary, (select meterbook,meterNum from dbo.SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as sysWaterMeter where sysWaterMeter.meterNum=WaterMeterNum and year='" + year + "' " + month + " and PriceListID='1' and SF_ReceivableLibrary.DiscountPrice!='-1.00' group by meterbook )k on k.meterbook=a.number");
sb.Append(" left join (select sum(Money) as bucha,meterbook from SF_AddReducePrice, (select meterbook,meterNum from dbo.SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as sysWaterMeter where SF_AddReducePrice.meterNum=sysWaterMeter.meterNum and year='" + year + "' " + month + " and PriceListID='1' group by meterbook )m on m.meterbook=a.number");
sb.Append(" left join (select sum(Itemprice) as shishou,meterbook from dbo.SF_Paidlibrary, (select meterbook,meterNum from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as sysWaterMeter where sysWaterMeter.meterNum=WaterMeterNum and year='" + year + "' " + month + " and PriceLIstID='1' group by meterbook )l on l.meterbook=a.number"); sb.Append(" left join (select meterbook,sum(ShuiLiang) as juminbucha from SF_AddReducePrice,(select meterbook,meterNum from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as sysWaterMeter where SF_AddReducePrice.meterNum=sysWaterMeter.meterNum and year='" + year + "' " + month + " and PriceListID='1' and WaterType='1'group by meterbook)n on n.meterbook=a.number");
sb.Append(" left join (select meterbook,sum(ShuiLiang) as gongshangbucha from SF_AddReducePrice,(select meterbook,meterNum from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as sysWaterMeter where SF_AddReducePrice.meterNum=sysWaterMeter.meterNum and year='" + year + "' " + month + " and PriceListID='1' and WaterType='7'group by meterbook)o on o.meterbook=a.number ");
sb.Append(" left join (select meterbook,sum(ShuiLiang) as jiguanbucha from SF_AddReducePrice,(select meterbook,meterNum from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as sysWaterMeter where SF_AddReducePrice.meterNum=sysWaterMeter.meterNum and year='" + year + "' " + month + " and PriceListID='1' and WaterType='3'group by meterbook)p on p.meterbook=a.number");
sb.Append(" left join (select meterbook,sum(ShuiLiang) as jingyingbucha from SF_AddReducePrice,(select meterbook,meterNum from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as sysWaterMeter where SF_AddReducePrice.meterNum=sysWaterMeter.meterNum and year='" + year + "' " + month + " and PriceListID='1' and WaterType='4'group by meterbook)q on q.meterbook=a.number");
sb.Append(" left join (select meterbook,sum(ShuiLiang) as tezhongbucha from SF_AddReducePrice,(select meterbook,meterNum from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as sysWaterMeter where SF_AddReducePrice.meterNum=sysWaterMeter.meterNum and year='" + year + "' " + month + " and PriceListID='1' and WaterType='5' group by meterbook)r on r.meterbook=a.number"); sb.Append(" left join (select meterbook,(sum(WaterVolumn)-sum(ShuiLiang)) as shijijumin from SF_AddReducePrice,(select meterbook,meterNum,waterType from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as sysWaterMeter,(select WaterVolumn,WaterMeterNum from SF_ReceivableLibrary where year='" + year + "' " + month + " and PriceListID='1') as SF_ReceivableLibrary where SF_AddReducePrice.meterNum=sysWaterMeter.meterNum and SF_ReceivableLibrary.WaterMeterNum=SF_AddReducePrice.meterNum and sysWaterMeter.meterNum =SF_ReceivableLibrary.WaterMeterNum and year='" + year + "' " + month + " and SF_AddReducePrice.PriceListID='1'and sysWaterMeter.WaterType='1' group by meterbook )s on s.meterbook=a.number ");
…………//此处还有好几个这样的sb.append DataTable dt = DBHepler.DataTable(sb.ToString()); return dt;
}
结果页面运行30秒左右,就出现最上面的错误,顺便写下我的配置信息:
<add key="ConnectionString" value="server=.\NEWDB;Initial Catalog=water;Integrated Security=True;Connect Timeout=500" />
方法是这样的,有几十行:
public static DataTable GetShuiFeiBaoBiao(string year, string month)
{
StringBuilder sb = new StringBuilder();
sb.Append(" left join (select count(*) as shichaoshu,meterbook from SF_WaterVolumn ,(select meterbook,meterNum from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0) SF_sysWaterMeter where SF_WaterVolumn.WaterMeterNum=SF_sysWaterMeter.meterNum and year='" + year + "' " + month + " group by SF_sysWaterMeter.meterbook) b on a.number=b.meterbook ");
sb.Append(" left join (select count(*) as baotingshu ,meterBook from SF_TempMeter where year='" + year + "' " + month + " and MeterState=17 group by meterBook) c on c.meterBook=a.number "); sb.Append(" left join (select sum(watervolumn) as jumin,meterbook from (select WaterMeterNum,WaterVolumn from SF_ReceivableLibrary where SF_ReceivableLibrary.PriceListID='1' and year='" + year + "' " + month + " and not exists (select distinct(meterNum) from SF_AddReducePrice where SF_ReceivableLibrary.WaterMeterNum=SF_AddReducePrice.meterNum and year='" + year + "' " + month + " ))as Volumn,(select meterbook,meterNum,waterType from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as SF_sysWaterMeter where SF_sysWaterMeter.meterNum=Volumn.WaterMeterNum and SF_sysWaterMeter.waterType='1' group by meterBook )d on d.meterBook=a.number ");
sb.Append(" left join (select sum(watervolumn) as gongshang,meterbook from (select WaterMeterNum,WaterVolumn from SF_ReceivableLibrary where SF_ReceivableLibrary.PriceListID='1' and year='" + year + "' " + month + " and not exists (select distinct(meterNum) from SF_AddReducePrice where SF_ReceivableLibrary.WaterMeterNum=SF_AddReducePrice.meterNum and year='" + year + "' " + month + " ))as Volumn,(select meterbook,meterNum,waterType from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as SF_sysWaterMeter where SF_sysWaterMeter.meterNum=Volumn.WaterMeterNum and SF_sysWaterMeter.waterType='7' group by meterBook )e on e.meterBook=a.number ");
sb.Append(" left join (select sum(watervolumn) as jiguan,meterbook from (select WaterMeterNum,WaterVolumn from SF_ReceivableLibrary where SF_ReceivableLibrary.PriceListID='1' and year='" + year + "' " + month + " and not exists (select distinct(meterNum) from SF_AddReducePrice where SF_ReceivableLibrary.WaterMeterNum=SF_AddReducePrice.meterNum and year='" + year + "' " + month + " ))as Volumn,(select meterbook,meterNum,waterType from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as SF_sysWaterMeter where SF_sysWaterMeter.meterNum=Volumn.WaterMeterNum and SF_sysWaterMeter.waterType='3' group by meterBook )f on f.meterBook=a.number ");
sb.Append(" left join (select sum(watervolumn) as jingying,meterbook from (select WaterMeterNum,WaterVolumn from SF_ReceivableLibrary where SF_ReceivableLibrary.PriceListID='1' and year='" + year + "' " + month + " and not exists (select distinct(meterNum) from SF_AddReducePrice where SF_ReceivableLibrary.WaterMeterNum=SF_AddReducePrice.meterNum and year='" + year + "' " + month + " ))as Volumn,(select meterbook,meterNum,waterType from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as SF_sysWaterMeter where SF_sysWaterMeter.meterNum=Volumn.WaterMeterNum and SF_sysWaterMeter.waterType='4' group by meterBook )g on g.meterBook=a.number ");
sb.Append(" left join (select sum(watervolumn) as tezhong,meterbook from (select WaterMeterNum,WaterVolumn from SF_ReceivableLibrary where SF_ReceivableLibrary.PriceListID='1' and year='" + year + "' " + month + " and not exists (select distinct(meterNum) from SF_AddReducePrice where SF_ReceivableLibrary.WaterMeterNum=SF_AddReducePrice.meterNum and year='" + year + "' " + month + " ))as Volumn,(select meterbook,meterNum,waterType from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as SF_sysWaterMeter where SF_sysWaterMeter.meterNum=Volumn.WaterMeterNum and SF_sysWaterMeter.waterType='5' group by meterBook )h on h.meterBook=a.number "); sb.Append(" left join (select sum(watervolumn) as heji,meterbook from dbo.SF_WaterVolumn,(select meterbook,meterNum from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') SF_sysWaterMeter where meterNum=WaterMeterNum and year='" + year + "' " + month + " group by meterbook )i on i.meterbook=a.number "); sb.Append(" left join (select sum(ItemAmount) as putongyingshu,meterbook from dbo.SF_ReceivableLibrary, (select meterbook,meterNum from dbo.SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as sysWaterMeter where sysWaterMeter.meterNum=WaterMeterNum and year='" + year + "' " + month + " and PriceListID='1' and DiscountPrice='-1.00' group by meterbook )j on j.meterbook=a.number ");
sb.Append(" left join (select sum(DiscountPrice) as teshuyingshu,meterbook from dbo.SF_ReceivableLibrary, (select meterbook,meterNum from dbo.SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as sysWaterMeter where sysWaterMeter.meterNum=WaterMeterNum and year='" + year + "' " + month + " and PriceListID='1' and SF_ReceivableLibrary.DiscountPrice!='-1.00' group by meterbook )k on k.meterbook=a.number");
sb.Append(" left join (select sum(Money) as bucha,meterbook from SF_AddReducePrice, (select meterbook,meterNum from dbo.SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as sysWaterMeter where SF_AddReducePrice.meterNum=sysWaterMeter.meterNum and year='" + year + "' " + month + " and PriceListID='1' group by meterbook )m on m.meterbook=a.number");
sb.Append(" left join (select sum(Itemprice) as shishou,meterbook from dbo.SF_Paidlibrary, (select meterbook,meterNum from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as sysWaterMeter where sysWaterMeter.meterNum=WaterMeterNum and year='" + year + "' " + month + " and PriceLIstID='1' group by meterbook )l on l.meterbook=a.number"); sb.Append(" left join (select meterbook,sum(ShuiLiang) as juminbucha from SF_AddReducePrice,(select meterbook,meterNum from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as sysWaterMeter where SF_AddReducePrice.meterNum=sysWaterMeter.meterNum and year='" + year + "' " + month + " and PriceListID='1' and WaterType='1'group by meterbook)n on n.meterbook=a.number");
sb.Append(" left join (select meterbook,sum(ShuiLiang) as gongshangbucha from SF_AddReducePrice,(select meterbook,meterNum from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as sysWaterMeter where SF_AddReducePrice.meterNum=sysWaterMeter.meterNum and year='" + year + "' " + month + " and PriceListID='1' and WaterType='7'group by meterbook)o on o.meterbook=a.number ");
sb.Append(" left join (select meterbook,sum(ShuiLiang) as jiguanbucha from SF_AddReducePrice,(select meterbook,meterNum from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as sysWaterMeter where SF_AddReducePrice.meterNum=sysWaterMeter.meterNum and year='" + year + "' " + month + " and PriceListID='1' and WaterType='3'group by meterbook)p on p.meterbook=a.number");
sb.Append(" left join (select meterbook,sum(ShuiLiang) as jingyingbucha from SF_AddReducePrice,(select meterbook,meterNum from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as sysWaterMeter where SF_AddReducePrice.meterNum=sysWaterMeter.meterNum and year='" + year + "' " + month + " and PriceListID='1' and WaterType='4'group by meterbook)q on q.meterbook=a.number");
sb.Append(" left join (select meterbook,sum(ShuiLiang) as tezhongbucha from SF_AddReducePrice,(select meterbook,meterNum from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as sysWaterMeter where SF_AddReducePrice.meterNum=sysWaterMeter.meterNum and year='" + year + "' " + month + " and PriceListID='1' and WaterType='5' group by meterbook)r on r.meterbook=a.number"); sb.Append(" left join (select meterbook,(sum(WaterVolumn)-sum(ShuiLiang)) as shijijumin from SF_AddReducePrice,(select meterbook,meterNum,waterType from SF_TempMeter where year='" + year + "' " + month + " and PrimaryMeter=0 and name='普通水表') as sysWaterMeter,(select WaterVolumn,WaterMeterNum from SF_ReceivableLibrary where year='" + year + "' " + month + " and PriceListID='1') as SF_ReceivableLibrary where SF_AddReducePrice.meterNum=sysWaterMeter.meterNum and SF_ReceivableLibrary.WaterMeterNum=SF_AddReducePrice.meterNum and sysWaterMeter.meterNum =SF_ReceivableLibrary.WaterMeterNum and year='" + year + "' " + month + " and SF_AddReducePrice.PriceListID='1'and sysWaterMeter.WaterType='1' group by meterbook )s on s.meterbook=a.number ");
…………//此处还有好几个这样的sb.append DataTable dt = DBHepler.DataTable(sb.ToString()); return dt;
}
结果页面运行30秒左右,就出现最上面的错误,顺便写下我的配置信息:
<add key="ConnectionString" value="server=.\NEWDB;Initial Catalog=water;Integrated Security=True;Connect Timeout=500" />
可以用timeout解决sql timeout时间默认30秒,超出这个时间就会引发你那个异常。
2.檢查你是連接數據庫的網絡是否穩定
//DataTable dt = DBHepler.DataTable(sb.ToString());
//打印你构造的查询字符串,然后拷贝到查询分析器中去执行一下,看看提取数据是否也奇慢(我觉得肯定也是非常慢)。建议在窗体上拖一个RichTextBox控件,将构造的查询语句放到里面,然后复制粘贴到查询分析器去执行,然后看看执行所需时间
richtextbox1.Text=sb.ToString();