Thursday, May 28, 2015

More Star Trek fan productions

Just a quick reminder that there are a lot of Star Trek productions out there, some really terrible, some really good. I am going to talk about three(ish) of them today.

First one is Star Trek: Federation One. It is with a lot of actors you have seen in other Hidden Frontier and Studio Areakt productions and focuses on the chief security of the presidential office of the Federation. While the plot is kind of a mess, the acting was decent and I was actually very sad to see that after the first "season" of two episodes they continued the story in an audio format. Even if the main character looked like he was on speed all the time, I had fun with it.

Another one is Star Trek: The Infinite Chain. Even if it is split into "episodes" it is actually a feature film. The acting is a bit amateurish, but decent. No obese teens in this one. It uses the now tired plot of a Federation starship thrown into unknown space by "an anomaly". The worst part of it was the "doctor" that was assigned to the mission. His character was believable: the guy in charge of the project creating the sensors used to study the anomaly, but in truth not a good manager or having any merit in the team. He just explodes randomly in childish emotional outbursts, threatening everybody with his artificial authority, and everybody hates him. I totally know people like that. However the actor was just goddamn awful and his script lines ridiculous! The makeup was ingenious, but really cheesy.

The last, but certainly not least, is something made by the Hidden Frontier team, but not set in the Star Trek universe. I am talking about Frontier Guard. The casting is made with the better actors of previous productions and, while the universe seems very similar to the Star Trek one, it is actually a different thing altogether. They use another type of space propulsion, it involves a FederationFrontier Guard academy and it concerns The Arc, a huge alien artifact that may have seeded the galaxy with intelligent life. Unfortunately, after some great episodes, they completely lost it. They began (some) episodes with "My name is..." evoking feelings of remembrance towards all the bad DC superhero series and movies, they started pointless subplots like the gay relationship thing - which always pisses me off, not because of the gayness, but because of the relationship that doesn't further the plot in any meaningful way - and threw in the towel after the 12th episode (which was an elevator show, basically).

From these three Frontier Guard was the best. My guess is that they wanted to do a Star Trek Academy series - which makes total sense and it should be the direction of the next Star Trek series - but they didn't get the approval. Since this sounds like a really good idea and one that might attract a lot of fans, I believe studios are keeping the story in reserve. However, not being Star Trek allowed them to evolve both stories and characters and I was really excited to see this done. In a world where everybody is remaking remakes, we need and deserve original content. Just look at David Feintuch's Seafort Saga series of books. That would make a fantastic TV series and has enough material to allow for several companion feature films.

For people just considering starting to watch these, do not expect Star Trek Next Generation. The actors are amateurs, I mean real amateurs not professionals who just started acting, most of them are American Trekkies and - even if I don't want to insult - some of them are just humongous pieces of fat with big glasses, which kind of explains the whole geek high school thing that is kind of difficult to understand in Europe. The stories, the lines, the makeup, the uniforms, the special effects, they are all done by enthusiasts. Sometimes they feel cheesy, sometimes they look completely and horribly fake. But once you realize that they are people like you, enjoying the wonderful universe of Star Trek, you can begin enjoying these productions.

I've watched other things as well, including Machinima style animations like Borg War - which I wouldn't recommend, but wasn't bad, and I will continue to look for these things. Just by looking at people like Hidden Frontier, who start doing something for the fun of it and end up doing original stuff with veteran amateur actors, I get filled with hope for the whole fan made universe, not just the Star Trek one. Keep up the good work guys (and if you can't do it good, keep working and it will get there eventually :) )

The best thing about it is that most of these series are online, on YouTube, free to watch. Here are some links to the shows above and others:
Star Trek Federation One - 1.01 Unity
Star Trek Federation One - 1.02 Institutions

Star Trek: The Infinite Chain

Frontier Guard

Star Trek: Digital Ghost

Special mention: Star Trip - a humorous parody of Star Trek.

Tuesday, May 26, 2015

Determine the best matching regular expression for a string

I had this situation where I had to match a string by several regular expressions and choose the most specific match. Let me explain it a little bit further: you have a string and several regular expressions that match that string. The requirement is to choose which one matches better. This would be useful if, for example, you want to match mobile manufacturers and so you define one configurations for Apple and another for Android and another for the rest. The regular expressions would be "Apple", "Android" and ".*" (which matches everything). So you would want to choose only "Apple" for Apple devices and not ".*".

Now, I've studied regular expressions a bit, but I don't want to implement a regular expression parser in order to compute the complexity of the expression tree. What I want is to have various Regex objects and, somehow, compare their complexity. I am not going to pretend that I understand what goes on within the Regex object, instead I will say that I noticed the complexity of a regular expression is directly proportional to the length of the pattern and, in the case of the Regex object, with the number of items in the _codes int32 array found in the code RegexCode object found as a private field of the Regex object.

So, the simplest code for that is this:
private double regexComplexity(string pattern)
    var reg = new Regex(pattern, RegexOptions.Singleline | RegexOptions.IgnoreCase);
    var codeField = reg.GetType().GetField("code", BindingFlags.Instance | BindingFlags.NonPublic);
    var code = codeField.GetValue(reg);
    var _codesField = code.GetType().GetField("_codes", BindingFlags.Instance | BindingFlags.NonPublic);
    var codes = (int[])_codesField.GetValue(code);
    return codes.Length;

However we must take into consideration several cases:
  • The regular expression pattern might be wrong
  • Reflection is slow

So, let's first catch all exceptions and use the length of the pattern as the complexity and then find a way to cache the complexity of a string, in case we use it more than once. Also, cache the FieldInfo objects for use at every call.

Here is the final code:
public static class RegularExpressionExtensions
    private static ConcurrentDictionary<string, int> _dict;
    private static FieldInfo _codeField;
    private static FieldInfo _codesField;

    static RegularExpressionExtensions()
        _dict = new ConcurrentDictionary<string, int>();
        _codeField = typeof(Regex).GetField("code", BindingFlags.Instance | BindingFlags.NonPublic);
        _codesField = _codeField.FieldType.GetField("_codes", BindingFlags.Instance | BindingFlags.NonPublic);

    public static int Complexity(this Regex reg)
        if (reg == null) return 0;
        var pattern = reg.ToString();
        var complexity = _dict.GetOrAdd(pattern, p => getComplexity(reg));
        return complexity;

    private static int getComplexity(Regex reg)
        var code = _codeField.GetValue(reg);
        var codes = (int[])_codesField.GetValue(code);
        return codes.Length;

Basically we encapsulate the Regex complexity in an static extension class that we can use on any Regex instance as reg.Complexity().

Monday, May 25, 2015

Pixelo Solver, now at Github

Today I published a raw version of a program that solves Pixelo puzzles, a Flash version of the game generally known as Nonogram, Picross, Hanjie, etc.. I was absolutely fascinated by the game, not only the concept, but also the attention to details that Tamaii lent to the game. The program is called Pixelo Solver and you can find it at Github, complete with source code.

I liked working on this because it covers several concepts that I found interesting:
  • Responding to global key combinations
  • Getting a snapshot of the screen and finding an object in it
  • Parsing a visual image for digits in a certain format
  • The algorithm for solving the puzzle in a reasonable amount of time and memory (it was not trivial)

Quick how-to: get all the files from here and copy them in a folder, then run PixeloTools.GameSolver.exe and press Ctrl-Shift-P on your puzzle in the browser.

  1. Start PixeloTools.GameSolver.exe
  2. Open the browser to the Pixelo game
  3. Start a puzzle
  4. Press Ctrl-Shift-P

However, if the list of numbers per row or column is too long, the automated parsing will not work. In that case, you may use it offline, with a parameter that defines a file in the format:
5 1 10, 3 3 7, 2 5 4, 2 5 2, 2 1 1 1, 1 1 1 1 1, 1 1 3 1 1, 1 1 1 1 1, 1 1 1, 1 1, 1 1, 2 2 2 4, 2 1 1 1 2 4, 2 1 2 1, 7 4, 2 2 1 2 2 2, 2 1 1 1 1 1 1, 2 1 1 4 2, 1 3 4 2 1, 1 1
8 1 1, 5 2 2 1, 2 1 3, 1 1, 1 7 4 1, 3 5 1, 3 1 1 3, 4 3 4, 3 1 1 3, 3 5 1, 1 7 4 1, 1 2, 1 1 1, 2 2 2, 2 1 1 2, 2 1 2 2, 3 2 1, 3 1 1, 4 1 2 2, 9 2 3
where the first line is the horizontal lines and the second line the vertical lines. When the parsing fails, you still get something like this in the output of the program. You can just copy paste the two lines in a file, edit it so that it matches the puzzle, then run:
start "" PixeloTools.GameSolver.exe myFile.txt

The file can also be in XML format, same as in the file the game loads. That's for Tamaii's benefit, mostly.

Let me know what you think in the comments below.

Saturday, May 23, 2015

TV Shows so shitty, that even I couldn't stand watching them

As some of you know, I am watching a lot of TV series, including cop and lawyer stuff, childish fantasy and sci-fi series, pirate and musketeer crap, etc. So when I say that I couldn't watch something, you should heed me. There are also some shows that I actually can stand watching, but barely, usually using the fast forward to watch an episode in ten minutes or less.
Here is a list of just such shows:
  • Lost - I think it all started with Lost. After a brilliant pilot episode, it got into 1001 Arabian Nights territory and became ridiculously unwatchable. While it wasn't a truly bad show (some people actually enjoyed all 6 seasons) I couldn't watch it more than 2 seasons. Calm down, the rest of the shows will be more recent.
  • The Messengers - every once in a while the American Bible thumpers convince producers to create religion based TV series or movies. While every possible superstition has and will be used in films, the Christian ones are standalone - in their own cinematic universe, if you will - and take themselves seriously every time. Messengers is one of these, and I couldn't watch more than 2 episodes before I gave up. It's (again) about the upcoming apocalypse and end of days and these 8 idiots are given superpowers to convince God that we are still worth it, while Satan goes around doing silly things. It was not only about the most tabloid part of (a part of) the bible, but it was acted and scripted badly. Ugh!
  • Dig - This relates more to Hebrew religion and feels like a TV series take on the Da Vinci Code, with clones of Jesus (I think), FBI policemen running around in Israel doing whatever they feel like, shady cabals and shady opposing cabals. It could have been interesting and I admit that I was drawn into it by the acting and production values. Unfortunately the script was a convoluted mess that had no way of improving. They never learn to ease into it, present a little bit of the story, add another element later on, make people feel rewarded for continuing to watch the series. Anyway, four or five episodes in, I gave up.
  • iZombie - Oh, you have to love this: young blonde accidentally gets turned into a zombie and gets a job at the morgue in order to have access to a fresh supply of brains. While consuming said brains, she gets visions of what the brain's owner had lived and thus helps a police detective to find out who murdered them. It combines police procedurals with zombies, comedy, romance and drama. Talking of messes, this is as messy as they come. I watched a few episodes before I slapped myself "what the hell are you doing watching this swill?"
  • Eye Candy - A darker show about a girl using her hacker tech skills to try and catch the serial abductor and killer of young women, including her sister. Yet the killer is just as able and a game of cat and mouse ensues. It started well, then went into pop culture territory. Besides, she continues on only because the killer refuses to murder her and get on with his job. Something about the way the film was made or the main actress or the ridiculous plot made it impossible for me to get past the second episode.
  • Allegiance - It is strange to put this series here, as I watched all the episodes so far. However, it was hope that kept me going, not the quality of the show. While it is about Russian spies in the US, living as a family, while their unknowing son is recruited into the FBI, while the antagonists are interesting, the story goes so much over the top that it becomes overcheesy. That means it starts as cheesy, then it starts to smell. Just do yourself a favor and do not watch it.
  • Suits - Yet another flashy show that I kept watching for no good reason. It is about lawyers. Young, cocky, caught in personal intrigues and competing with one archnemesis after another, while occasionally engaging in some silly romance. It makes no sense, although I have to admit that I liked the feel of the show. It just goes nowhere. They get the mood right, the actors, the characters, then they make a joke of the storyline and script.
  • Helix - Helix is the new Lost! It starts with a subject close to my heart: killer viruses, and then it crashes into la-la-land. Viruses that make people immortal and strong so they form a world controlling organization, CDC teams that act like they are terrorist splinter cells with no support, no rules, no communication, strains of the virus that turns people into The Strain like vampires, religious cults that are also genetic scientists living on their own island, etc. It starts as something, goes into something else, switches back. It's like someone was undecided what to write about and they wrote about everything then they just drew lines between the unrelated subjects and wrote a script. It is a complete and utter waster of time.
  • Elementary - I watched two seasons of this until I admitted to myself that there is no point in continuing. I like both Johnny Lee Miller and Lucy Liu, I love Arthur Conan Doyle's Sherlock Holmes character and so a modern Sherlock should have been something I would at least enjoy. Unfortunately, it shortly turns into another "gifted person helps the New York police" show and one can feel when the writers just completely stopped giving a damn, if they ever did before beyond pushing the pilot.
  • Scorpion - Talking about gifted persons helping the police, this had a promising concept: a genius young man creates a sort of a thinktank of gifted people then starts helping his FBI parent surrogate. While the characters were brilliant, the people writing their script obviously were not. It took a few episodes to completely fail and another ten for me to just give up hope.
  • The Librarians - God, what a complete and utter mess. A TV show spinoff of the ridiculous Librarian movies, it was completely stupid. I mean, the movies were masterpieces compared to the writing for this show. Stay away!
  • The Newsroom - This is a show that has ended and I watched all of its episodes. However, it was under duress. My wife liked it, but then I could see that she couldn't watch it anymore. I watched the last mini-season just in order to tell her it was not worth watching. It's about a news organisation that fights for its moral integrity. It would have been fun if not for the hyperactive writers that thought people could normally carry conversations like machineguns shoot bullets and the ridiculous romantic interludes that made no sense for anyone but a New Yorker raised on Woody Allen movies.
  • State of Affairs - I already ranted enough about this piece of propagandistic crap. The plot was stupid, the script was stupid, the actors couldn't give a damn. It's just bad!
  • The 100 - In theory I am still watching this young adult sci-fi TV show. In reality, I can't make myself start watching the second season. Why? Because the sci, as usual, gets completely negated by the fi. Add to this hormone induced behaviour of teenage kids and savage barbarians and you get a load of messy crap. It started really well, though. Too bad.
  • Extant - Another sci-fi show in which they dumped randomly whatever was hot at the moment: aliens, artificial intelligence, the Japanese guy from Helix, etc. Add to this the excruciating acting skills of Halle Berry and you get something unwatchable.
  • Stalker - This is one of the more interesting police procedurals. It involves a team that is tasked to handle stalkers. The new hire of the agency may be a stalker himself. The lead actors are people that I respect. However the story was so banal and lacking anything that showed any concern for the lives of the characters or victims, it was so formulaic that it ended up being something I regret having watched.
  • Arrow - OK, I am still watching The Arrow, but I do it on fast forward. It started as a fun comic book TV show with a vigilante dressed in green killing assholes with the use of arrows. It then started growing and expanding until it reached a full team of heroes, with impossible situations, history, skill learning curves, ridiculous villains. It culminated with John Barrowman having a line in the show in which he tells three other characters he is the better man for a job because he is the better at acting. Which is true, but it just shows that even the scriptwriters are tired of the mediocre talents in this mediocre show. I keep watching it because it is set in the same universe as The Flash, which is the better TV show, even if not by much.
  • Gracepoint - When Americans are redoing a show with the same script as the original show from a different country I snicker. Stoopeed Americaens, they can't read subtitles. But Gracepoint upped the ante: they redid Broadchurch, a British TV show. Not only that, they cast the same actor as the main character. I love David Tennant, but I can't possibly condone watching this. Anyway, it was cancelled after a season, and then Broadchurh continued for a second season, also starring Tennant.
  • Sleepy Hollow - An entire TV series based on the Legend of the Sleepy Hollow. Starring a colonial Mason that fought for and knew the forefathers of the United States, resurrected in our times and fighting along a black female police officer to stop the End of Times. What can possibly go wrong here? Yet it wasn't that clear cut. The comedy was fun, the original drama had impact, the romance was believable and John Noble, like in Fringe, was awesome. However the storyline, like in Fringe, descended into a chaotic mess that makes no sense and no one could ever care for. Too bad, since it wasn't a complete waste of time, especially at the beginning.
  • Madam Secretary - Just as with State of Affairs, I've ranted enough about it. It's a pure propaganda piece, with no artistic value whatsoever.
  • Haven - It started as a nice supernatural police procedural with a sexy FBI agent coming in a town where people get dangerous "troubles" which act like superpowers out of control. I kept waiting for it to get anywhere, instead it just went more and more overdramatic and adding storylines that made no sense and in the end turned into a supernatural 90120. Too bad, since it started well and has some actors that I really like.
  • Forever - Forever boring, this show is about a British doctor who never ages and, whenever gets killed, he finds himself alive and well in a nearby body of water, completely naked. And what does he do with that? He helps the police, as a forensic medic. Just as a general rule: avoid the gifted people helping the police catch bad guys shows. They never end well and they are as well propaganda pieces.
  • The Lottery - Badly made show. Bad casting, bad acting, bad scripting. The plot: people stop having children, embryos stop getting fertilized and a woman doctor finds a way. Obviously, the powers that be want to control this in a market of dwindling supplies. Could have been nice, probably, although shows about children and parently feelings never do well in the long run, but it was pure shit from the beginning.
  • Taxy Brooklyn - Someone tried to combine the French Taxi storyline with a police procedural. Sexy ass police detective, a Black French taxi driver, some intrigue and of course the obligatory connection with an old case involving a relative. But it was bad. The actors did what they could with lazy writing, but they couldn't save it.
  • The Witches of East End - Some of the most beautiful women in acting get together with ridiculously good looking men in order to act a really stupid Dynasty-like plot. Just like similarly afflicted The Originals, it is a complete waste of time, unless you are a hormonal teenager and you need something to jack off to.
  • The Leftovers - Trying to express the horrible anguish of being among the ones left on Earth after 2% of the population randomly disappears. Of course, it hints of another Bible concept: the Rapture. It felt to me overly dramatic, with people just doing stuff for no good reason and overly depressing as well for equally no good reason. It seemed to be well acted, but I just couldn't watch it.
  • Resurrection - an American remake after Les Revenants, it missed the point completely. Ended up being this drawn out mystery that led nowhere, with really unsympathetic characters and without any of the real tension from the French series.
  • Da Vinci's Demons - I know, I am still watching it, but not because it is worth watching. It is about a young Da Vinci who does everything from saving Florence to saving Italy and then going to the Americas (way before it was discovered) to look for clues about his mother, who was there previously. It is a fun to watch show, but it really goes nowhere. I liked the acting and the production values, though.
  • Crisis - An intriguing concept: some very resourceful people kidnap a lot of high profile adolescent kids, including the son of the president of the United States, then they use the parents to achieve their goals. While the show was well done, the story was just a lazy fantasy trying to surf the edge of suspension of disbelief. Again, it goes nowhere.
  • The Tomorrow People - An American take of an old British children's TV show that wasn't good to begin with, it only served to promote certain actors to better TV shows. It used the common tropes: child with mysterious father who dies doing something awesome, mother unknowing, uncle being involved somehow, evil organizations, rebellious teenagers fighting them, etc. It just collapses under the ridiculous ideas that they introduce to appease a boring viewership.
  • The Originals - Some spin-off from a vampire show that I never watched, it is about pompous asses acting even more pompous because they are the original vampires. Werewolves and witches are thrown in the mix, but the only worth mentioning attribute of the show is the pompousness. Which is annoying. So don't watch it.
  • Killer Women - Trying to get on the diversity bandwagon and get some easy money, it was supposed to be a police procedural with tough women. Didn't pan out. Was cancelled.
  • Bitten - One of the most beautiful actresses around is a werewolf. Instead of being about her biting more than she can chew (heh heh), it is about another clan/royalty obnoxiousness, while the world is blissfully unaware and rivals are trying to take power or kill them or whatever. Do you know why people don't officially acknowledge vampires and werewolves? Because they act all annoying and overbearing while being completely idiotic. Nobody wants to know these people!

OK, as an interesting experiment on my preferences vs the public preferences, I will tell you how many of the shows above were cancelled and how many were not. Ready?

Completed (meaning not cancelled, but finishing their story): 2
Cancelled: 16
Still Running or undecided: 15

So there you go, studios think 50% of the shows that I can't stand should be running and they are basing their decisions on public choice. What do you guys think?

Monday, May 18, 2015

Encapsulating indexes or creating a database that allows for fast inserts and updates as well as indexed queries

Relational databases have the great advantage of being able to use indexes. These are constructs that trade space and the speed of inserts and updates for query speed. This introduces a problem: what do you do when you have a high input and high output application? How can you make your database fast for both changes and queries?

The traditional solution is a write-database and a read-database. There are background services that ensure that the read-database is updated as fast as necessary with the new data, most of the time also doing extra computation and caching for the information one needs extracted. OLAP systems are a common solution, where the aggregated data required by various reports is computed and stored, ready to be read by your applications. I am not saying this is a bad idea, in fact I am saying this is the best idea for this scenario. The problem that I have with it is that you can hardly automate the process. You need to know what you want to read, you need to write the software to create the data and aggregate it into what you need.

So I decided to try to build a system that obeys the following rules:
  1. The speed of insert and update operations needs to be unhindered by indexes. Indeed, changes to the original data should be avoided.
  2. The select operation need to benefit from the indexing system.
  3. The system must be able to determine by itself the data structures needed to cover the first two rules.
  4. Optionally, there should be a way to encapsulate this new behaviour into a separate data structure from the original database.

In order to insert and update as fast as possible, I needed tables that only had primary keys, identity integers rather than uniqueidentifiers, as they lead to fragmentation of clustered indexes. In order to not only index the columns that are used in where and join conditions, but also encapsulate the behaviour in some other data structure, I decided to create shadow tables with the columns that I needed for querying. These tables I would then index in order to improve selects. The connection between the original insert table and the select table would be made via primary keys and the synchronization between the two types of tables would be done in the background, whenever needed. Best of all, analysis on execution plans could automatically determine the columns needed for this system and create the tables and indexes required, then suggest improvements on the observed queries.

In order to test my theory I created the following tables:
  • OriginalTable - with a primary key identity ingeniously called Id and two other columns called Latitude and Longitude, containing random decimal(18,6) values
  • LocationIndexTable - with a refId integer primary key column that links to the OriginalTable Id - without being a foreign key - and two Latitude and Longitude columns that are indexed by the same non clustered index
  • LocationIndexTable2 - with a refId integer primary key column that links to the OriginalTable Id - without being a foreign key - and a locId integer column that links to another table called LocationTable and has its own non clustered index
  • LocationTable - with a primary key identity column and Latitude and Longitude columns. I know that this looks identical to LocationIndexTable, but this is the more complex case where there are multiple records with the same location. LocationTable holds the distinct Location and Latitude values
  • LocationIndexTable3 - with a refId integer column that links to the OriginalTable Id and is indexed by its own nonclustered index - without being a foreign key - and two Latitude and Longitude columns that are indexed by a clustered index

With a number of 77179072 original table rows, I attempted the queries for each case, careful to clean the cache and memory buffers before that. Here are the results:
  • SELECT count(1) FROM OriginalTable WHERE Latitude BETWEEN 45.5 AND 46 AND Longitude BETWEEN 8.5 AND 9 - no indexes whatsoever. Result: 30 seconds
  • SELECT count(1) FROM OriginalTable ot INNER JOIN LocationIndexTable lit ON lit.RefId=ot.Id WHERE lit.Latitude BETWEEN 45.5 AND 46 AND lit.Longitude BETWEEN 8.5 AND 9. Result: 17 seconds
  • SELECT count(1) FROM OriginalTable ot INNER JOIN LocationIndexTable2 lit2 ON lit2.RefId=ot.Id INNER JOIN LocationTable lt ON lit2.LocId=lt.Id WHERE lt.Latitude BETWEEN 45.5 AND 46 AND lt.Longitude BETWEEN 8.5 AND 9. Result: 41 seconds
  • SELECT count(1) FROM OriginalTable ot INNER JOIN LocationIndexTable3 lit ON lit.RefId=ot.Id WHERE lit.Latitude BETWEEN 45.5 AND 46 AND lit.Longitude BETWEEN 8.5 AND 9. Result: 22 seconds

Unexpectedly for me, the most comfortable solution also seems the faster. Indeed, one issue is that I didn't have duplicate location data in the database, so there was no advantage in adding a new table to link locations to the original table. That means that in cases where the indexed data has many duplicates, it might be advantageous to experiment with a "distinct" table, although indexing should take this case into account, as well. The clustered index is slower than the unclustered one, that was a surprise. Also, the indexing just made the query twice as fast - I had expected more. Of course, all this benchmarking was done after deleting the cache and buffers with the commands DBCC FREEPROCCACHE; DBCC DROPCLEANBUFFERS. It is interesting to see how fast they queries go without this clearing. The first unindexed query takes 3 or 4 seconds, while all the others take less than 2.

There is one more thing that needs to be addressed: moving these tables into another database. Are the indexes just as fast? They should be, but we must test interdatabase communication. This would allow to move the entire system outside a target database, truly encapsulated, really adding value to a database that remains unaffected. My tests show the following results: 28, 18, 29 and 18 seconds, respectively. Yes, you saw that right, the speed of the joins when the indexed databases are in another database on the same server seems faster! Just to make sure I reran the original tests on the same database and I got approximately the same results: 29,19,43 and 24, respectively. The only thing I didn't try (and I don't intend to) is to create primary-foreign key associations, as this means modifying the original tables, something I wish to avoid.

So, after all this I believe my idea has been validated. A lot more work has to be done in order to automate this system, but at least a no-effort manual solution is possible. Let's recap:
  1. The speed of row inserts and updates remains unchanged: the only index is the original primary key identity integer key that should always exist in a table anyway.
  2. The speed of selects is improved by creating tables that have an integer primary key that links to the original table, and only the columns used in queries, over which indexes are created.
  3. Queries can be used to determine the columns needed to index. Even better, computed columns can be used instead of the original columns, which adds a little more performance.
  4. Encapsulation is achieved by not only creating other tables for reading, but also moving these tables into another database, after which, unexpectedly, the performance is even better.

The end result is a system similar to indexing, but which takes space in another database and which is updated on demand, not when the system deems it necessary. Any SQL statements that worked before will continue to work unaffected, but faster alternatives for selects will become available. Overall, I am very satisfied, although I had expected better performance improvements than observed.

Friday, May 01, 2015

The spy TV shows running right now

There are these time periods in which movies and TV shows start to follow a common theme. I don't know exactly why that happens, but I suspect it's about Hollywood corporate managers stealing everything that seems to work and getting to copy each other. We seem to be living under the sign of the Spy right about now and in this blog post I will be discussing the following TV series (listed in alphabetical order):
  • Allegiance - a show about a CIA analyst who is the unknowing son of a pair of Russian KGB spies. He is also a kind of idiot savant which can remember a lot of things and make connections that no one can. It gets really hard to believe soon as it mashes up family drama with evil SVR (former KGB) machinations, CIA and FBI operations, traitors and double agents, hired assassins, plots to bring down the United States, and so on. It's one of the lesser spy shows and I don't recommend it.
  • The Americans - I believe this is the gem of the lot. The protagonists are two fake Americans, in reality KGB agents. They live like an American couple, with the mandatory two children and their neighbor is an FBI agent in the very taskforce that is supposed to catch people like them. The acting is very good and the show is a serious one, going only occasionally over the top. Also the lovely Keri Russell makes it very appealing as well :)
  • The Assets - The Assets has ended after a single season, with the successful completion of the main plot. It was a strange mix of really good acting and rather weak characterisation. It followed the story of Aldrich Ames, a famous disgruntled CIA operative who sold American secrets to the Russian KGB. As it is a true story, the entire plot was not terribly captivating, but the problem for me was with the characters.
  • Turn - About the spy ring of the American rebels against the British during the Washington era, it is an intricate and interesting story. I like most of the characters, even the villains, which is rare in films these days. I particularly like Burn Gorman in this, even if he plays this British buffoon that understands nothing of what is going on around him.
  • X Company - Set during World Word II, it is about a company of spies working in the German occupied territories. Their newest recruit is a guy who can remember everything he sees, but is a little weird. Sound familiar? Heh

From the list above, The Americans shines bright. Good acting, good directing, but also a dedication to show the political and social issues of the time. From the ones here, it is the only one who I feel can teach one something about history and about human nature in general. Turn is also rather good, but it is too reliant on a number of characters that keep ending up crossing paths in implausible situations, and many times goes a little over the top. The actors are really good in this one, though, and it is worth watching for sure. X Company is more like the typical WWII movie, with the evil Germans and the brave resistance fighters. They do attempt occasionally to enter the grey areas, like good German officers, conflicted collaborators or remorseful secret agents who have to compromise between human values and the mission, but it is not enough. When the beautiful team leader played by Evelyne Brochu reveals that she is half Jewish and the whole story goes into Holocaust territory you can feel the show going south. The Assets was brave in not employing only beautiful actors. The main character was ugly and weak and constantly fearing discovery, hard to empathize with. But so were the people who were trying to catch him, led by an obsessive woman that acted like a blood hound the entire time. Not being able to make an emotional connection to any of the characters hurt the success of this otherwise interesting story. This leaves Allegiance, which feels a lot like a fairy tale combined with a police "special" procedural (you know the type: special person helps police solve crimes) which incidentally features spies. While I cannot really recommend X Company, Allegiance is the only one that I would recommend against.

Worth mentioning is that three of these shows are based on real events. The Assets follows the exploits of Aldrich Ames, X Company is loosely based on Camp X and Turn links to the story of the Culper Ring.

That's it for pure spy shows. I am not putting in this list shows like Homeland or Legends, which also show undercover operatives, but are not specifically aimed at spying as the main subject of the plot. Tell me what you think.