问题描述
我读了一个sqlite数据库(数据库大小3MB左右,所以信息不多,每张表大概有1、2000行)并从中提取信息,然后我把这些信息添加到一个新的数据库.整个操作大约需要 40 秒.
I read a sqlite database (The size of the database is about 3 MB, So there is not much information, each table is about 1 or 2 thousand rows) and extract information from it, Then I add this information to a new database.The whole operation takes about 40 seconds.
我怎样才能减少这个时间并尽快完成操作?(任务、并行、异步……)
How can I reduce this time and get the operation done as quickly as possible? (Task, Parallel, async,...)
我目前正在使用此代码:
I am currently using this code:
await Task.Run(async () =>
{
var pkgs = new ManifestTable();
var mydb = new dbContext();
await mydb.Database.EnsureDeletedAsync();
await mydb.Database.EnsureCreatedAsync();
using (var msixDB = new MSIXContext())
{
foreach (var item in await msixDB.IdsMSIXTable.ToListAsync())
{
var rowId = item.rowid;
var manifests = await msixDB.Set<ManifestMSIXTable>().Where((e) => e.id == rowId).ToListAsync();
foreach (var manifest in manifests)
{
pkgs = new ManifestTable();
pkgs.PackageId = item.id;
var productMap = await msixDB.ProductCodesMapMSIXTable.FirstOrDefaultAsync((e) => e.manifest == manifest.rowid);
if (productMap != null)
{
var prdCode = await msixDB.ProductCodesMSIXTable.FirstOrDefaultAsync((e) => e.rowid == productMap.productcode);
if (prdCode != null)
{
pkgs.ProductCode = prdCode.productcode;
}
}
var publisherMap = await msixDB.Set<PublishersMapMSIXTable>().FirstOrDefaultAsync((e) => e.manifest == manifest.rowid);
if (publisherMap != null)
{
var publisher = await msixDB.PublishersMSIXTable.FirstOrDefaultAsync((e) => e.rowid == publisherMap.norm_publisher);
if (publisher != null)
{
pkgs.Publisher = publisher.norm_publisher;
}
}
var pathPart = manifest.pathpart;
var yml = await msixDB.PathPartsMSIXTable.FirstOrDefaultAsync((e) => e.rowid == pathPart);
if (yml != null)
{
pkgs.YamlName = yml.pathpart;
}
var version = await msixDB.VersionsMSIXTable.FirstOrDefaultAsync((e) => e.rowid == manifest.version);
if (version != null)
{
pkgs.Version = version.version;
}
await mydb.ManifestTable.AddAsync(pkgs);
}
}
await mydb.SaveChangesAsync();
}
});
推荐答案
将数据库视为对象存储是有史以来最糟糕的想法.您必须尽可能减少数据库往返次数.在您的情况下 - 只需一个请求.如果您不知道哪个部分慢,也不要玩 Task.Run、Parallel 等.在您的情况下 - 数据库往返.
Treating database as object storage is worst idea ever. You have to reduce database roundtrips as it is possible. In your case - by just one request. Also do not play with Task.Run, Parallel, etc. if you do not know which part is slow. In your case - database roundtrips.
var mydb = new dbContext();
await mydb.Database.EnsureDeletedAsync();
await mydb.Database.EnsureCreatedAsync();
using (var msixDB = new MSIXContext())
{
var query =
from item in msixDB.IdsMSIXTable
from manifest in msixDB.Set<ManifestMSIXTable>().Where(e => e.id == item.rowId)
from productMap in msixDB.ProductCodesMapMSIXTable.Where(e => e.manifest == manifest.rowid).Take(1).DefaultIfEmpty()
from prdCode in msixDB.ProductCodesMSIXTable.Where(e => e.rowid == productMap.productcode).Take(1).DefaultIfEmpty();
from publisherMap in msixDB.Set<PublishersMapMSIXTable>().Where(e => e.manifest == manifest.rowid).Take(1).DefaultIfEmpty()
from publisher in msixDB.PublishersMSIXTable.Where(e => e.rowid == publisherMap.norm_publisher).Take(1).DefaultIfEmpty()
from yml in msixDB.PathPartsMSIXTable.Where(e => e.rowid == manifest.pathpart).Take(1).DefaultIfEmpty()
from version in msixDB.VersionsMSIXTable.Where(e => e.rowid == manifest.version).Take(1).DefaultIfEmpty()
select new ManifestTable
{
PackageId = item.id,
ProductCode = prdCode.productcode,
Publisher = publisher.norm_publisher,
YamlName = yml.pathpart,
Version = version.version
};
mydb.ManifestTable.AddRange(await query.ToListAsync());
await mydb.SaveChangesAsync();
}
这篇关于改进 EFCore Query 以实现快速操作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!