Query times out when run from ADO.NET but runs fin

问题:

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:

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)

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

回答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.

回答2:

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.