什么更快?结构数组或数据表

我正在使用linqtosql处理来自sql server的数据,将其转储到iSeries服务器以进行进一步处理。 More details on that here .

我的问题是,处理这350行数据大约需要1.25分钟。我仍在试图破译来自sql server分析器的结果,但仍有大量的查询正在运行。以下是关于我正在做什么的更多细节:

using (CarteGraphDataDataContext db = new CarteGraphDataDataContext())
{
    var vehicles = from a in db.EquipmentMainGenerals
                   join b in db.EquipmentMainConditions on a.wdEquipmentMainGeneralOID equals b.wdEquipmentMainGeneralOID
                   where b.Retired == null
                   orderby a.VehicleId
                   select a;

    et = new EquipmentTable[vehicles.Count()];

    foreach (var vehicle in vehicles)
    {
       // Move data to the array

       // Rates
       GetVehcileRates(vehicle.wdEquipmentMainGeneralOID);

       // Build the costs accumulators
       GetPartsAndOilCosts(vehicle.VehicleId);
       GetAccidentAndOutRepairCosts(vehicle.wdEquipmentMainGeneralOID);

       // Last Month's Accumulators
       et[i].lastMonthActualGasOil = GetFuel(vehicle.wdEquipmentMainGeneralOID) + Convert.ToDecimal(oilCost);
       et[i].lastMonthActualParts = Convert.ToDecimal(partsCost);
       et[i].lastMonthActualLabor = GetLabor(vehicle.VehicleId);
       et[i].lastMonthActualOutRepairs = Convert.ToDecimal(outRepairCosts);
       et[i].lastMonthActualAccidentCosts = Convert.ToDecimal(accidentCosts);

       // Move more data to the array

       i++;
   }
}

get方法看起来都类似于:

private void GetPartsAndOilCosts(string vehicleKey)
{
   oilCost = 0;
   partsCost = 0;

   using (CarteGraphDataDataContext db = new CarteGraphDataDataContext())
   {
      try
      {
         var costs = from a in db.WorkOrders
                     join b in db.MaterialLogs on a.WorkOrderId equals b.WorkOrder
                     join c in db.Materials on b.wdMaterialMainGeneralOID equals c.wdMaterialMainGeneralOID
                     where (monthBeginDate.Date <= a.WOClosedDate && a.WOClosedDate <= monthEndDate.Date) && a.EquipmentID == vehicleKey
                     group b by c.Fuel into d
                     select new
                            {
                                isFuel = d.Key,
                                totalCost = d.Sum(b => b.Cost)
                            };

          foreach (var cost in costs)
          {
             if (cost.isFuel == 1)
             {
                oilCost = (double)cost.totalCost * (1 + OVERHEAD_RATE);
             }
             else
             {
                partsCost = (double)cost.totalCost * (1 + OVERHEAD_RATE);
             }
          }
       }
       catch (InvalidOperationException e)
       {
          oilCost = 0;
          partsCost = 0;
       }
    }

    return;
 }

我的想法是减少对数据库的查询数量,这样可以加快处理速度。如果linq为每条记录做了一个select,那么我可能需要首先将每条记录加载到内存中。

我仍然认为自己是一个C和OOP的初学者(我主要在iSeries上做RPG编程)。所以我猜我在做蠢事。你能帮我纠正我的愚蠢吗(至少在这个问题上)?

更新: 我想我会回来告诉你我发现了什么。看起来数据库设计得很差。无论linq在后台生成什么,它都是非常低效的代码。我不是说LINQ不好,只是对这个数据库不好。我转换成了一个快速拼凑的.xsd设置,处理时间从1.25分钟变为15秒。一旦我做了一个适当的重新设计,我只能猜测我会剃掉更多的几秒钟。谢谢大家的评论。改天我会在一个更好的数据库里再试试linq。