如何避免connectiontimeout异常？ [英] How to avoid connectiontimeout exception ?

问题描述

我有一张桌子TRnasaction;

有字段，SlNo，Date，Currency，BuyRate，SellRate，CustomerID



（此表有超过100万条记录（1,766,568,987）条记录，

在Feching中显示ConnectionTimeOutException，这些记录



我尝试过：



从TRANSACTION T中选择SlNo，Date，CurrencyID，BuyRate，SellRate，CustomerID

内连接T.CurrencyID上的CURRENCY C = C.CurrencyID

内部联接CUSTOMER CS on T.CustomerID = CS.CustomerID

解决方案

0）增加 SQlCommand.CommandTimeout ： SqlCommand.CommandTimeout属性（System.Data.SqlClient） [ ^ ]

1）查询较少的数据（您不需要显示10亿条记录）

< b> 2）根据您的查询模式优化索引，使用SQL事件探查器：如何使用：使用SQL事件探查器 [ ^ ]

3）如果可能的话，将数据分区以降低表格大小：创建分区表和索引 [ ^ ]

您可以附加;连接超时= 30到您的连接字符串并指定您想要的值。

SqlConnection.ConnectionTimeout属性（System.Data.SqlC （）

I had a table "TRnasaction";
having fields, SlNo, Date,Currency, BuyRate,SellRate,CustomerID

( and this table have more than 1 million records( 1,766,568,987) records,
During Feching it showing the ConnectionTimeOutException, these records

What I have tried:

Select SlNo, Date, CurrencyID, BuyRate,SellRate,CustomerID from TRANSACTION T
inner join CURRENCY C on T.CurrencyID=C.CurrencyID
inner join CUSTOMER CS on T.CustomerID=CS.CustomerID

解决方案

0) Increase the SQlCommand.CommandTimeout : SqlCommand.CommandTimeout Property (System.Data.SqlClient)[^]
1) Query less data (you don't need to show 1 billion records)
2) Optimize your indexes based on your query pattern, use the SQL Profiler : How To: Use SQL Profiler[^]
3) If possible partition your data to lower the table size : Create Partitioned Tables and Indexes[^]

You can append ;Connection Timeout=30 to your connection string and specify the value you wish.
SqlConnection.ConnectionTimeout Property (System.Data.SqlClient)

A value of 0 indicates no limit, and should be avoided in a ConnectionString because an attempt to connect waits indefinitely.

这篇关于如何避免connectiontimeout异常？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！