dimanche 27 septembre 2015

How can I vastly improve the perfomance in loading multiple interlinked things from Database?

So, I think there MUST be a better way of doing what I am Doing...

I currently have 1 class, called "Hero". This contains 19 properties (subject to change, of course). It represents a database table I have of information.

Then, I have another Database table for comments on these hero's. One of the properties on each Hero is an ObservableCollection, and comment just contains a few basic properties.

I plan in the future as I build this up to have other relating tables attached also.

In the ViewModel, I have an ObservableCollection which I initialise with each hero from my SQLite DB.

When the program launches, I go through the following:

First, I query the Heroes Table, and pull out all the heroes into an ObservableCollection in the VM. Example:

DataTable dt = db.GetDataTable("SELECT * FROM Heroes");
foreach (DataRow Row in dt.Rows)
{
    Heroes.Add(new Hero(
        HID: Convert.ToInt32(Row["ID"]),
        HName: Row["Name"].ToString(),
        ETC.....
        ));
}

Then, I do the exact same for the Heroes_Comments table - I pull out everything from there, and store it in an ObservableCollection. Same way as above, only query is "SELECT * FROM Heroes_Comments", and different fields used ETC ofc.

Finally, I run a foreach on each comment in the ObservableCollection, and add it to the Hero Collection. As so:

foreach (Comment c in HeroesDBComments)
{
    HeroesDBHeroes.Single(h => h.ID == c.Foreign).Comments.Add(c);
}

It all works, and does its job etc... However with not that much test data, it does seem to take a fair while to do everything and I feel that as I add more things to this it will bog down completely and be worthless.

What can I do to improve this? I would rather get it right now, than have to improve and fix it all later on. :)

(FYI - Test data is currently looking at about 10 Hero's and 5 Comments, all 5 comments on the same Hero. Each Hero does have 2 images stored as a BLOB... so thats not the smallest amount of data ever but I feel as though the foreach on the comments is bad when it comes to adding more linked tables and doing the same with those... I just dont know anyhting better?)

Thought: Are ObservableCollections slower and there is a better alternative perhaps?

Aucun commentaire:

Enregistrer un commentaire