private static void clearDbCache(DSMContext db)
{
string sql = @"CHECKPOINT;
DBCC DROPCLEANBUFFERS;";
db.Database.ExecuteSqlCommand(sql);
}
private static SensorInfo getRandomSensor(DSMContext db, ProjectInfo project, int insertSensorCnt)
{
Random r = new Random();
int index = r.Next(insertSensorCnt);
string name = PrefixName + index.ToString("d5");
var sensor = db.SensorInfo.First(s => s.ProjectId == project.Id && s.SensorCode == name);
return(sensor);
}
private static void deleteAllValues(DSMContext dbcontext)
{
string sqlDeleteStatement =
@"alter table [dbo].[SensorDataOrigin] nocheck CONSTRAINT ALL;
TRUNCATE TABLE[dbo].[SensorDataOrigin];
delete from[dbo].[SensorInfo];
alter table [dbo].[SensorDataOrigin] check CONSTRAINT ALL;";
dbcontext.Database.ExecuteSqlCommand(sqlDeleteStatement);
}
private static ProjectInfo getFirstProject(DSMContext dbcontext)
{
var item = dbcontext.ProjectInfo.FirstOrDefault();
if (item == null)
{
item = new ProjectInfo();
item.Id = Guid.NewGuid();
item.Name = "测试工程。";
dbcontext.Add(item);
dbcontext.SaveChanges();
}
return(item);
}
private static List <Guid> getRandom20SensorIdList(DSMContext db, ProjectInfo project, int insertSensorCnt)
{
List <string> codeList = new List <string>();
for (int i = 0; i < 20; i++)
{
Random r = new Random();
int index = r.Next(insertSensorCnt);
string name = PrefixName + index.ToString("d5");
codeList.Add(name);
}
var query = from i in db.SensorInfo
where i.ProjectId == project.Id && codeList.Contains(i.SensorCode)
orderby i.Id
select i.Id;
var result = query.ToList();
return(result);
}
private static long testQuerySingle(DSMContext db, ProjectInfo project, int insertSensorCnt)
{
//clearDbCache(db);
SensorInfo sensor = getRandomSensor(db, project, insertSensorCnt);
var query = from i in db.SensorDataOrigin
where i.SensorId == sensor.Id && i.MeaTime > StartDate.AddYears(2) && i.MeaTime < StartDate.AddYears(2).AddMonths(3)
select i;
Stopwatch watch = new Stopwatch();
watch.Start();
var result = query.ToArray();
watch.Stop();
Console.WriteLine(string.Format("随机查询并返回单个测点半年数据，返回数据{0}条, 耗时 {1:N0} ms。sensor code: {2}",
result.Length, watch.ElapsedMilliseconds, sensor.SensorCode));
return(watch.ElapsedMilliseconds);
}
private static long testQueryMultiply(DSMContext db, ProjectInfo project, int insertSensorCnt)
{
//clearDbCache(db);
List <Guid> sensorIdList = getRandom20SensorIdList(db, project, insertSensorCnt);
var query = from i in db.SensorDataOrigin
where sensorIdList.Contains(i.SensorId) && i.MeaTime > StartDate.AddYears(2) && i.MeaTime < StartDate.AddYears(2).AddDays(15)
select i;
Stopwatch watch = new Stopwatch();
watch.Start();
var result = query.ToArray();
watch.Stop();
Console.WriteLine(string.Format("随机查询并返回20测点多个数据，返回数据{0}条, 耗时 {1:N0} ms。",
result.Length, watch.ElapsedMilliseconds));
return(watch.ElapsedMilliseconds);
}
public CheckListGroupMasterDAL(DSMContext _db)
{
db = _db;
}
public LineNumberMasterDAL(DSMContext _db, IOptions <AppSettings> _appSettings)
{
db = _db;
appSettings = _appSettings.Value;
}
public NotificationDAL(DSMContext _db)
{
db = _db;
}
public RoleDAL(DSMContext _db)
{
db = _db;
}
public TargetOverAllDAL(DSMContext _db)
{
db = _db;
}
public GradeMasterDAL(DSMContext _db)
{
db = _db;
}
public ColorMasterDAL(DSMContext _db)
{
db = _db;
}
public CheckListTypeMasterDAL(DSMContext _db)
{
db = _db;
}
public CheckListJobActivityMasterDAL(DSMContext _db)
{
db = _db;
}
public CheckListLOTOTOMasterDAL(DSMContext _db)
{
db = _db;
}
static void Main(string[] args)
{
Console.WriteLine("是否删除所有数据，然后重新插入数据？yes/no");
DSMContext dbcontext = new DSMContext();
int cnt = dbcontext.SensorInfo.Count();
var key = Console.ReadLine();
Console.WriteLine();
if (string.Equals(key, "yes", StringComparison.OrdinalIgnoreCase))
{
deleteAllValues(dbcontext);
cnt = dbcontext.SensorInfo.Count();
if (cnt > 0)
{
Console.WriteLine("删除数据失败");
prepareQuit();
return;
}
}
int insertSensorCnt = 9899;
int insertDataCntPerSensor = 87600;
if (cnt == 0)
{
Stopwatch stop = new Stopwatch();
stop.Start();
seedSensor_Data(insertSensorCnt, insertDataCntPerSensor);
stop.Stop();
Console.WriteLine(string.Format("插入 {0:N0} 条数据，用时 {1:N0} 毫秒。",
(long)insertSensorCnt * insertDataCntPerSensor, stop.ElapsedMilliseconds));
}
cnt = dbcontext.SensorInfo.Count();
Console.WriteLine("测点数量: " + cnt);
ProjectInfo project = getFirstProject(dbcontext);
ConsoleKeyInfo input;
const int testLoopCnt = 100;
Console.WriteLine(string.Format("单测点按时间区分查询测试：{0}次", testLoopCnt));
int cntLoop = 0;
long cntTime = 0;
for (int i = 0; i < testLoopCnt; i++)
{
cntTime += testQuerySingle(dbcontext, project, insertSensorCnt);
cntLoop++;
}
Console.WriteLine(string.Format("平均耗时：{0} ms。", cntTime / cntLoop));
cntLoop = 0;
cntTime = 0;
Console.WriteLine(string.Format("20测点按时间区分查询测试：{0}次", testLoopCnt));
for (int i = 0; i < testLoopCnt; i++)
{
cntTime += testQueryMultiply(dbcontext, project, insertSensorCnt);
cntLoop++;
}
Console.WriteLine(string.Format("平均耗时：{0} ms。", cntTime / cntLoop));
prepareQuit();
}
public CheckListJobAdvanceMasterDAL(DSMContext _db)
{
db = _db;
}
public AssetDAL(DSMContext _db, IOptions <AppSettings> _appSettings)
{
db = _db;
appSettings = _appSettings.Value;
}
public DepartmentDAL(DSMContext _db)
{
db = _db;
}
private static void seedSensor_Data(int sensorCnt, int dataCntPerSersor)
{
DSMContext dbcontext = new DSMContext();
dbcontext.ChangeTracker.AutoDetectChangesEnabled = false;
Console.WriteLine("cleared all data in database");
var bulkOption = new BulkConfig
{
PreserveInsertOrder = true,
SetOutputIdentity = false,
BatchSize = dataCntPerSersor
};
//
//DataTable sensorInfoTable = new DataTable();
//sensorInfoTable.Columns.Add("Id", typeof(Guid));
//sensorInfoTable.Columns.Add("ProjectId", typeof(Guid));
//sensorInfoTable.Columns.Add("SensorCode", typeof(string));
ProjectInfo project = getFirstProject(dbcontext);
List <SensorInfo> sensorList = new List <SensorInfo>(sensorCnt);
for (int i = 0; i < sensorCnt; i++)
{
//Console.WriteLine(string.Format("inserting No. {0} Sensor info ", i + 1));
string sensorCode = PrefixName + i.ToString("d5");
SensorInfo sen = new SensorInfo();
sen.Id = Guid.NewGuid();
sen.SensorCode = sensorCode;
sen.ProjectId = project.Id;
sensorList.Add(sen);
}
Stopwatch stop = new Stopwatch();
stop.Start();
dbcontext.BulkInsert(sensorList);
stop.Stop();
Console.WriteLine(string.Format("insert all sersor info Elapsed Milliseconds :{0} ", stop.ElapsedMilliseconds));
var orderEnum = sensorList.OrderBy(s => s.Id);
List <SensorDataOrigin> dataList = new List <SensorDataOrigin>(dataCntPerSersor);
int index = 0;
foreach (var senItem in orderEnum)
{
dataList.Clear();
for (int j = 0; j < dataCntPerSersor; j++)
{
var item = new SensorDataOrigin();
item.SensorId = senItem.Id;
item.MeaTime = StartDate.AddHours(0.5 * j);
item.MeaValue1 = j;
item.MeaValue2 = j;
item.MeaValue3 = j;
item.ResValue1 = j;
item.ResValue2 = j;
item.ResValue3 = j;
dataList.Add(item);
}
Console.Write(string.Format("inserting data of {0} , order: {1:d5},", senItem.SensorCode, index));
stop = new Stopwatch();
stop.Start();
dbcontext = new DSMContext();
dbcontext.ChangeTracker.AutoDetectChangesEnabled = false;
dbcontext.BulkInsert(dataList, bulkOption);
stop.Stop();
Console.WriteLine(string.Format(" Elapsed :{0} ms", stop.ElapsedMilliseconds));
index++;
}
//避免尾巴
}
public CheckListSubCategoryMasterDAL(DSMContext _db)
{
db = _db;
}
public UserManagementDAL(DSMContext _db, IOptions <AppSettings> _appSettings)
{
db = _db;
appSettings = _appSettings.Value;
}
public DocumentMasterDAL(DSMContext _db, IOptions <AppSettings> _appSettings)
{
db = _db;
appSettings = _appSettings.Value;
}
public DesignationDAL(DSMContext _db)
{
db = _db;
}
public CheckListJobOperatorDAL(DSMContext _db)
{
db = _db;
}
public CheckListJobAssignedResourceMasterDAL(DSMContext _db)
{
db = _db;
}
public HelpContentDAL(DSMContext _db)
{
db = _db;
}
