The Olympics

Well 2008 Olympics, and I have enjoyed it quite a bit.  I really enjoyed the opening ceremony, and it was a great chance to use the PVR to record the whole thing.

It will be good to not feel compelled to have the TV on for a while.  When so much top level competition is happening I feel that I need to enjoy it all…

Things I will not miss, and think should be scraped are:

Walking race – really how is this a sport, it’s so contrived, ‘lets run, but without running‘. I mean you walk when you don’t want to run, or you run when your late and have a need to get there faster.  So running races are about going as fast as you can, yet the walk (I don’t want to go fast) race, is about going as fast as you can without going fast, how can that be a race.

Womans beach volleyball – lets get a great team sport, and empty the court of most of the players so that points are easy to get.  Oh and now lets skimp the togs down so people watch.  Um, if the people are not watching it’s because it’s boring.  So tarting it up means only oglers will watch – is that what the Olympics is about, perving?

My other major gripe I have is with sports that could be defined as a ‘game’.  Football, Baseball, Softball and Tennis are not really higher, longer, faster are they.  Ok, there are some team games I do like, but would be willing to loose, Volleyball, Table Tenis.  I just don’t see ‘games’ as personal excellence.

My last gripe, is that TV One had four TV streams available on-line, but our Internet plan charges for traffic because ‘international traffic is expensive‘ yet local traffic also gets billed at these rates, because ISPs can. Nice one ISPs.

Slow sub-select maybe LAG or LEAD might help

One of the queries in our application is used to edit event entries.  Event entries have a start time and an end time, but when they are processed from the data files, they can be entered with just a start time (ie when the event started), but in cases of problems (crashes) these entries will not have matching entries with end time.

So in our editor, we are wanting to know the upper limit of when the event could have ended, which is the next start time.

ROWID TIME_START TIME_END OTHER_FIELDS
1 2008-1-1 null data
2 2008-1-2 2008-1-3 more data

So for row 1 we want 2008-1-2 as the suggested end time.

Originally we had a query that looked like this:

SELECT other_fields,
  TIME_START,
  TIME_END,
  NVL((SELECT MIN(TIME_START)           FROM some_view
         WHERE MACHINE_ID = T1.MACHINE_ID
           AND TIME_START > T1.TIME_START),
       TIME_START + 1/24) "NEXT_TIME_START"
  FROM some_view T1
  WHERE MACHINE_ID = machine_id
    AND NVL(TIME_END,
            NVL((SELECT MIN(TIME_START)
                   FROM some_view WHERE MACHINE_ID = T1.MACHINE_ID
                   AND TIME_START > T1.TIME_START),
                TIME_START + 1/24)) > start_time_range
    AND TIME_START < end_time_range ORDER BY TIME_START

 

So this is a little trickier than described above as it deals with the edge case of when there is no next entry.  In that case the current start time plus 1 hour is used. I coloured all the optional/variables yellow.

This performs fine with small data sets, but when we had over a million rows in that view, the selection of one days data (~2700 rows) took 551 seconds (>9 minutes), and this was one of many large views, so the overall effect was a >30 minute wait.

I rewrote the query with the use of LEAD, and the performance went to 4 seconds (with data retrieval) or 0.6 seconds for a count(*) operation.  Here’s that query now using LEAD.

SELECT *
    FROM(SELECT other_fields,
           TIME_START,
           TIME_END,
           NVL( LEAD(TIME_START) OVER (PARTITION BY machine_id ORDER BY time_start),
                TIME_START + 1/24) AS "NEXT_TIME_START",
           FROM some_view T1
           WHERE MACHINE_ID = machine_id )
    WHERE NEXT_TIME_START > start_time_range
      AND TIME_START < end_time_range
    ORDER BY TIME_START

 

Now that I review the code I notice I also rearranged the code to not repeat the next_time_start calculation, so I am now not sure all the performance improvements can be attributed to LEAD, but I’ll take that ~138 times improvement either way.

DVD Unlimited – Thanks, but no thanks

We have just ended a three month free trial with DVD Unlimited, and we will not continue with their service.  We got the trial as part of buying our TV, so it did not hurt us to try.

Things I liked:

  • DVDs arrived in the mail
  • You could return the DVDs in the mail
  • You could keep a movie for as long as you want, this would work well for games (not that we got any of the games from our request list)
  • We got to see lots of foreign movies, as there seems to be no queue on these

Things that I did not like:

  • I’m not sure we ever got any DVD in the top ten of our 20+ DVD selection list
  • I’m not sure any of the DVDs we received would be ‘new release’ at the local video shop
  • Three of the first four DVDs didn’t play error free.  While they have a good swap program, it was a disappointing start, especially when the movies were not worth replacing, but only ‘watch it because we have it‘ quality
  • The movie suggestion system never had any results
  • When rating movies, the “0 1 2 3 4 5” text link based ratings where hard to read, and also hard to gauge meaning of the levels.  I really like how Amazon use bright stars, with text describing the meaning of that level eg “It’s ok”, “I like it”, “I love it”.  This allows you to know others will interpret those statements the same
  • They have lots of TV series, which is good if you want that, but I don’t and there’s no way to say “stop showing me all these freaking TV shows”, well there is a “not interested” button, but that seemed to have zero effect
  • When rating lists of DVDs I found it highly disturbing how the next item is auto focused/scrolled.  Once you learned what was happening, it sort of worked, but only if you had not scrolled the window, otherwise its auto jump feature moved incorrectly
  • It was very hard to find the details of our plan, and the options on how to handle our monthly limit where not in line with our trial limit. The limiting feature told us our plan was 10, but only gave the option of “4 movies a month(your limit” which it wasn’t
  • I didn’t like their terms and conditions, one of which includes that you are not allowed to link to them. Fine no Google juice for you!
  • Over the last week, after logging on to the site, the site shows this prompt:
    NAME_OF_MOVIE was awesome, don’t you think?
    Your Rating: 0 1 2 3 4 5
    Write Review

    No, I already told you it was crap (1), so stop asking.  But there it is again
  • There is a pressure to watch DVDs when they arrive so you get your $$ worth.  I guess this happens if you rent too many movies from a rental shop also…

So over all, $28 x 3 months for 20 DVDs which I’m sure I could rent for $1 each, plus fast post each way equals

Cost: $84 > Value: $60, so even if I’m generous and cost each DVD at $2 each + pp ($2), that brings the value to $80, which still below cost.

Mac Widget – Calc Points

Calc Points Widget

I have now made my Mac Calc Points widget available.

The widget is used for calculating the ‘points’ for food, based on the kilojoules, saturated fat, and serving size on the food packet.

You can choose kilo-calories or kilo-joules, measurements ‘per 100g’ or ‘per serving’, and the calculations based on constant numbers that are used in New Zealand or those suggested on Wikipedia.

To change the options, just click on the i in the bottom right corner.

Sicko – Michael Moore

Michaela and I have just finished watching Sicko, fantastic movie/documentry, that I’d been keen to watch for a while. Nic has an even better write-up so just read his (in full).

It has made me hesitant about working in the US, it seems illogical to go somewhere so messed up.  In fact I feel not so bullet-proof about my pending trip there.

I guess I’ll take it a day at atime, and hope for good luck, just like 30 million Americans.