c# - SqlDataReader索引器性能

我有一些这样的代码：

using (var cmd = TransicsCommand.GetFleetCommand())
{
    cmd.CommandText = @"
                        SELECT dr.DeviceId, dr.DeviceTruckRelationId, dr.TruckId, dr.RelationCreatedOn,
                        dl.DriverLoginId, dl.DriverId, dl.UserType, dl.LoginType, dl.SecondsSince DriverLoginCreated,
                        Action.ActionId, Action.ActionTimestamp, Action.UserType actionusertype, Action.TripreportId,
                        DeviceHeaderData.DeviceHeaderid, DeviceHeaderData.Odo, DeviceHeaderData.X, DeviceHeaderData.Y,
                        DeviceHeaderData.ValidPosition, DeviceHeaderData.Tfu,
                        DeviceHeaderData.FuelPercentage, DeviceHeaderData.Speed,
                        InstructionsetAction.VersionId,
                        tc.CouplingId, tc.TrailerId, tc.CouplingEvent, tc.TrailerEntry, tc.SecondsSince
                        FROM TripReport.Action Action
                        INNER JOIN DeviceHeader.Data DeviceHeaderData ON Action.DeviceHeaderId = DeviceHeaderData.DeviceHeaderId
                        INNER JOIN Instructionset.Action InstructionsetAction  ON InstructionsetAction.ActionId = Action.ActionId
                        INNER JOIN DeviceHeader.Truck dht ON Action.DeviceHeaderId = dht.DeviceHeaderId
                        INNER JOIN Device.TruckRelation dr ON dht.DeviceRelationId = dr.DeviceTruckRelationId
                        LEFT OUTER JOIN [DeviceHeader].[LoginSession] dhls ON dhls.DeviceHeaderId = dht.DeviceHeaderId
                        LEFT OUTER JOIN [LogIn].DriverLogin as dl ON dhls.DriverLoginId = dl.DriverLoginId
                        LEFT OUTER JOIN [DeviceHeader].[TrailerCoupling] dhtc ON dhtc.DeviceHeaderId = dht.DeviceHeaderId
                        LEFT OUTER JOIN [Trailer].[Coupling] as tc ON dhtc.CouplingId = tc.CouplingId ";

    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();
            var trailerId = reader["TrailerId"];
            sw.Stop();
            Debug.WriteLine(trailerId + "-" + sw.ElapsedMilliseconds);//10s - 8s -...
        }
    }
}

此代码需要40秒钟。经过一番搜索，我发现规则阅读器[“ TrailerId”]总共占用39秒，查询本身运行非常快！

卸下“ TC”。 “ TrailerId”的标头使其在0.6秒内运行，reader [“ TrailerId”]现在仅需0毫秒：

SELECT ..., tc.CouplingId, TrailerId,...

这是sqldatareader索引器代码中的错误吗？我无法理解为什么第二个版本比第一个版本运行得这么快。

最佳答案

尝试使“ TrailerId”列的索引不循环并在内部使用它； afaik它使这个数字在记录中寻找每个，就像

using (var reader = cmd.ExecuteReader())
{
    int idx = -1;
    while (reader.Read())
    {
        if (idx==-1) idx = reader.GetOrdinal("TrailerId");
        Stopwatch sw = new Stopwatch();
        sw.Start();
        var trailerId = reader[idx];
        sw.Stop();
        Debug.WriteLine(trailerId + "-" + sw.ElapsedMilliseconds);//10s - 8s -...
    }
}