列表需要时间从数据库插入数据(List take time to insert data from database)

我有类列表哪个类有3个这样的属性。

public string attributeName { get; set; } public string strFormId { get; set; } public string strValue { get; set; }

我通过这样的列表将数据库数据添加到其中

List<myAttributeData> attributesData = new List<myAttributeData>(); var result = db.ExecuteQuery<myAttributeData>(query, new object[0]); // attributesData.Clear(); foreach (myAttributeData item in result.ToList()) { if (item.attributeName == "Province ") { var Loc = from d in db.tblLocations where d.LocationId == Convert.ToInt32(item.strValue) select new { d.LocationName }; foreach (var item1 in Loc.ToList()) { attributesData.Add(new myAttributeData() { attributeName = item.attributeName, strFormId = item.strFormId, strValue = item1.LocationName }); } }

问题是它花了这么多时间现在我有70万条记录进入我的数据库表，这需要超过半小时的任何关于这种情况的建议谢谢。我必须将我的数据添加到列表中，因为我需要它在需要时使用它可以让任何人给我解决方案来减少将数据添加到字符串中的时间。

I have list of class which class has 3 properties like this.

public string attributeName { get; set; } public string strFormId { get; set; } public string strValue { get; set; }

I am adding my database data into it through list like this

List<myAttributeData> attributesData = new List<myAttributeData>(); var result = db.ExecuteQuery<myAttributeData>(query, new object[0]); // attributesData.Clear(); foreach (myAttributeData item in result.ToList()) { if (item.attributeName == "Province ") { var Loc = from d in db.tblLocations where d.LocationId == Convert.ToInt32(item.strValue) select new { d.LocationName }; foreach (var item1 in Loc.ToList()) { attributesData.Add(new myAttributeData() { attributeName = item.attributeName, strFormId = item.strFormId, strValue = item1.LocationName }); } }

The problem is its taking so much time right now i have 70 thousands record into my database table which is take more than half hour any suggestion about this situation thanks. I have to add my data into list because I need it to use it back when it is needed can anybody give me solutions to cut the time of adding data into string.

最满意答案

一句话：缓存。

这段代码的问题在于您正在遍历70,000条记录，而对于每条记录，您将返回数据库，以读取额外信息。

foreach (myAttributeData item in result.ToList()) { if (item.attributeName == "Province ") { var Loc = from d in db.tblLocations where d.LocationId == Convert.ToInt32(item.strValue)

如果可以缓存位置列表（在调用foreach循环之前），您会发现代码过得很快

List<Location> cachedLocations = db.tblLocations.ToList();

..然后从那里设置你的Loc变量：

var Loc = from d in cachedLocations where d.LocationId == Convert.ToInt32(item.strValue)

始终尽可能减少回调数据库的数量。

祝你好运！

One word: caching.

The problem with this code is that you're iterating through your 70,000 records, and for each one, you're going back to the database, to read in extra information.

foreach (myAttributeData item in result.ToList()) { if (item.attributeName == "Province ") { var Loc = from d in db.tblLocations where d.LocationId == Convert.ToInt32(item.strValue)

You'll find your code flies if you can cache the list of locations (before calling your foreach loop)

List<Location> cachedLocations = db.tblLocations.ToList();

..and then set your Loc variable from there:

var Loc = from d in cachedLocations where d.LocationId == Convert.ToInt32(item.strValue)

Always keep the number of calls back to the database as low as possible.

Good luck!

更多推荐