提高在EF中为一对多关系插入数据的性能 [英] Improve performance of inserting data for one to many relationship in EF

问题描述

由于我的数据库具有特殊的设计结构，我在EF中的表现非常差。以下是相关关系：

---

---

我有以下数据模型：

---

  public class Sensor 
 {
 [Key] 
 public int Id {得到;组; } 
 
 [必需，MaxLength（64）] 
 public string Name {get;组; } 
 
 [必需，ForeignKey（Type）] 
 public int SensorTypeId {get;组; } 
 
 public virtual SensorType Type {get;组; } 
 
 public virtual ICollection< SensorSample> SensorSamples {get;组; 
} 
 
 public class SensorSample 
 {
 [Key] 
 public int Id {get;组; } 
 
 [必需，ForeignKey（Sensor）] 
 public int SensorId {get;组; } 
 
 public virtual Sensor Sensor {get;组; 
 
 [必需] 
 public DateTime SampleTime {get;组; 
 
 [必需] 
 public virtual ICollection< SampleData> SampleData {get;组; 
} 
 
 public class SampleData 
 {
 [Key] 
 public int Id {get;组; } 
 
 [必需，ForeignKey（DataType）] 
 public int SampleDataTypeId {get;组; } 
 
 public virtual SampleDataType DataType {get;组; } 
 
 [必需，ForeignKey（Unit）] 
 public int SampleUnitId {get;组; } 
 
 public virtual SampleUnit Unit {get;组; } 
 
 [必需，ForeignKey（Sample）] 
 public int SensorSampleId {get;组; } 
 
 public virtual SensorSample Sample {get;组; } 
 
 [MaxLength（128）] 
 public string Value {get;组; 
} 
  

---

因为code> SensorSample 可以有多个数据样本类型（即温度，压力等），一个 INSERT 必须查询现有样本，以使与正确的 SampleTime 相关联。这是使用以下代码完成的：

---

  SensorSample sample = null; 
 foreach（var d in input）
 {
 SampleData data = new SampleData（）; 
 data.SampleDataTypeId = dataTypeId; 
 data.SampleUnitId = unitId; 
 data.Value = d.Value; 
 
 //检查此传感器和时间戳的现有样本
 sample = SensorSamples.FirstOrDefault（s => s.SensorId == sensor.Id&& s.SampleTime == d.Timestamp） 
 if（sample == null）
 {
 //样本不存在，创建一个新的
 sample = new SensorSample（）; 
 sample.SampleTime = d.Timestamp; 
 sample.SensorId = sensor.Id; 
 sensor.SensorSamples.Add（sample）; 
} 
 //将数据添加到样本
 sample.SampleData.Add（data）; 
} 
  

---

我已经尝试优化通过批量插入样本数据（即一次记录1000个记录）。这样做有帮助，但即使在 SampleTime 字段中有一个索引，查询查询似乎需要更长的时间，因为添加了更多记录。

所以，我的问题是，如何改进将样本数据添加到数据库的设计和/或性能？是否有更好的数据库结构来处理一对多关系？如果我能在性能上获得适当的偏移量，我愿意对数据库设计做出一些妥协，但是我仍然需要处理与给定的 SampleTime 相关联的不同数据。

解决方案

以最大化测试数据的LOAD性能

  DONT在Debug模式下运行项目（EF为多个因素）
  

使用这些设置：

  Context.Configuration.LazyLoadingEnabled = false; 
 Context.Configuration.ProxyCreationEnabled = false; 
 Context.Configuration.AutoDetectChangesEnabled = false; 
 Context.Configuration.ValidateOnSaveEnabled = false;每100个条目或更少，丢弃上下文，
  

 使用（新上下文）
  

尝试

  Context.Set< TPoc​​o>（）。AddOrUpdate（poco）; 
  

而不是

 code> Context.Set< TPoc​​o>（）。firstorDefault（lamba）; 
 Context.Set< TPoc​​o>（）。Add（poco）; 
  

I am getting really poor performance in EF because of a particular design structure for my database. Here are the relevant relationships:

---

---

I have the following data model:

---

public class Sensor
{
    [Key]
    public int Id { get; set; }

    [Required, MaxLength(64)]
    public string Name { get; set; }

    [Required, ForeignKey("Type")]
    public int SensorTypeId { get; set; }

    public virtual SensorType Type { get; set; }

    public virtual ICollection<SensorSample> SensorSamples { get; set; }
}

public class SensorSample
{
    [Key]
    public int Id { get; set; }

    [Required, ForeignKey("Sensor")]
    public int SensorId { get; set; }

    public virtual Sensor Sensor { get; set; }

    [Required]
    public DateTime SampleTime { get; set; }

    [Required]
    public virtual ICollection<SampleData> SampleData { get; set; }
}

public class SampleData
{
    [Key]
    public int Id { get; set; }

    [Required, ForeignKey("DataType")]
    public int SampleDataTypeId { get; set; }

    public virtual SampleDataType DataType { get; set; }

    [Required, ForeignKey("Unit")]
    public int SampleUnitId { get; set; }

    public virtual SampleUnit Unit { get; set; }

    [Required, ForeignKey("Sample")]
    public int SensorSampleId { get; set; }

    public virtual SensorSample Sample { get; set; }

    [MaxLength(128)]
    public string Value { get; set; }
}

---

Because a SensorSample can have multiple data sample types (i.e. temperature, pressure, etc), an INSERT must query for existing samples to make the appropriate association with the correct SampleTime. This is done using the following code:

---

SensorSample sample = null;
foreach (var d in input)
{
    SampleData data = new SampleData();
    data.SampleDataTypeId = dataTypeId;
    data.SampleUnitId = unitId;
    data.Value = d.Value;

    // check for existing sample for this sensor and timestamp
    sample = SensorSamples.FirstOrDefault(s => s.SensorId == sensor.Id && s.SampleTime == d.Timestamp);
    if (sample == null)
    {
        // sample doesn't exist, create a new one
        sample = new SensorSample();
        sample.SampleTime = d.Timestamp;
        sample.SensorId = sensor.Id;
        sensor.SensorSamples.Add(sample);
    }
    // add the data to the sample
    sample.SampleData.Add(data);
}

---

I have tried optimizing the inserting of sample data by doing it in batches (i.e. 1000 records at a time). This does help, but even though there is an index on the SampleTime field, the lookup query seems to take longer as more records are added.

So, my question is, how do I improve the design and/or performance of adding sample data to the database? Is there a better database structure for handling the one-to-many relationship? I am willing to make some compromises on database design if I can get an appropriate offset in performance, but I still need to be able to handle different data associated with a given SampleTime.

解决方案

to maximize LOAD performance for test data

    DONT run project in Debug mode (multiple factor slower for EF)

use these settings:

    Context.Configuration.LazyLoadingEnabled = false;
    Context.Configuration.ProxyCreationEnabled = false;
    Context.Configuration.AutoDetectChangesEnabled = false;
    Context.Configuration.ValidateOnSaveEnabled = false;

every 100 entries or fewer, discard Context.

 Using( new context)

try

Context.Set<TPoco>().AddOrUpdate(poco);

Instead of

   Context.Set<TPoco>().firstorDefault(lamba);
   Context.Set<TPoco>().Add(poco);

这篇关于提高在EF中为一对多关系插入数据的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！