Database: Refactor MP1 database for multi seat usage (6 Viewers)

[doskabouter]

Wouldn't the simplest way to deal with this be for you, at some point, to test the app for those scenarios - to confirm, or otherwise what you are worrying about.

I'm not understanding the issue because I need to see a concrete example of what you are describing; as I've never come across anything like it. Then I'll understand how to deal with the issue. There is always a solution that works well for everyone. I just need to get my head around all of the possibilities

The same goes for stacking. I have never used stacking so I don't understand how it works and why you need it. The code still does what it always did but it would be helpful for me to understand stacking because I suspect it is not being handled as efficiently as it should be.

I can ofcourse only test on my system and I suspect with my not overly large databases and demands and fast enough hardware it wouldn't be a problem.
@ajs: do you remember that issue I'm referring to?
Edit: this was the one I think: https://forum.team-mediaportal.com/threads/5061-music-database-doesnt-show-all-data.140324/

 

[Anthony Vaughan]

So I can imagine that there could be some systems (slow spinning rust f.e.) where this is could be slow...

When you are performing an iterative process, you should encase the iteration with the using statement.

I'm sorry if I gave the impression that each access should have it's own connection. That's not what I meant at all.

What I am saying is that you should not connect at the beginning of an application and leave the connection open until you log off or close the application .

 

[Anthony Vaughan]

Interesting discussion. I don't know what the right answer is, but I just wanted to mention that not everyone uses a threshhold of 80% or 90% to denote "watched". In particular, I use this property to identify the recordings that I have started watching, not the recordings that I have finished watching. So I have this threshhold set to 5%.

I've set the percentage threshold to 1%. I always want to know how far I am through a video. I guess 1% is useful to avoid highlighting videos you've just opened to have quick look

 

[Anthony Vaughan]

Just a quick point about iterations. When using Entity Framework and LINQ you commit changes by calling SaveChanges() on the connection handle.
It makes a dramatic difference if you put the SaveChanges inside an iterative loop, after each update, as opposed to placing it after the loop has finished but before closing the using scope. The difference is astonishing - by factors of over a hundred, I suspect.

using (var sql = new SQLite.NET.SQLiteClient(@"D:\klad\sqlitetest\sqlitetest\PictureDatabaseV3_1.db3"))
{
for (int i = 0; i < 1000; i++)
{
var res = sql.Execute("update picturedata set blah = 1 where idpicture=3856");
SaveChanges(); //bad
}
SaveChanges(); // good
}

 

[doskabouter]

Just a quick point about iterations. When using Entity Framework and LINQ you commit changes by calling SaveChanges() on the connection handle.
It makes a dramatic difference if you put the SaveChanges inside an iterative loop, after each update, as opposed to placing it after the loop has finished but before closing the using scope. The difference is astonishing - by factors of over a hundred, I suspect.

using (var sql = new SQLite.NET.SQLiteClient(@"D:\klad\sqlitetest\sqlitetest\PictureDatabaseV3_1.db3"))
{
for (int i = 0; i < 1000; i++)
{
var res = sql.Execute("update picturedata set blah = 1 where idpicture=3856");
SaveChanges(); //bad
}
SaveChanges(); // good
}

I can imagine that full-heartedly
Persisting stuff in a database (in this case a flat file) is expensive, but sometimes (not in Mediaportal scenario I expect) needed so other users of the database have the most uptodate view of the data.

What needs to be done (either by examining code or waiting for complaints :imp: ) is make sure that all places where possible iterations can occurr are handled correctly. Don't know if there are any places where f.e. data is fetched lazily from the database to populate a listview (and only visible items are fetched) and then user repeatedly pressing pagedown...
In the end you cannot easily batch those to use a single connection for that.
But as I said... overthinking maybe

 

[Anthony Vaughan]

What needs to be done (either by examining code or waiting for complaints :imp: ) is make sure that all places where possible iterations can occurr are handled correctly. Don't know if there are any places where f.e. data is fetched lazily from the database to populate a listview (and only visible items are fetched) and then user repeatedly pressing pagedown...
In the end you cannot easily batch those to use a single connection for that.
But as I said... overthinking maybe

There aren't many places in MP where iterations come into play; but there are a few instances.

I think you'll get a full understanding when you see the code. As you can imagine, it's quite difficult to get this across using words when the code is much more explanatory .

 

[Anthony Vaughan]

I've edited the first posting in this thread to include an attachment of a zip file containing the refactor code. Run the batch file to install the upgrade then run MP1 as usual. This will only work with MP 1.29.

Please give it a go and let me know how it goes. Your existing data should not be overwritten as new versions of the two affected databases are created based on your existing databases. This release is only going to work for Configuration, MP1 and the built-in Fanart software. If the refactor proves to be popular, then we can start working on upgrading the extensions.

 

[Anthony Vaughan]

Persisting stuff in a database (in this case a flat file) is expensive, but sometimes (not in Mediaportal scenario I expect) needed so other users of the database have the most uptodate view of the data.

I understand that MP has been moving towards small footprint databases over the years, and away from the more expensive solutions like SQL Server, especially with MP2. Although SQLite cannot work for a normal data driven business environment, where you have to have an enterprise level database engine to handle multiple users, I think there are some environments, like MP, where SQLite can work.

I believe I've come up with a workable solution for MP for a multi-user setup, but only because MP only writes to the database for a fraction of the time it is in use. SQLite is okay for multiple reads and occasional writes, but I don't think it would be workable if the user time at the front-end and back-end were more equal. I've had to use some pretty unusual techniques to get this to work when a larger footprint database would be much easier to program for.

But SQLite is free and it's small, with virtually no overheads. So I think it was worth the effort. It makes it available to everyone.

 

[Anthony Vaughan]

But as I said... overthinking maybe

I have been having a think about our conversation on iterations. In MP, it doesn't really happen much. If you watch live TV, then there is no database activity, and when you watch a movie, there is no database activity for the duration of watching. So, there are long periods of database inactivity.

The opposite is true for Configuration, when it comes to scanning. There are some major bottlenecks, especially when getting and storing actors for movies and generating actorlinkmovie records. If we were using SQL Server, this wouldn't matter - we would simply do things in the most efficient way and allow the database engine to handle the asynchronous processing. But with SQLite things are different. We have to allow space for MP user clients to do their occasional database activity. This is something I have been aware of and have been thinking about how best to handle things. My thought is to warn the user, about to do scan, that what they are doing may interfere with other household users and give them the choice whether to take the fastest option (which will lock other users out) or the less efficient option or do the scan when no-one else is using MP. Obviously, the wording will be important.

I have a methodology for handling batch processing that goes something like this (in pseudo code):

if using batch mode
{
do stuff
}
else
{
using ()
{
do stuff
}
}

The calling code either sets batch mode on and does an explicit connect, disconnecting at the end of the batch processing and setting batch mode off, or does the call with batch mode off.

This is the normal way I write the database accessing code. I didn't do it in MP because it is interactive for most of the time. But I could put this conditioning in when it is beneficial, like during a scan.

It's always going to be a trade-off between performance and co-operation, getting the users to understand they have to work together, with SQLite

I'll have a good look at this. It's something I've dealt with many times before; my version of MPSync, for example. It was to get away from having to synchronize that I embarked on this project in the first place because synchronizing data when it's locked most of the time is problematic, to say the least.

 

[ajs]

do you remember that issue I'm referring to?

To be honest, I don't remember. Or rather, my memory is that we solved it, and I forgot about it. Or did we?