Virtual ListCtrl

Werner_F_Bruhin1 · May 30, 2003, 4:55pm

Trying to use a virtual listctrl to display data from a SQL table which will have somewhere around 15,000 to 25,000 rows.

I am not at all sure if I understand how the virtual listctrl works, my guess at the moment is that I would have to do something like:

- get a rowcount of my SQL query to set SetItemCount
- create a directory object to track what items I already retrieved from the db where the key is the item number
- in OnGetItemText I check this python directory and either get the next item from the db (and append to dir) or return the requested item data

Is this roughly how I should go about it or I am missing some magical function which will make live easier for me?

Any suggestions/hints, or even better some sample code would be greatly appreciated.

See you
Werner

Robin · May 30, 2003, 5:44pm

Werner F. Bruhin wrote:

Trying to use a virtual listctrl to display data from a SQL table which will have somewhere around 15,000 to 25,000 rows.

I am not at all sure if I understand how the virtual listctrl works, my guess at the moment is that I would have to do something like:

- get a rowcount of my SQL query to set SetItemCount
- create a directory object to track what items I already retrieved from the db where the key is the item number
- in OnGetItemText I check this python directory and either get the next item from the db (and append to dir) or return the requested item data

Is this roughly how I should go about it or I am missing some magical function which will make live easier for me?

You probably also want to catch the EVT_LIST_CACHE_HINT event and in the handler use event.GetCacheFrom() and event.GetCacheTo() to help you do the caching. That way you can prefetch several rows at once and the whole thing should be more efficient.

···

--
Robin Dunn
Software Craftsman
http://wxPython.org Java give you jitters? Relax with wxPython!

Werner_F_Bruhin1 · June 1, 2003, 5:59pm

Got this basically to work, however performance is not what I would like it to be. So, I would like to make use of the cache stuff, but frankly this is way over my head and I could not find much in the documentation (except what is in wxListEvent), nor on the Wiki pages. No problem setting up the EVT_LIST_CACHE_HINT, but then how do I determine to call GetCacheFrom or GetCacheTo?

Also when I was trying to figure out things I noted that OnGetItemText is called too often (in my opinion), which doesn't help performance. I added the following line into the demo in OnGetItemText and noted a few things.

self.log.WriteText('OnGetItemText: "%s", "%s"\n' % (item, col))

- that just moving the cursor over the list calls OnGetItemText (if I have not clicked within the listctrl yet). - when scrolling with page down it happens that OnGetItemText seems to jump backwards and start over again at some point. - when doing many page downs it gets to a point were OnGetItemText does not seem to get called anymore (e.g. the log text is no longer been written), however the ListCtrl looks fine.

Any pointers on where I should look for more information on the cache stuff and how to optimize the listctrl will be much appreciated.

See you
Werner

Robin Dunn wrote:

···

Werner F. Bruhin wrote:

Trying to use a virtual listctrl to display data from a SQL table which will have somewhere around 15,000 to 25,000 rows.

I am not at all sure if I understand how the virtual listctrl works, my guess at the moment is that I would have to do something like:

- get a rowcount of my SQL query to set SetItemCount
- create a directory object to track what items I already retrieved from the db where the key is the item number
- in OnGetItemText I check this python directory and either get the next item from the db (and append to dir) or return the requested item data

Is this roughly how I should go about it or I am missing some magical function which will make live easier for me?

You probably also want to catch the EVT_LIST_CACHE_HINT event and in the handler use event.GetCacheFrom() and event.GetCacheTo() to help you do the caching. That way you can prefetch several rows at once and the whole thing should be more efficient.

Robin · June 2, 2003, 9:39pm

Werner F. Bruhin wrote:

Got this basically to work, however performance is not what I would like it to be. So, I would like to make use of the cache stuff, but frankly this is way over my head and I could not find much in the documentation (except what is in wxListEvent), nor on the Wiki pages. No problem setting up the EVT_LIST_CACHE_HINT, but then how do I determine to call GetCacheFrom or GetCacheTo?

Those are methods of the wxListEvent object passed to your EVT_LIST_CACHE_HINT handler, so when it is called you can get the cachefrom and cachetto at that time and do whatever you need to cache those records.

Also when I was trying to figure out things I noted that OnGetItemText is called too often (in my opinion), which doesn't help performance. I added the following line into the demo in OnGetItemText and noted a few things.

self.log.WriteText('OnGetItemText: "%s", "%s"\n' % (item, col))

- that just moving the cursor over the list calls OnGetItemText (if I have not clicked within the listctrl yet). - when scrolling with page down it happens that OnGetItemText seems to jump backwards and start over again at some point. - when doing many page downs it gets to a point were OnGetItemText does not seem to get called anymore (e.g. the log text is no longer been written), however the ListCtrl looks fine.

It's called whenever the item needs to be repainted. (But on MSW it's called whenever micorosoft wants... <wink>)

···

--
Robin Dunn
Software Craftsman
http://wxPython.org Java give you jitters? Relax with wxPython!

Ilia_Kats · June 3, 2003, 6:50am

Robin Dunn wrote:

Werner F. Bruhin wrote:
> Got this basically to work, however performance is not what I would like
> it to be. So, I would like to make use of the cache stuff, but frankly
> this is way over my head and I could not find much in the documentation
> (except what is in wxListEvent), nor on the Wiki pages. No problem
> setting up the EVT_LIST_CACHE_HINT, but then how do I determine to call
> GetCacheFrom or GetCacheTo?

Those are methods of the wxListEvent object passed to your
EVT_LIST_CACHE_HINT handler, so when it is called you can get the
cachefrom and cachetto at that time and do whatever you need to cache
those records.

I have experiencing the same problem (I have about 15,000 rows). Robin, can
you provide us a small example that demonstrates the usage of
event.GetCacheFrom() and event.GetCacheTo() methods please?

Ilia Kats

Robin · June 3, 2003, 6:34am

Ilia Kats wrote:

Robin Dunn wrote:

Werner F. Bruhin wrote:

Got this basically to work, however performance is not what I would like
it to be. So, I would like to make use of the cache stuff, but frankly
this is way over my head and I could not find much in the documentation
(except what is in wxListEvent), nor on the Wiki pages. No problem
setting up the EVT_LIST_CACHE_HINT, but then how do I determine to call
GetCacheFrom or GetCacheTo?

Those are methods of the wxListEvent object passed to your
EVT_LIST_CACHE_HINT handler, so when it is called you can get the
cachefrom and cachetto at that time and do whatever you need to cache
those records.

I have experiencing the same problem (I have about 15,000 rows). Robin, can
you provide us a small example that demonstrates the usage of
event.GetCacheFrom() and event.GetCacheTo() methods please?

EVT_LIST_CACHE_HINT(self, -1, self.OnCacheHint)

     def OnCacheHint(self, evt):
         cacheFrom = evt.GetCacheFrom()
         cacheTo = evt.GetCacheTo()
         # At this point you would normally do whatever is needed to
         # put the data for the items from cacheFrom to cacheTo into a
         # local cache of some sort, perhaps just a dictionary, but if
         # your dataset is huge then you will want to use something
         # that has an upper bound on the number of items and removes
         # the least recently used items from the cache. Then in the
         # OnGetItem* methods you would just need to retrieve the data
         # from the cache object.

Anybody have any suggestions or samples for a class to use for the cache data structure? If so it would be nice to have it in the library.

···

--
Robin Dunn
Software Craftsman
http://wxPython.org Java give you jitters? Relax with wxPython!

Werner_F_Bruhin1 · June 3, 2003, 1:20pm

Robin Dunn wrote:

Ilia Kats wrote:

Robin Dunn wrote:

Werner F. Bruhin wrote:

Got this basically to work, however performance is not what I would like
it to be. So, I would like to make use of the cache stuff, but frankly
this is way over my head and I could not find much in the documentation
(except what is in wxListEvent), nor on the Wiki pages. No problem
setting up the EVT_LIST_CACHE_HINT, but then how do I determine to call
GetCacheFrom or GetCacheTo?

Those are methods of the wxListEvent object passed to your
EVT_LIST_CACHE_HINT handler, so when it is called you can get the
cachefrom and cachetto at that time and do whatever you need to cache
those records.

I have experiencing the same problem (I have about 15,000 rows). Robin, can
you provide us a small example that demonstrates the usage of
event.GetCacheFrom() and event.GetCacheTo() methods please?

        EVT_LIST_CACHE_HINT(self, -1, self.OnCacheHint)

    def OnCacheHint(self, evt):
        cacheFrom = evt.GetCacheFrom()
        cacheTo = evt.GetCacheTo()
        # At this point you would normally do whatever is needed to
        # put the data for the items from cacheFrom to cacheTo into a
        # local cache of some sort, perhaps just a dictionary, but if
        # your dataset is huge then you will want to use something
        # that has an upper bound on the number of items and removes
        # the least recently used items from the cache. Then in the
        # OnGetItem* methods you would just need to retrieve the data
        # from the cache object.

Anybody have any suggestions or samples for a class to use for the cache data structure? If so it would be nice to have it in the library.

Robin,

Your hint was enough to get me going, and I got it mostly working. Also I still get super flues calls to EVT_LIST_CACHE_HINT where I get the same values (or similar) for From and To as on the previous event.

Below is what I came up with, any suggestions on making this nicer, better are very welcome.

Note, that I am using ORM (Object Relational Membrane) for data access.

The one thing which is not yet working 100% is that OnGetItemText is once called BEFORE the cache event, would like to call OnCacheHint if the attribute cepageData does not exist - any hint on how I do this? Actually just noted that it is always called after the first OnGetItemText for a new range of items, which makes things somehow more complicated - but maybe someone else on the list has some good ideas on how to get around this.

Following the relevant code, again I am open for suggestions on how to improve this (even code style suggestions etc).

See you
Werner

initData - create a dictionary (cepageDataCache) with minimal data
getData - get whatever data I need for the listctrl columns and put it in a small dictionary
onCacheHint - initialize the small (cepageData) dictionary and use getData to fill it

    def initData(self, ds):
        """ds = ORM datasource
        Using ORM's execute so I can get just the two columns in question and
        fetchall to get a list back"""
        mycur = ds.execute('SELECT SYNONYMID, NAME FROM CEPAGE_SYN ORDER BY NAME')
        self.cepageDataCache = mycur.fetchall()
        self.SetItemCount(mycur.rowcount)

    def getData(self, item, pkey):
        """item = list item number
        pkey = primary key for item number
        """
        where = 'synonymid=%i' % pkey[0]
        row = self.ds.selectByClauses(beans.cepage_syn,
                                where=where).fetchone()
        self.cepageData[item] = (row.name, row.cepage.name)

    def OnCacheHint(self, event):
        cacheFrom = event.GetCacheFrom()
        cacheTo = event.GetCacheTo()
        print "cacheFrom: %s, cacheTo: %s" % (cacheFrom, cacheTo)
        self.cepageData = {}
        item = cacheFrom
        while cacheTo >= item:
            self.getData(item, self.cepageDataCache[item])
            item = item +1

    def OnGetItemText(self, item, col):
        print 'item = %s, col = %s' % (item, col)
        if col == 0:
            if not hasattr(self, 'cepageData'):
* # how can I call here -> self.OnCacheHint what do I need to feed it?*
                dummy = ''
            if self.cepageData.has_key(item):
                self.colinfo = self.cepageData[item]
            return self.colinfo[0]
        if col == 1:
            return self.colinfo[1]

Print output:
item = 597, col = 0 -> from OnGetItemText
cacheFrom: 581, cacheTo: 601
item = 583, col = 0
.... (many calls which I will send my thanks to Bill G.
item = 594, col = 0
cacheFrom: 581, cacheTo: 601 -> why do get this one?
item = 594, col = 0
cacheFrom: 594, cacheTo: 595 -> why do get this one?
item = 594, col = 0
...
item = 594, col = 0
item = 16157, col = 0 -> why this one BEFORE the cacheHint event?
cacheFrom: 16138, cacheTo: 16157
item = 16140, col = 0
...
item = 16157, col = 0
cacheFrom: 16138, cacheTo: 16157 -> again unneeded
item = 16157, col = 0
item = 16157, col = 1
item = 16151, col = 0
cacheFrom: 16138, cacheTo: 16157 -> again unneeded
item = 16157, col = 0
item = 16157, col = 1
item = 16151, col = 0
cacheFrom: 16143, cacheTo: 16154 -> again unneeded
item = 16143, col = 0
...
item = 16157, col = 0
item = 0, col = 0 -> again before the cacheHint
cacheFrom: 0, cacheTo: 18
item = 0, col = 0

Robin · June 3, 2003, 9:01pm

Werner F. Bruhin wrote:

Your hint was enough to get me going, and I got it mostly working. Also I still get super flues calls to EVT_LIST_CACHE_HINT where I get the same values (or similar) for From and To as on the previous event.

Yeah, it's pretty dumb. It just sends the range of items that it is about to refresh. It's up to your caching code to determine if you already have an item cached or not.

Below is what I came up with, any suggestions on making this nicer, better are very welcome.

Note, that I am using ORM (Object Relational Membrane) for data access.

The one thing which is not yet working 100% is that OnGetItemText is once called BEFORE the cache event, would like to call OnCacheHint if the attribute cepageData does not exist - any hint on how I do this? Actually just noted that it is always called after the first OnGetItemText for a new range of items, which makes things somehow more complicated - but maybe someone else on the list has some good ideas on how to get around this.

There are a couple approaches here. First is to just call onCacheHint passing the item index that needs to be cached and then checking the type of the parameter in OnCacheHint. Secondly, create another method that is called from both places:

  def DoCacheHint(self, f, t):
    # check for items already in cache, fetch
    # them if not

Then OnCacheHint becomes this:

def OnCacheHint(self, evt):
self.DoCacheHint(evt.GetCacheFrom(), evt.GetCacheTo())

and in OnGetItemText just call:

self.DoCacheHint(item, item)

···

--
Robin Dunn
Software Craftsman
http://wxPython.org Java give you jitters? Relax with wxPython!

Werner_F_Bruhin1 · June 4, 2003, 1:45pm

Robin Dunn wrote:

Werner F. Bruhin wrote:

Your hint was enough to get me going, and I got it mostly working. Also I still get super flues calls to EVT_LIST_CACHE_HINT where I get the same values (or similar) for From and To as on the previous event.

Yeah, it's pretty dumb. It just sends the range of items that it is about to refresh. It's up to your caching code to determine if you already have an item cached or not.

Will have to work on the caching stuff some more.

Below is what I came up with, any suggestions on making this nicer, better are very welcome.

Note, that I am using ORM (Object Relational Membrane) for data access.

The one thing which is not yet working 100% is that OnGetItemText is once called BEFORE the cache event, would like to call OnCacheHint if the attribute cepageData does not exist - any hint on how I do this? Actually just noted that it is always called after the first OnGetItemText for a new range of items, which makes things somehow more complicated - but maybe someone else on the list has some good ideas on how to get around this.

There are a couple approaches here. First is to just call onCacheHint passing the item index that needs to be cached and then checking the type of the parameter in OnCacheHint. Secondly, create another method that is called from both places:

    def DoCacheHint(self, f, t):
        # check for items already in cache, fetch
        # them if not

Then OnCacheHint becomes this:

    def OnCacheHint(self, evt):
        self.DoCacheHint(evt.GetCacheFrom(), evt.GetCacheTo())

and in OnGetItemText just call:

    self.DoCacheHint(item, item)

Thanks Robin, this works great.

Speed is actually acceptable, so will work on doing some better caching a bit later.

See you
Werner