Sunday, September 19, 2010

Discussing an iPhone App: Settlers of Catan

As noted previously, I’m thinking about starting an iPhone App, so I’ve been paying closer to attention to things I like and dislike about some of the apps and games I’m using.

I like playing the occasional game on the iPhone, and in the last few weeks I was playing a lot of Siedler of Catan (Settlers of Catan in English). Just to be sure: The adaption of the board game is done quite well, the computer players have different strengths, so the game provides fun and entertainment for a long time. Well spent 4€ (or 5$)!

However, there are a few issues that I have. The start of the game features an elaborate animation .. but there is no way I have found to get straight into the game. So every time you start the app, after waiting a few seconds for the animation to load, you get the start of the animation

IMG_0001 After tapping on the screen, you get another splash screen:

IMG_0007where the game even tells you to tap the screen (“Bitte Bild berühren”). Then you get the main game menu

IMG_0005 where you can hit “Spiel  fortsetzen” to continue the ongoing game.

That’s three useless taps (and probably 10 seconds of time) that I would consider bad style for an iPhone app. (It’s okay for a PC game, but not for an iPhone app that gets frequently stopped and restarted.)

There are a few other examples where a bit more thinking would have helped create a better experience. For example in this screen you have only three options, but the graphics don't fit the screen so there may be some scrolling involved:

IMG_0003 With just some more work, the graphics would fit the screen. Also, swiping doesn’t really work here, instead you have to use the provided arrows.

I’m sure that these issues arise from trying to be consistent with other versions of the same game, but I would think that some more care should have been taken while adapting the game to the iPhone.

So five stars for the basic game, but only one star for the iPhone adaption.

Friday, August 20, 2010

Getting into iPhone development?

Over the last weeks, I’ve been thinking about starting some development for the iPhone. There are some ideas in my head of what I could do, but nothing specific yet. Only thing that I’m sure is that I don’t want to do another “me too” program, but something that really uses the iPhone strengths.

For example, a lot of mobile apps I see just has you entering data on the iPhone using the traditional “keyboard” and “picker” methods. I’m pretty sure that some gestures may be helpful and allow for quicker entry. Also, oftentimes “guessing” from a GPS-location or time-of-day may offer some good default values. I hope that this will be a road that I’ll be able to explore a bit more.

For now, I’m just running down the list of things I need:

  • iPhone (check)
  • Apple Developer membership (check, although for now just the “free” level)
  • Macintosh computer
    in the works, I wanted to get a new laptop anyways, and the 13” MacPros look pretty good for what I have in mind …

Also, I’ve started to read a few books on iPhone dev, I hope to be able to review some of them in my next blog entries …

Friday, January 15, 2010

Performance Issues using PL/SQL Tables in SQL Statements

Recently, I ran into an interesting performance problem while writing some PL/SQL code for work:

  • We’re using a PL/SQL table to collect some id that have to be updated (using UPDATE aTable Set .. Where id IN (…)).
  • The update statement was very slow.

I’ve posted question #2057055 on Stackoverflow and got some very helpful replies that helped me figure out what was going on and what to do.

Problem Description

The best way to describe the problem is with some code:

create table atable (acolumn number, id number);

insert into atable select rownum, rownum from dual connect by level < 150000;

alter table atable add constraint atab_pk primary key (id);

create type type_coll is table of number(4);

exec dbms_stats.gather_table_stats(ownname => user, tabname => 'ATABLE');

    a_coll type_coll;
  a_coll := type_coll(1,2,3,4);
  for n in 1..1000
    UPDATE aTable
    SET aColumn = 1
                     FROM   TABLE (cast (a_coll as type_coll)) 
  end loop;

This script creates a new table with 150.000 records and does some housekeeping to define a primary key and make sure the optimizer has decent statistics. Then the PL/SQL table type is defined (it has to be defined as a type on the database level so it can be used in SQL statements)

The final statements (starting with declare) form an anonymous PL/SQL block that simulates the problems I was seeing:

  • The PL/SQL table a_coll is declared and filled with some values. (There are only a few values, which was also similar to the scenario I was having problems with.)
  • Then the UPDATE statement I was describing above is executed 1000 times. The statement looks a bit weird, but that is how you can use the contents of a_coll in a SQL statement. (There may be a way to get rid of the cast, but I couldn’t get that to work.)

When running the PL/SQL block, it was running quite a while (about 4 minutes in our environment, so about 4 updates per second) .. way too long! (The scenario I was implementing required more than 150.000 updates which would have been 10 hours .. totally unacceptable.)

Performance Analysis

The first thing with a slow statement is to look at the execution plan. As I couldn’t get an ‘Explain Plan’ for the whole PL/SQL block or just the ‘Update’ statement to execute, I just tried to get a plan for this statement which I felt was what was executed by the database:

SET aColumn = 1
WHERE id IN (1,2,3,4);

For this, I got a plan that looked good, it was using the primary key defined on id, so it should run fast enough. When I used that exact statement in the block (instead of all the SELECT Column_Value stuff), the execution time dropped dramatically to basically sub-second performance for 1000 updates. So obviously something else is going on …

When talking about this problem with a colleague, he showed me a way to get to the execution plan of the statement that is actually executed. He was using the Oracle Enterprise Manager to look at my session and the current statement and plan, later I was able to do the same thing on my machine using TOAD and the Session Browser.

When looking at the execution plan of the statement that is executed when running the PL/SQL block, we realized that the primary key is not utilized, but instead a full table scan is used. Obviously, that is much slower than using the index for the few records that were required, so that explained the slow performance I was seeing.

Improvement I

The first possible solution to the performance problems I was seeing is to force the use of the primary key index. In Oracle, this is done using Optimizer Hints that are enclosed in /*+ <hint> */. Apparently, they were very common in older versions of Oracle, but haven’t been that important in newer versions (when the Optimizer switched to cost-based instead of rule-based and was much better in coming up with a good plan).

We can force the optimizer to use the index by writing the update like this:

UPDATE /*+ INDEX(a_Table SYSxxx) */a_Table
SET aColumn = 1
FROM TABLE (cast (a_coll as type_coll)) );

with SYSxxx being the index for the primary key that is generated by Oracle.

When using this Update-Statement, the performance was the same as with  the ‘naive’ Update shown earlier. The execution plan showed that indeed the index was used.

While this works, it is a bit clumsy .. we have to figure out the name of the generated index (which may change when the table is dropped and re-created or may be different between dev, qa and production databases).

Improvement II

In order to build a better solution, first we have to figure out what is going on and why the optimizer comes up with the bad execution plan. As first explained by Nils Weinander:

The bad execution plan is probably unavoidable (unfortunately). There is no statistics information for the PL/SQL table, so the optimizer has no way of knowing that there are few rows in it.

(Nils also suggested to use the index hint as explained above.)

This explains why the optimizer chooses a full table scan: There may be a lot of matching rows, so a full table scan may be quicker. (I’m not sure where the cut-off would be, but probably when more than 10% of the rows are affected, a full-table scan is faster then identifying the rows through the index and then retrieving the full record for the update).

However, there is a way to tell the optimizer how many rows there are in the PL/SQL table using the cardinality hint (suggested by APC):

UPDATE aTable 
SET aColumn = 1 
WHERE id IN (SELECT /*+ cardinality( pt 10 ) */ 
              FROM   TABLE (cast (a_coll as type_coll)) pt ); 

This tells the optimizer that there are about 10 records in the table pt (which is an alias for our PL/SQL TABLE). When using this statement, everything works as expected, it is quick and also uses the primary key index (without explicitly having to reference it in the SQL statement).

With this, I was able to improve the execution time of my initial scenario from about 10 hours down to about 5 minutes total execution time. Not too shabby an improvement!

Sunday, December 27, 2009

Lessons learned from redesigning my website

Just finished a major re-design of my business website. Most of the work seemed to be to just figure out a basic design (also something that I’m not really good at). In my previous post I included a mockup of the design I had in mind. I managed to get things almost exactly done as I wanted.

There were a few things I changed in the html:

Menu Border Images

In the old design, there were little images at the left side of the menu bar:


This added a nice border to the menu bar, but of course this was totally a visual thing and nothing that belonged in the html. It should have been a background image of the div that made up the menu bar. In the new design, the images were no longer needed, so they were removed from the html files.

SmarterSoftware Pen Image

In the header of each page, I have an image of the company logo, a pen writing some digital code (originally designed by Leo Blanchette at Jesterarts):


As this is also nothing structural, I’ve moved the reference to the image from the html to the css file as a background image. This is not totally clear-cut as with the menu images. In the first version I made the image a link to the initial splash screen with a large version of the pen. I’m not aware of any way this can be done with a background image (other than with JavaScript, which I may do in another version).

Additional divs

The only thing that was a bit tricky to do in CSS was the way I wanted to “switch” colors in the left “column”:


I’ve played around with the “border-left” properties but could quite get it to work.

Here’s a little code snippet of what I finally implemented:

<div id="header" class="outer"> <div class="inner">
    <p> Smarter Software </p>
</div> </div>
<div id="nav" class="outer"> <div class="inner"> <ul>
    <li><a id="current" href="home.html"> Home </a> </li>
    <li>&bull; </li>
    <li> <a href="loesungen.html"> L&ouml;sungen </a> </li>
</ul>  </div> </div>
<div id="main" class="outer"> <div class="inner"> 
    <h1>Wir &uuml;ber uns</h1> 
</div> </div>
.outer {
    background-color: rgb(0, 102, 153);
    margin-right: 20%;

    background-color: white;
    margin-left: 15%;
    padding: 10px;

div#nav.outer, div#footer.outer{
    background-color: white;

div#nav .inner, div#footer .inner{
    background-color: rgb(0, 102, 153);
    padding: 0;

The outer divs define the color in the left “column”, usually blue (the rgb-color). Also, with the right margin set here, the color to the right of the text will be the standard background color (usually white). We could move the margin-selector to the inner class, so that the color on the left would be repeated on the right. The outer div only contains an inner div, but no content on its own.

The inner divs then define the left margin and the background color for the “main” content area. Usually, it’s a white background, but for #nav (the navigation or menu bar) and the footer the colors are swapped between the inner and outer divs.

This achieves the desired look, but introduces extra divs that are purely presentational. Does anyone have a better idea how to achieve this look without additional divs? Any ideas are appreciated!


I was quite happy that there weren’t to many changes required. Once I got the main page done, there weren’t many steps required to get the whole website changed. Total re-design took two evenings on X-mas, maybe 4 to 5 hours of work in total (including this blog-post). Not too bad!

Friday, December 18, 2009

Planning to re-design my website using just CSS

When I became a freelancer more than a year ago, one of the first things I did was to build and deploy my own website.

Back then, I knew basic HTML, but CSS was pretty new to me. What better way to get to know it than with a real project! I’ve read a couple of books and websites and managed to get things done and I’m quite happy with the results from a coding perspective.

Now that I’m starting to talk to a few more people in my network, there are some new people looking at the site. An old colleague/boss remarked recently that it looked like a school project.  Touche!

So I looked a bit around for a new design. One inspiration was a friend’s design for PowerPoint slides. As I also plan to add a “business blog” to my site, I’ve looked into wordpress as blogging platform and some of the more common templates. I’ve picked a good looking template and did some changes to it, so between those two inspirations, I’ve got a baseline.

Here’s a quick mockup:

Website Mockup

Depending on how well I developed the site last year, I managed to split content and structure (in the HTML) and presentation (in the CSS). I hope to get almost all of the changes done just by changing the CSS. My goal is to do minimal changes to HTML (apart from adding maybe a class or id here and there). It’ll be interesting to see how much I’ll be able to do and I’ll post some lessons learned in this blog.

Have you tried similar things? Let me know in the comments or send me an email!

Friday, July 31, 2009

Possible Extensions

The code I currently have is working fine for me. However, there may be a few extensions that can be helpful.

Configure locations

For now, some information is hard coded, for example the initial path that the playlists are to be written to. There should be some easy way of configuring this information and storing the information “somewhere”.

Check existence of files

It only makes sense to put files in the playlist if the files exist at the location that is put into the playlist. The Converter could check if the file actually exists and only include the file if it is already there.

This information could be checked at the time the playlist is displayed in the listbox. Not sure if the runtime of all that checking would be acceptable.

Copy the music files if required

The next step would be to have the Converter copy the file from the local path to the server location if the file is missing at the server location. That way, the file would be there whenever it is referenced in the playlist on the server.

Interested or other ideas?

Let me know if one of these or some other feature would be useful to you – just leave a comment or drop me an email. I will then see if I can put that in the code.


Friday, July 24, 2009

Retrieving track information from the iTunes XML library file

In order to convert a playlist as described in the previous post, we need two pieces of information:

  1. the tracks (represented by their IDs) in a playlist
  2. the location of the file of a certain track

How to get this from the iTunes XML library file is described in the next sections.

Get Tracks in a playlist

Similar to the playlist member in the library, the trackIDs member of the playlist is built at the fist time it is accessed:

public List<String> getTrackIDs()
{   // lazy initialization
 if (_trackIDs == null)
     _trackIDs = new List<String>();

     //Query XML for TrackIDs
     IEnumerable<XElement> tracks = (from element in _root.Descendants()
                                       where element.Value.Equals("Track ID")
                                       select element);
     foreach (XElement track in tracks)
         _trackIDs.Add((track.NextNode as XElement).Value );
 return _trackIDs;

Again, the LinkToXML query retrieves the items with a certain value (“Track ID”) from the XML. The statement uses just the part representing the playlist (stored in the _root member). It then moves over to the NextNode to get the actual value and stores all of these in a member that the form can then iterate over.

Get filenames for a given TrackID

Once you know the IDs of all the tracks in the playlist, you can then retrieve the filename as follows:
internal string GetTrackLocation(string ptrackID)
 // get root of XML-Dictionary for this track
 XElement track = (from element in _root.Descendants().Elements("key")
                   where element.Value.Equals(ptrackID)
                   select element).First().NextNode as XElement;

 XElement location = (from element in track.Descendants()
                   where element.Value.Equals ("Location")
                   select element).First().NextNode as XElement;

 string fileName = HttpUtility.UrlDecode((string)(location  as XElement));

 return fileName;
This code is in the library class. I could have used a dedicated track class, but apart from this little piece of information, there wasn’t anything else needed – so that felt a little bit too small to actually build a class for that.

The code gets the location in these steps:

  1. Get the dict for this specific track (lines 4 to 6)
  2. Get the “Location” from within this specific dict (lines 8 to 10)
  3. Get the ‘proper’ formatting by URL-Decoding the value from the XML.
I’m sure that there are easier or more compact ways to achieve this, but this code is working fine and seems quite clear to me. Let me know if you have better suggestions ….