SqlDataReader Indexer Performance

Question

I have a code like this:

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

This code takes 40 seconds. After some searching, I found out that the reader ["TrailerId"] rule takes 39 seconds, the request itself is very fast!

Delete "TC". the header from "TrailerId" will start it in 0.6 s, the reader ["TrailerId"] now only accepts 0ms:

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

Is this an error in the sqldatareader index code? I can’t understand why the second version is much faster than the first.

Answer 1

try to get the index of the "TrailerId" column outside the loop and use it inside; afaik he makes this number look for everyone on the record, something like

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

Answer 2

Use an ordinal index and do not declare a variable every time.
Use a method specific to the data type.
Assuming trailerID is Int32 and position 22.

 Stopwatch sw = new Stopwatch(); Int32 trailerID; while (reader.Read()) { sw.Start(); trailerId = reader.GetInt(22); sw.Stop(); Debug.WriteLine(trailerId + "-" + sw.ElapsedMilliseconds);//10s - 8s -... } 

WITH
var trailerId = reader ["TrailerId"];
It should find the TrailerId and assign the correct data type for each loop in var.

I don't know why TC made a difference.
Most likely, this has something to do with the search, and then the schema for assigning var.