What's it all about, Alfie?

When I added a bunch of additional values to my web app, I noticed that the updating of the Google AnnotatedTimeLine was taking a lot longer than before. I mean it was pausing for a good 3 sec. before updating the view. I looked at the CPU usage, and it was all in the Flex component. It was bad. Very bad. So I knew I had to do something about it.

In the original version of the page, I had code that was run when the data was received and the graph redrawn. This was a standard hook for the AnnotatedTimeLine:

  /**
   * This function is called when the ATL is done updating the graph
   * from the data and the "draw()" method.
   */
  function graphReady() {
    // hide all the unchecked data sets
    for (var i = 0; i < portfolioChecks.length; ++i) {
      if (!portfolioChecks[i].checked) {
        updateBackgroundVisibility(portfolioNames[i], false);
      }
    }
    // ...finish up with more processing
  }
 
  /**
   * This is called to update the visibility of the named portfolio
   * to the provided state in the background graph.
   */
  function updateBackgroundVisibility(name, state) {
    var colCnt = graphData.getNumberOfColumns();
    // I have to find the column name in the table
    for (var i = 1; i < colCnt; ++i) {
      if (graphData.getColumnLabel(i) == name) {
        // the dataset number is one less than the column number
        if (state) {
          chart[bg].showDataColumns(i-1);
        } else {
          chart[bg].hideDataColumns(i-1);
        }
      }
    }
  }

When I originally built the code, I wanted to have something that allowed me to change the visibility of the data sets in the graph(s) either way, and that's really nice. But what I didn't expect was the performance penalty I would pay for such a design.

As it turns out, there's a form of the hideDataColumns() method on the ATL that allows the user to send an array of dataset indexes. I didn't know what to expect, but I thought it had to be better than this, so I recoded this as:

  /**
   * This function is called when the ATL is done updating the graph
   * from the data and the "draw()" method.
   */
  function graphReady() {
    // hide all the unchecked data sets
    var cols = [];
    for (var i = 0; i < portfolioChecks.length; ++i) {
      if (!portfolioChecks[i].checked) {
        cols.push(i-i);
      }
    }
    chart[bg].hideDataColumns(cols);
    // ...finish up with more processing
  }

Without the call to updateBackgroundVisibility(), I knew it'd be a little faster - no need to look up all the column headers, etc. But I didn't expect to see what I saw.

The resulting code took the refresh time from 2-3 sec. to under the blink of an eye. Really. It was a slight flicker, but that's about it. Amazing. In retrospect, it makes sense... I was individually hiding about 30 columns each update. That's a lot. To do it right, there had to be some type of lock, update, refresh/redraw, and then unlock. All that added up. Big time.

It's taught me a big lesson about the efficiency of Google's code: if in doubt, look for a better way, there's probably one there, or you can always ask the visualization team. I know I've learned my lesson.

Today I found another bug in the Google Visualizations AnnotatedTimeLine widget. Basically, if you set the graph setting legendPosition to sameRow, the legend at the top of the widget will start on the same row as the date/time of the point you're currently highlighting. If you have it set to newRow, the legend will start on the line below the date/time. The sameRow looks like this:

and the newRow looks like this:

What you can see is that on the sameRow, the legend starts out right, but it never wraps to the next line. On the newRow version, it wraps nicely, but you loose a complete row, and in the case of large legends, that row is important.

So I posted a question to the Visualization group and got this answer:

Hi,

Please open a feature request from the link at the left side menu, and we will try to get to this.

Regards,
VizGuy

So that's exactly what I did. I'm hoping that they get to this as soon as possible.

This morning there were some updates for my MacBook Pro - specifically, the DVD Drive is supposed to be "making noise" coming out of sleep, or so they say. The EFI fix is necessary for the SuperDrive fix that is the second update in the cycle. There's also a fix for AirPort clients to get better connection stability and the ability to shut it down under all conditions.

Gotta get these, even if I don't see a real problem with the system.

I'm not a big Flash fan, in fact, I use ClickToFlash to keep from seeing it displayed on most of my web usage, but there is one notable exception: the Google Visualization widgets. I use these extensively in my web work to assist in the visualization of the data. In order to run these guys, I need Flash. So it makes sense to keep up with Flash for this reason alone.

This morning, Adobe updated the Flash player for Mac OS X to 10.0.42.34, and I needed to pick it up. Not thrilled, but until Google moves from Flash to something else, this is what I have to do.

This morning I noticed that BBEdit 9.3.1 was out with an impressive list of fixes and features for a minor release update. Had to get that, I use it every single day.

With all the work I'd been doing the last few days with the conversion of the Alerts from a Java Properties file-based system to a database-driven system, I needed to make an editor page for the bulk of the data. It used a ton of AJAX, but it works very nicely and I'm able to put this behind me. Very nice to get this conversion done.

Well... I've sneaked a few developer builds of Google Chrome for Mac OS X, but today I finally got an email from the Google Chrome for Mac team. Now when you go to the Chrome web page it detects that you're on a Mac, and gives you the option to download it for Mac OS X 10.5 or later. It's still 32-bit, but I guess that's not a terrible thing as it's not putting anything into the kernel, which would be a big no-no to getting my laptop to boot 64-bit.

The about page is very nicely done, and it appears to borrow heavily from the Windows versions for the UI, but there are several things that are very Mac-specific, and that's nice to see. Well... I'll be checking it out, but I'll be stunned if it beats Safari. Still, nice to see.

I had to laugh at the email because it listed a "few fun facts" about the Google Chrome for Mac team:

Here are a few fun facts from us on the Google Chrome for Mac team:

• 73,804 lines of Mac-specific code written
• 29 developer builds
• 1,177 Mac-specific bugs fixed
• 12 external committers and bug editors to the Google Chrome for Mac code base, 48 external code contributors
• 64 Mac Minis doing continuous builds and tests
• 8,760 cups of soft drinks and coffee consumed
• 4,380 frosted mini-wheats eaten

it was the last item - the Frosted Mini-Wheats that made me laugh! It's what I have every morning at work. What a gas!

Today I spent a good bit of the day trying to create a nice, user subscription system for my alerts. I needed it to be clear, but easy to use. With the database schema I had, I needed to use a left outer join to get all the alerts the user could possibly subscribe to. It was pretty fun to get it all worked out.

The core was a simple stored procedure that pulled all the data into a nice table:

  CREATE PROCEDURE dumpUserAlerts
      @username VARCHAR(80)
  AS
  BEGIN
      -- make a table to hold a little more than we'll be needing
      CREATE TABLE #everything (
          name        VARCHAR(32),
          description VARCHAR(8000),
          portfolios  VARCHAR(4096),
          chat        bit,
          email        bit
      )
      INSERT INTO #everything
          SELECT a.name, a.description, a.value AS portfolios, r.chat, r.email
            FROM ( SELECT fa.alertID, fa.name, fa.description, fp.value
                     FROM Alerts fa, AlertParams fp
                    WHERE fa.active=1
                      AND fa.alertID=fp.alertID
                      AND fp.name='Portfolio'
                      AND fp.setName='default' ) a
           LEFT OUTER JOIN AlertRecv r
            ON r.username=@username
            AND r.setName='default'
            AND r.alertID=a.alertID
 
      -- update the wildcard portfolios to something readable by the user
      UPDATE #everything
         SET portfolios='[All Portfolios]'
       WHERE portfolios='[*]'
 
      -- now let's add the portfolios to the end of the description
      UPDATE #everything
         SET description=description
                        + '<br><p class="hilite">'
                        + REPLACE(portfolios,',',', ')
                        + '</p>'
 
      -- now let's show the user what they were meant to see
      SELECT Name, Description, Chat, email AS Mail
        FROM #everything
  END

What I really like about the way this is coming together is the coordination of the SQL stored procedures, the Java Tomcat server (with its' servlets), and the web GUI with JavaScript. At my last job I did something like this, but not to this extent. I didn't have the nice JavaScript front-end... OK, I did, but the project didn't make use of it.

What I like is that I have three places to put code: the JavaScript, the Java, and the SQL. This means that I can distribute a solution in the most efficient manner possible. I can make use of the fact that the SQL can be changed independently of the Java or JavaScript, and that makes it a very powerful tool.

This is just how I like to code - across the entire system. Not just in one little slice. The code above was then massaged by the Java to remove NULL values and put in HTML INPUT check boxes and then using the onChange tag, these were able to send back messages to the same database to effect the change of the subscriptions.

Very nice.

Today I spent time taking the database schema I made yesterday and making it look like a Java Properties file so that I could easily create Properties objects and then feed them to the alerts I had and not have to worry about changing the rest of the code. The problem is, the database schema was logical, but it wasn't anything like what the previous Properties file layout looked like.

The ChatTo and MailTo properties were at the same level as the rest of the parameters, but in the new database schema, it's an entirely new (and separate) table, with links to an existing table for the email address. It's different, all right. After a little messing around in SQSH, I came up with the following stored procedure that takes a set name and returns two columns - keys and values, and from this, I can easily feed a newly created Properties object and we're good to go.

  CREATE PROCEDURE sp_getProperties
      @setName VARCHAR(80)
  AS
  BEGIN
      -- get an answer table to hold the whole lot of it
      CREATE TABLE #answer (
          name    VARCHAR(256),
          VALUE   VARCHAR(4096)
      )
 
      -- get the list of the alertIDs that are in this set
      CREATE TABLE #interesting (
          alertID     INT
      )
      INSERT INTO #interesting
          SELECT DISTINCT a.alertID
            FROM Alerts a, ALertParams p
           WHERE p.setName=@setName
             AND a.alertID=p.alertID
             AND a.active=1
 
      -- get the list of all active, interesting alerts
      DECLARE @id INT,
              @val VARCHAR(4096)
      SELECT @val=''
      SELECT @val=@val+CASE WHEN @val='' THEN '' ELSE ';' END+a.name
        FROM Alerts a
       WHERE a.active=1
      -- ...and now that it's built, put it into the answer
      INSERT INTO #answer
          VALUES('Alerts', @val)
 
      -- run through them all, processing each in turn
      DECLARE actives cursor FOR
          SELECT alertID FROM #interesting
      OPEN actives
      fetch NEXT FROM actives INTO @id
      while @@fetch_status = 0
      BEGIN
          -- get the ClassName for this active guy
          INSERT INTO #answer
              SELECT 'Alert.'+a.name+'.ClassName' AS name, a.classname AS VALUE
                FROM Alerts a
               WHERE a.alertID=@id
 
          -- get the VersionID for this active guy
          INSERT INTO #answer
              SELECT 'Alert.'+a.name+'.VersionID' AS name, a.versionID AS VALUE
                FROM Alerts a
               WHERE a.alertID=@id
 
          -- get the bulk of the parameters for the alert...
          INSERT INTO #answer
              SELECT 'Alert.'+name+'.'+p.name AS name, p.value AS VALUE
                FROM Alerts a, AlertParams p
               WHERE a.alertID=@id
                 AND a.alertID=p.alertID
                 AND p.setName=@setName
 
          -- get the ChatTo list for this active guy
          SELECT @val=''
          SELECT @val=@val+CASE WHEN @val='' THEN '' ELSE ';' END+u.username
            FROM AlertRecv r, Users u
           WHERE r.alertID=@id
             AND r.username=u.username
             AND r.chat=1
             AND r.setName=@setName
          -- ...now that it's built, insert it into the answer
          IF @val<>''
              INSERT INTO #answer
                  SELECT 'Alert.'+a.name+'.ChatTo' AS name, @val AS VALUE
                    FROM Alerts a
                   WHERE a.alertID=@id
 
          -- get the MailTo list for this active guy
          SELECT @val=''
          SELECT @val=@val+CASE WHEN @val='' THEN '' ELSE ';' END+u.username
            FROM AlertRecv r, Users u
           WHERE r.alertID=@id
             AND r.username=u.username
             AND r.email=1
             AND r.setName=@setName
          -- ...now that it's built, insert it into the answer
          IF @val<>''
              INSERT INTO #answer
                  SELECT 'Alert.'+a.name+'.MailTo' AS name, @val AS VALUE
                    FROM Alerts a
                   WHERE a.alertID=@id
 
          fetch NEXT FROM actives INTO @id
      END
      close actives
      deallocate actives
 
      -- now return this to the caller
      SELECT name, VALUE FROM #answer
  END

Now while I'll be the first to admit that this is not the most efficient way of getting the data into a format that I can easily parse, it's pretty fool-proof. I wanted to make the Java parsing as simple as possible, and this does the job - a simple table with two columns - names and values to put in the map. Can't get any simpler than that.

I will say that some of the parts that I think are clever are the building of the strings that are really individual rows in the database - like the list of active alerts, or the list of ChatTo or MailTo recipients:

      SELECT @val=''
      SELECT @val=@val+CASE WHEN @val='' THEN '' ELSE ';' END+a.name
        FROM Alerts a
       WHERE a.active=1
      -- ...and now that it's built, put it into the answer
      INSERT INTO #answer
          VALUES('Alerts', @val)

We know that the select statement contains an implicit loop, and by using the case statement in the select, we get the delimiters in the right place - as opposed to an unnecessary trailing, or leading, one. I have to give credit for the use of the case to a co-worker. Pretty nice and concise.

With this, I'm able to finish the replacement of the Properties file with a database schema, and that's what I needed. In the coming days I need to make an editor in my web app, and then put a user-serviceable subscription page together so that the users can update what they get on their own. That will be a nice win for the web app.

I keep hoping that Miro will get a little more mainstream, but I think it's going to be a lot like Public Television - interesting, and educational, but not necessarily entertaining. Still, it's nice to see them making progress, and there's always time to learn something new on PBS. 🙂