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

在本地开发机器上，我的查询在通过网站(ASP)运行时无限期地超时。NET - ADO.NET)同样的查询昨天还在运行。当我从本地机器执行时，它运行得很好。

I even resorted to rebooting machine. What can it be?

我甚至求助于重启机器。会是什么呢?

Explanations per requests:

每个请求的解释:

1. 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)

   查询在默认30秒后超时(在ADO.NET中)。如果我把它设为0(不定)——它会无限运行。在查询分析器中，它立即运行(1秒)

2. 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;
}

2 个解决方案

#1

Not enough information to go on, but I'd start by changing all of your calls to .AddWithValue() to use .Add() instead. When you call .AddWithValue(), .Net has to guess what the type of your parameter is. If it guesses wrong (and it can), suddenly your query might no longer match with an index, and that speaks to the core of database performance.

虽然没有足够的信息，但是我首先将您的所有调用更改为. addwithvalue()以使用. add()。当您调用. addwithvalue()时，. net必须猜测参数的类型。如果它猜测错误(并且可以)，那么您的查询可能会突然与索引不匹配，这就涉及到了数据库性能的核心。

#2

Differences in performance between Query Analyzer and ADO.NET are often related to different configuration of the database connection (e.g. ANSI_NULLS).

查询分析器和ADO的性能差异。NET通常与数据库连接的不同配置相关(例如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.

如果您确信您正在使用完全相同的查询(相同的客户id，相同的日期范围)，您可以尝试使用ANSI_NULLS和查询分析器中的其他设置来尝试复制您在ADO.NET上看到的行为。