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

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.

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.

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.

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

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

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

Code (text):

    public IList<TuningDetail> ReferringTuningDetail()
    {
      //select * from 'foreigntable'
      SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof (TuningDetail));

      // where foreigntable.foreignkey = ourprimarykey
      sb.AddConstraint(Operator.Equals, "idChannel", idChannel);

      // passing true indicates that we'd like a list of elements, i.e. that no primary key
      // constraints from the type being retrieved should be added to the statement
      SqlStatement stmt = sb.GetStatement(true);

      // execute the statement/query and create a collection of User instances from the result set
      return ObjectFactory.GetCollection<TuningDetail>(stmt.Execute());

      // TODO In the end, a GentleList should be returned instead of an arraylist
      //return new GentleList( typeof(TuningDetail), this );
    }

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...

Right, understood...

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

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?