从ADO.NET运行，但如果查询超时的查询Analyer运行良好 [英] Query times out when run from ADO.NET but runs fine in Query Analyer
问题描述
在本地开发机器上我查询超时无限期地通过网站上运行时（ASP.NET - ADO.NET） 同样的查询跑就在昨天罚款。当我从本地计算机上执行它运行良好。
我还使出重新启动机器。这里是什么地方？
每个请求说明：
-
查询超时默认为30秒（ADO.NET中）之后。如果我将它设置为0（无限期） - 它运行下去。在查询分析器它会立即运行（1秒）
-
查询并没有改变。这里是code的看法：
公共静态列表＆LT;装运＆GT; GetShipments（名单＆LT;字符串＆GT; customerIds，日期时间dateFrom，日期时间dateTo）
{
尝试
{
VAR数据=新的名单，其中，运费及GT;（）;
使用（VAR连接=新的SqlConnection（ConnectionString的））
{
connection.Open（）;
常量字符串SQL = @
SELECT TOP 1000 SH.ShipmentId，SH.TripId，CASE IsCancelled 1时然后'X'ELSE SH.Status结束状态，
SH.FromMunicipality，SH.FromAdministrativeArea，
SH.ToMunicipality，SH.ToAdministrativeArea，
SH.PONumber，SH.Pro codeID，SH.ShipperReferenceNumber，SH.BOLNumber，
T.ScheduledPickupDate，T.ScheduledDeliveryDate，
不用时TN.PDFBinary为null，则1 ELSE 0 END HasPOD
从dbo.vPcyShipment SH
INNER JOIN dbo.vPcyTrip T ON SH.TripId = T.TripId
LEFT OUTER JOIN dbo.tTripNumber TN ON SH.TripId = TN.TripNumber
WHERE SH.CustomerId IN（{0}）
与T.ScheduledPickupDate @DateFrom和@DateTo间
ORDER BY T.ScheduledPickupDate DESC;
变种customerParamNames = customerIds.Select（（S，I）=＆gt;中@customer+ i.ToString（CultureInfo.InvariantCulture））的ToArray（）。
变种customerInClause =的string.join（，，customerParamNames）;
使用（VAR命令=新的SqlCommand（的String.Format（SQL，customerInClause），连接））
{
command.Parameters.AddWithValue（@ DateFrom，dateFrom）;
command.Parameters.AddWithValue（@ DateTo，dateTo）;
对于（VAR I = 0; I＆LT; customerParamNames.Length;我++）
{
command.Parameters.AddWithValue（customerParamNames [I]，customerIds [I]）;
}
使用（VAR的dataTable =新的DataTable（））
{
dataTable.Load（Command.ExecuteReader却（））;
VAR的查询=从行dataTable.AsEnumerable（）
选择新货
{
ShipmentId = row.Field＆LT;字符串＆GT;（ShipmentId），
TRIPID = row.Field＆LT;字符串＆GT;（TRIPID），
PONO = row.Field＆LT;字符串＆GT;（PONumber），
临codeID = row.Field＆LT;字符串＆GT;（临codeID），
ShipperRef = row.Field＆LT;字符串＆GT;（ShipperReferenceNumber），
BolNo = row.Field＆LT;字符串＆GT;（BOLNumber），
ProphecyStatus code = row.Field＆LT;字符串＆GT;（状态），
FromCity = row.Field＆LT;字符串＆GT;（FromMunicipality），
FromState = row.Field＆LT;字符串＆gt;中FromAdministrativeArea），
ToCity = row.Field＆LT;字符串＆GT;（ToMunicipality），
ToState = row.Field＆LT;字符串＆GT;（ToAdministrativeArea），
ScheduledPickup = row.Field＆LT; DateTime的＆GT;（ScheduledPickupDate），
ScheduledDelivery = row.Field＆LT; DateTime的＆GT;（ScheduledDeliveryDate），
HasPOD = row.Field＆其中; INT＆GT（HasPOD）
};
data.AddRange（query.ToList（））;
}
}
}
返回的数据;
}
赶上（例外前）
{
登录（前）;
}
返回null;
}
在查询分析器和ADO.NET之间的性能差异往往与数据库连接的不同的配置（如ANSI_NULLS）。
如果你确定你使用的是完全相同的查询（相同的客户ID，同一日期范围），你可以尝试在查询分析器ANSI_NULLS和其他设置打去尝试重现你看到与ADO行为.NET。
On local development machine my query times out indefinitely when run via website (ASP.NET - ADO.NET) Same query was running just yesterday fine. It runs fine when I execute it from local machine.
I even resorted to rebooting machine. What can it be?
Explanations per requests:
Query times out after default 30 seconds (in ADO.NET). If I set it to 0 (indefinite) - it runs indefinitely. In Query analyzer it runs immediately (1 second)
Query didn't change. Here is code for view:
.
public static List<Shipment> GetShipments(List<string> customerIds, DateTime dateFrom, DateTime dateTo)
{
try
{
var data = new List<Shipment>();
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
const string SQL = @"
SELECT TOP 1000 SH.ShipmentId, SH.TripId, CASE IsCancelled WHEN 1 THEN 'X' ELSE SH.Status END Status,
SH.FromMunicipality, SH.FromAdministrativeArea,
SH.ToMunicipality, SH.ToAdministrativeArea,
SH.PONumber, SH.ProCodeId, SH.ShipperReferenceNumber, SH.BOLNumber,
T.ScheduledPickupDate, T.ScheduledDeliveryDate,
CASE WHEN NOT TN.PDFBinary IS NULL THEN 1 ELSE 0 END HasPOD
FROM dbo.vPcyShipment SH
INNER JOIN dbo.vPcyTrip T ON SH.TripId = T.TripId
LEFT OUTER JOIN dbo.tTripNumber TN ON SH.TripId = TN.TripNumber
WHERE SH.CustomerId IN ({0})
AND T.ScheduledPickupDate BETWEEN @DateFrom AND @DateTo
ORDER BY T.ScheduledPickupDate DESC";
var customerParamNames = customerIds.Select((s, i) => "@customer" + i.ToString(CultureInfo.InvariantCulture)).ToArray();
var customerInClause = string.Join(",", customerParamNames);
using (var command = new SqlCommand(string.Format(SQL, customerInClause), connection))
{
command.Parameters.AddWithValue("@DateFrom", dateFrom);
command.Parameters.AddWithValue("@DateTo", dateTo);
for (var i = 0; i < customerParamNames.Length; i++)
{
command.Parameters.AddWithValue(customerParamNames[i], customerIds[i]);
}
using (var dataTable = new DataTable())
{
dataTable.Load(command.ExecuteReader());
var query = from row in dataTable.AsEnumerable()
select new Shipment
{
ShipmentId = row.Field<string>("ShipmentId"),
TripId = row.Field<string>("TripId"),
PoNo = row.Field<string>("PONumber"),
ProCodeId = row.Field<string>("ProCodeId"),
ShipperRef = row.Field<string>("ShipperReferenceNumber"),
BolNo = row.Field<string>("BOLNumber"),
ProphecyStatusCode = row.Field<string>("Status"),
FromCity = row.Field<string>("FromMunicipality"),
FromState = row.Field<string>"FromAdministrativeArea"),
ToCity = row.Field<string>("ToMunicipality"),
ToState = row.Field<string>("ToAdministrativeArea"),
ScheduledPickup = row.Field<DateTime>("ScheduledPickupDate"),
ScheduledDelivery = row.Field<DateTime>("ScheduledDeliveryDate"),
HasPOD = row.Field<int>("HasPOD")
};
data.AddRange(query.ToList());
}
}
}
return data;
}
catch (Exception ex)
{
Log(ex);
}
return null;
}
Differences in performance between Query Analyzer and ADO.NET are often related to different configuration of the database connection (e.g. ANSI_NULLS).
If you're sure you're using exactly the same query (same customer ids, same date range), you could try playing with ANSI_NULLS and other settings in Query Analyzer to try to reproduce the behavior you're seeing with ADO.NET.
这篇关于从ADO.NET运行，但如果查询超时的查询Analyer运行良好的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
