[Evaluate] - optimize listview controls filling in TVSetup - Channel list for groups

Discussion in 'Submit: code patches (MediaPortal/TV-Server/etc.)' started by Vasilich, July 1, 2011.

  1. Vasilich
    • Team MediaPortal

    Vasilich Test Group

    Joined:
    August 30, 2009
    Messages:
    3,389
    Likes Received:
    608
    Gender:
    Male
    Occupation:
    Developer
    Location:
    Germany, Mayence
    Ratings:
    +1,172 / 2
    Home Country:
    Russian Federation Russian Federation
    Show System Specs
    1. this patch optimizes listview filling with data for tvsetup - channel list for groups (both radio and TV). The optimization is done the same way as in ChannelListViewHandler.cs.
    2. some lines were just moved to another location in file to get consistence within 2 files: ChannelsInGroupControl.cs and ChannelsInRadioGroupControl.cs - they do same things for TV and radio channels, and code is REALLY the same, so now it is easier to compare them
    3. filter text in log entry "Filter listview for " is quoted now.



    generally - this patch doesn't eliminate separate SQL queries for every channel listed, what takes significantly more time that listview filling. But with this optimization applied I won 9 seconds on my tv channel list with 4500 channels and got 37 seconds instead of 46 when switching to "All channels" group.
     

    Attached Files:

    Last edited by a moderator: March 15, 2013
    • Like Like x 4
  2. Google AdSense Guest Advertisement



    to hide all adverts.
  3. mm1352000
    • Team MediaPortal

    mm1352000 Development Group

    Joined:
    September 1, 2008
    Messages:
    21,506
    Likes Received:
    4,716
    Ratings:
    +8,180 / 17
    Home Country:
    New Zealand New Zealand
    Hi Vasilich

    Patch looks good to me. I don't have 4000+ channels to test with so my time gains are *much* lower than yours.

    Testing method:
    0. Compile 2nd SetupTv.exe with the patch included.
    1. Open TV Server configuration with the old exe.
    2. Go to the TV Channels section.
    3. Record the time in the logs.
    4. Close TV Server configuration.
    5. Open TV Server configuration with the new exe.
    6. Go to the TV Channels section.
    7. Record the time in the logs.
    8. Close TV Server configuration.

    (without patch)
    (total: 1.312500 s)

    (with patch)
    (total: 1.296875 s)

    Hopefully I did the test correctly?

    Only 0.015625 s difference for 260 channels. If the relationship between number of channels and loading time are linear then that would result in a gain of ~0.27043 s for 4500 channels... nothing like the 9 seconds that you gain. But then again, if you make the same assumption it should take me approximately 22.5 s to load a list of 4500 channels. That's significantly lower than the 46 seconds for you...

    mm

    PS: I'm using MySQL 5.1.30 on XP SP3.
     
  4. Vasilich
    • Team MediaPortal

    Vasilich Test Group

    Joined:
    August 30, 2009
    Messages:
    3,389
    Likes Received:
    608
    Gender:
    Male
    Occupation:
    Developer
    Location:
    Germany, Mayence
    Ratings:
    +1,172 / 2
    Home Country:
    Russian Federation Russian Federation
    Show System Specs
    well,
    means that you have listed all channels. my patch is for channel list in groups (as stated in title), so you should measure time when selecting "All channels" group (or any other group with significant amount of channels).
    There is no log message for channel list building in groups, so there is little chance that you can see the difference with 260 channels.

    I can post here my channel table if someone wants to test it and feel the difference.
     
  5. mm1352000
    • Team MediaPortal

    mm1352000 Development Group

    Joined:
    September 1, 2008
    Messages:
    21,506
    Likes Received:
    4,716
    Ratings:
    +8,180 / 17
    Home Country:
    New Zealand New Zealand
    Hi again Vasilich

    Well, I'm confused then. Is there not also a problem with listing all channels in the first tab? I can add some logging for the channel groups and see if it makes any difference, but like you say, 260 channels is not even close to 4500. Unfortunately I don't have a test machine otherwise I could happily load up your channel list for testing. I'll see how easy it would be to take a backup of my DB so that I can test your list...

    mm
     
  6. Vasilich
    • Team MediaPortal

    Vasilich Test Group

    Joined:
    August 30, 2009
    Messages:
    3,389
    Likes Received:
    608
    Gender:
    Male
    Occupation:
    Developer
    Location:
    Germany, Mayence
    Ratings:
    +1,172 / 2
    Home Country:
    Russian Federation Russian Federation
    Show System Specs
    mm, i wrote about SQL queries in my 1st post. I think the only option to speed things up for ALL tabs is to read all channels details in one query and then parse them from memory. But i just started to learn C# and MP internals, so it will take some time.
    Still, my patch improves things for group tabs - i even wrote small C# application filling listview with data in different ways, and AddRange was way faster than other ways (direct filling or switching off sorter before adding an item). I measured total filling time and count of sorter calls. If you are interested, i can also post this test app here
     
  7. mm1352000
    • Team MediaPortal

    mm1352000 Development Group

    Joined:
    September 1, 2008
    Messages:
    21,506
    Likes Received:
    4,716
    Ratings:
    +8,180 / 17
    Home Country:
    New Zealand New Zealand
    Hi again Vasilich

    The difficulty is that we use Gentle.NET to hide the details of the database implementation (MySQL, SQL Server), so we don't write the raw SQL. Of course it wouldn't make sense to query the database for each channel individually. However that seems to be the way that the Gentle.NET layer translates the query. I'm not sure what to do about it...

    mm
     
  8. Vasilich
    • Team MediaPortal

    Vasilich Test Group

    Joined:
    August 30, 2009
    Messages:
    3,389
    Likes Received:
    608
    Gender:
    Male
    Occupation:
    Developer
    Location:
    Germany, Mayence
    Ratings:
    +1,172 / 2
    Home Country:
    Russian Federation Russian Federation
    Show System Specs
    Code (Text):
    1.     public IList<TuningDetail> ReferringTuningDetail()
    2.     {
    3.       //select * from 'foreigntable'
    4.       SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof (TuningDetail));
    5.  
    6.       // where foreigntable.foreignkey = ourprimarykey
    7.       sb.AddConstraint(Operator.Equals, "idChannel", idChannel);
    8.  
    9.       // passing true indicates that we'd like a list of elements, i.e. that no primary key
    10.       // constraints from the type being retrieved should be added to the statement
    11.       SqlStatement stmt = sb.GetStatement(true);
    12.  
    13.       // execute the statement/query and create a collection of User instances from the result set
    14.       return ObjectFactory.GetCollection<TuningDetail>(stmt.Execute());
    15.  
    16.       // TODO In the end, a GentleList should be returned instead of an arraylist
    17.       //return new GentleList( typeof(TuningDetail), this );
    18.     }
    this code is really like "select * from TuningDetails where idChannel = :idChannel". there is anyway only one record can be returned. So we need to ask e.g. "select * from TuningDetails where isTV = true" once, put the result in a dictionary, and then ask dictionary when filling listviews - i believe dictionary search in memory is faster than thousands of sql queries...
     
    • Agree Agree x 1
  9. mm1352000
    • Team MediaPortal

    mm1352000 Development Group

    Joined:
    September 1, 2008
    Messages:
    21,506
    Likes Received:
    4,716
    Ratings:
    +8,180 / 17
    Home Country:
    New Zealand New Zealand
    Right, understood...
     
  10. mm1352000
    • Team MediaPortal

    mm1352000 Development Group

    Joined:
    September 1, 2008
    Messages:
    21,506
    Likes Received:
    4,716
    Ratings:
    +8,180 / 17
    Home Country:
    New Zealand New Zealand
    Hi again Vasilich

    Perhaps you could try the attached SetupTv.exe files. One version builds a dictionary as you suggested. The other one cuts the number of queries in half by removing an unneeded call to channel.ReferringTuningDetail(). The changes are only applied when viewing the TV channel groups.

    mm
     

    Attached Files:

  11. Vasilich
    • Team MediaPortal

    Vasilich Test Group

    Joined:
    August 30, 2009
    Messages:
    3,389
    Likes Received:
    608
    Gender:
    Male
    Occupation:
    Developer
    Location:
    Germany, Mayence
    Ratings:
    +1,172 / 2
    Home Country:
    Russian Federation Russian Federation
    Show System Specs
    thanks mm, i will test them and post results as soon as i come home.
    I assume these binaries were built from latest SVN?
    could you also post patches for these versions for me that i can understand your changes and be able to tune them up a bit if necessarily?
     
Loading...

Users Viewing Thread (Users: 0, Guests: 0)

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice
  • About The Project

    The vision of the MediaPortal project is to create a free open source media centre application, which supports all advanced media centre functions, and is accessible to all Windows users.

    In reaching this goal we are working every day to make sure our software is one of the best.

             

  • Support MediaPortal!

    The team works very hard to make sure the community is running the best HTPC-software. We give away MediaPortal for free but hosting and software is not for us.

    Care to support our work with a few bucks? We'd really appreciate it!