Bill Roper (billroper) wrote,
Bill Roper

Adventures in XML

So I was asked to look at why it was taking so long to scroll around the Excel grid that our application -- operating as an Excel add-in -- has loaded data into. A few years ago, I wrote a very efficient data handler for the grid data being kept on the back end which we query to get a lot of property information that gets displayed as we move from cell to cell, so my first thought was that perhaps the different back end grid data handler that we were actually using just wasn't efficient enough.

I examined the code and it didn't look too bad. Then I decided to just comment out the call to the back end data handler.

And the scrolling was still slow.

Mind you, the scrolling was only slow when you had a big sheet of data. 2500 rows and 7 columns were enough to bollix it up, but 30 rows and 7 columns scrolled just fine. Hmm.

Oh, look. There's a big string that's an XML representation of all of the data on the sheet. It gets loaded up and searched using an XMLDocument. In fact, we're calling a function that loads the string and returns the particular bit of data we're looking for. And we call that function seven times.

Well, let's do some Googling. Ah! XPathDocument is faster. Ok, I'll swap that in.

Not that much help.

I don't really need to load that string into an XPathDocument seven times. I can load it into the XPathDocument once, get an XPathNavigator, and then pass that into a new version of the search function.

Oh. And we're also searching for the same item three times. Well, that's at least two times too many, because I can create a string to store it in without really thinking about it.

That's better. Heck, that's pretty much tolerable. It still gets slower on a big sheet, because there's a ton of stuff in the XML string that we don't care about for what we're doing here.

Unfortunately, I'm not in a position to change what's stored in the big XML string.

On the other hand, if I cache the invariant information the first time that you enter the event handler for moving between cells on the sheet, then the only additional information that I need is the row and column number. And I can get that directly from Excel without having to do anything with the big XML string. So let's cache that info and then add code to the event handler that's called when a sheet is activated to clear the cache, so I don't get any nasty surprises from the cached data.

Hey! Look at that! Now it scrolls really quickly!

I think I'll just put my efficient back end code away for a while now.

It may come in handy someday. Just not today. :)
Tags: computers, microsoft, musings, tech, work
  • Post a new comment


    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

  • 1 comment