Learning Technology

TouchStone and the Rapid Selector

July 14th, 2011 at 03:07

Vannevar Bush“There is a little used machine in existence – the rapid selector – which can review items on a strip of microfilm at the rate of 1,000 items a second and print those which are selected in accordance with a code set on a keyboard. It could, for example, review the case histories of half a million hospital patients in 10 minutes and present for aid in diagnosis printed copies of the few cases which corresponded to an unusual set of symptoms and complications.” (Bush, 1953)

The above quote was written in 1953 by Vannevar Bush who earlier wrote the famous ‘As We May Think’ paper in 1945. It is interesting to compare the speed of his rapid selector to the speed of today’s modern databases. TouchStone, an online assessment system, uses an open source database called MySQL. Some in the industry have looked down their noses at MySQL, instead favouring Microsoft’s SQL server or Oracle, but MySQL is far from a toy.

As the use of TouchStone has increased over the years so too has the size of the database tables, the largest of which is the log table for formative quiz answers which is currently 3.2 million records. When queries are executed MySQL can display how long in seconds the query took to execute. Selecting a record from the formative log can take 0.00 seconds. This is not to say that it is instant but that it is quicker than one hundredth of a second. This puts Bush’s 10 minutes for half a million records into the shade. The key to this phenomenal speed is appropriate indexes. These work like a card index so that if the database needed to look up ‘Jones’ then it can go straight to the ‘J’ divider card.

One of the problems with the architecture of the web is that it is stateless (i.e. each action is treated independently so if you ask for the same thing twice it doesn’t know you’ve asked for it before because it has no way to save your previous state). So each page has to work out what it is doing independently – it has to ask is the current user allowed to look at the page, what questions should it display and which answers should it store in the database. An exam screen in TouchStone for a single user can carry out as many as 10 queries. Multiply this by a large cohort of 300 students and then by 10 screens and the database is already having to handle 30,000 queries. The peak of database activity tends to occur towards the end of an exam when the examinees are moving quickly between screens checking for missing answers.

In the past the average number of queries per second has risen as high as 700. Keen to try and bring this figure down, so that TouchStone can run with even larger cohorts, an investigation of the code was made. No bugs were found, but an interesting inefficiency was detected. One of the important queries checks if the client computer’s IP address matches that of a listed computer room, to ensure the exam is only accessible from a particular room. This query executed for every question on a screen. Doing the check once per screen nearly halved the average number of queries per second (to 400). A second efficiency was also made concerning users with special needs. Originally a query was made to a special needs table for every examinee (so that extra time could be allocated as required). A re-engineer was carried out so that the main query that returned user authentication details also returned a special needs ‘flag’. Only if this flag was set to true was the second query initiated. This saves doing the query for the majority of users in a cohort.

Efficient database design is still a bit of a black art with careful design of keys within the database and coding in the application, but I believe Vannevar Bush would be proud of modern day databases.

by Simon Wilkinson

Post a comment

You must be logged in to post a comment.

No comments for “ TouchStone and the Rapid Selector ”