How can i speed up my process? Delete records from the list?

Question

So I have a list that comes from a database on which I'm using linq to find items and here's how i do it:

public static List<myEntity> GetEntities()
{
    List<myEntity> result = new List<myEntity>();
    var entities = _data.AsEnumerable()
        .Where(ent => ent.Field<string>(Constants.MODEL_DESC).Split(';')[0] == Constants.MODEL_ENTITY);

    foreach (var entity in entities)
    {
        myEntitytemp = new myEntity();
        string[] zzzDESC = entity.Field<string>(Constants.MODEL_DESC).Split(';');
        temp.ID = Convert.ToInt16(zzzDESC[1]);
        temp.icon = zzzDESC[2].Replace(".bmp", "");
        temp.Translations = GetTranslation(Convert.ToInt16(zzzDESC[1]));
        temp.tableName = GetTableName(temp.ID);
        temp.attributes = GetAttributes(temp.ID);
        result.Add(temp);
    }
    return result;
}

So basically there are 3 columns in my table and 1 of them stores the useful data separated by ";". Inside this function i have GetTableName (returns a string) and GetAttributes (return a list of class Attribute) and GetTranslations() in which i'm using the same kind of logic that in this method GetEntities().

private static List<Translation> GetTranslation(int id)
{
    List<Translation> result = new List<Translation>();
    var translations = _data.AsEnumerable()
      .Where(trl => trl.Field<string>(Constants.MODEL_DESC).Split(';')[0] == Constants.MODEL_TRANSLATION)
      .Where(trl => trl.Field<string>(Constants.MODEL_DESC).Split(';')[2] == id.ToString());

    foreach(var trl in translations)
    {
        Translation temp = new Translation();
        string[] ZZZDesc = trl.Field<string>(Constants.MODEL_DESC).Split(';');
        temp.ID = Convert.ToInt16(ZZZDesc[1]);
        temp.ParentID = Convert.ToInt16(ZZZDesc[2]);
        temp.Language = (Language)Convert.ToInt16(ZZZDesc[3]);
        temp.ShortName = ZZZDesc[4];
        temp.LongName = ZZZDesc[5];
        temp.HelpID = ZZZDesc[6];
        temp.Description = ZZZDesc[7];
        result.Add(temp);
    }
    return result;
}

They all query the same data which in this case is _data. It's a DataTable. And i can already notice that the process is very long. Pretty sure it's because i have to query the same table over and over to find what i'm searching for. The table itself has around 8000 rows so it's not that much.

I thought i may delete the rows once i got my stuff, i don't know if that a good idea since i will have to find the rows i was working with so that's 1 more entrance in _data per function and then delete it. Or maybe do it with linq if it's possible? and do it at the same time as i'm constructing the var entities, var translations, and so on.

Or is it just my way of working with linq that's the problem?

Never store multiple informations in one column(f.e. separated by ;). That's terrible database design — Tim Schmelter, Jun 29 '17 at 07:35
@TimSchmelter Yes that's why i originally said "And i don't know why they did this" that you edited out. I'll ask for the change of this cause that's terrible but in case i won't be able to change it i will have to work with this. I didn't do that, another team worked on it before me and did this crap. :( — user5014677, Jun 29 '17 at 07:38
Well the get translation one you could speed up by not repeatedly splitting on ; .. you split trl in the first query, if you had kept it there, you could have just used it in the second one — BugFinder, Jun 29 '17 at 07:40
@TimSchmelter I can delete it, i'm just asking if that's a good idea to do it and if this will fasten the process, or is it just because my linq logic is bad? — user5014677, Jun 29 '17 at 07:41
@user5014677: Why you use a DataTable at all? Can't you use a `Dictionary` to lookup the already instantiated Translation instances via `Model`(to implement) instance(which needs ofc to override `Equals` and `GetHashCode`)? That would greatly increase performance. Then you don't need to search the whole datatable for id and model-type. — Tim Schmelter, Jun 29 '17 at 07:44
@TimSchmelter Wow i didn't understand anything. Also i don't only have Translation and Entity. I have other functions that pretty much do the same thing, but because the data isn't stored the same way and i have to create other classes i can't create one single function our of it. — user5014677, Jun 29 '17 at 07:48
Completely off topic: "to fasten" does not mean "to speed up". It means "to close securely" (as in: I fastened my seatbelt). — Flater, Jun 29 '17 at 07:49
@user5014677: you dont have `Translation`? But you're returning a `List` so you have that class. You use that DataTable only to lookup the items, like the TableName, the Translation or the Attributes? — Tim Schmelter, Jun 29 '17 at 07:53
@TimSchmelter Yes, i use the datable to lookup the items. And each of the class don't have the same properties and the functions don't return all the same thing. Translation is a custom class i have created, it's not a generic class if a generic c# Translation class exists. — user5014677, Jun 29 '17 at 07:58
So use a `Dictionary` for every thing you need to lookup, f.e. a `TableNameDictionary` where the key is an `int`(the ID) and the value is a string(TableName). Do the same for translation and attributes. Side-note: stop repeating everything like `Convert.ToInt16(zzzDESC[1])`. Use variables. — Tim Schmelter, Jun 29 '17 at 08:08
@TimSchmelter Ok after some reading i did a dictionnary so now i have an unique ID to access all the data and then it's linked to a IEnumerable that it's the string with the ";" separation. I can't as yo usaid, id linked to a tableName (for example) because my id's in the database are number and the table name is inside a string with all the other poperties separated by ";". Performance look the same to me although accessing it is easier. — user5014677, Jun 29 '17 at 09:56

score 0 · Answer 1 · edited Nov 16 '21 at 16:19

0

IEnumerable uses lazy loading. Put it in a list at the first call instead and it may quicken the process.

If you are reading the table a Lot, you might want to have it in cache.

edited Nov 16 '21 at 16:19

marc_s

732,580
175
1,330
1,459

answered Jun 29 '17 at 10:10

Kautuk

1
1

https://stackoverflow.com/questions/3628425/ienumerable-vs-list-what-to-use-how-do-they-work Seems more convincing to me to use IEnumerable over List since i'm linq it – user5014677 Jun 29 '17 at 10:16
Can I ask if the results are already in the datatable memory when you apply the linq queries or are you using lazy loading? Also, how much time are we talking about here for 8000 rows? – Kautuk Jun 29 '17 at 10:34
I changed it now and i use a dictionnary to store the data coming from the database as someone suggested in the comments. And the time is somewhere around 3s – user5014677 Jun 29 '17 at 10:39

How can i speed up my process? Delete records from the list?

1 Answers1