4 months Ago

Optimizing data access for high-latency networks: part III

Published by marco on in Programming

 In the previous article, we partially addressed a performance problem in the calendar of Encodo’s time-tracking product, Punchclock. While we managed to drastically reduce the amount of time taken by each query (>95% time saved), we were still executing more queries than strictly necessary.

The query that we’re trying to optimized further is shown below.

var people =
  Where(p => Session.GetCount(p.TimeEntries.Query) > 0).

This query executes one query to get all the people and then one query per person to get the number of time entries per person. Each of these queries by itself is very fast. High latency will cause them to be slow. In order to optimize further, there’s really nothing for it but to reduce the number of queries being executed.

Let’s think back to what we’re actually trying to accomplish: We want to get all people who have at least one time entry. Can’t we get the database to do that for us? Some join or existence check or something? How about the code below?

var people = 
    Join(Person.MetaRelations.TimeEntries, JoinType.WhereExists).

What’s happening in the code above? We’re still getting a list of people but, instead of manipulating the related TimeEntries for each person locally, we’re joining the TimeEntries relation with the Quino query Join() method and changing the join type from the default All to the restrictive WhereExists. This sounds like exactly what we want to happen! There is no local evaluation or manipulation with Linq and, with luck, Quino will be able to map this to a single query on the database.

This is the best possible query: it’s purely declarative and will be executed as efficiently as the back-end knows how.

There’s just one problem: the WhereExists join type is broken in Quino 1.11.

Never fear, though! We can still get it to work, but we’ll have to do a bit of work until the bug is fixed in Quino 1.12. The code below builds on lessons learned in the earlier article, Mixing your own SQL into Quino queries: part 2 of 2 to use custom query text to create the restriction instead of letting Quino do it.

var accessToolkit = new PostgreSqlMetaDatabase().AccessToolkit;

var query = Session.CreateQuery<Person>();
query.CustomCommandText = new CustomCommandText();
    "EXISTS (SELECT id FROM {0} WHERE {1} = {2})", 
var people = Session.GetList<Person>(query);

A look at the statistics is very encouraging:

 One query for people; one for time-entries

We’re down to one 29ms query for the people and an even quicker query for all the relevant time entries.[1] We can see our query text appears embedded in the SQL generated by Quino, just as we expected.

There are a few other security-related queries that execute very quickly and hardly need optimization.

We’ve come much farther in this article and we’re almost done. In the next article, we’ll quickly clean up a few other queries that are showing up in the statistics and that have been nagging us since the beginning.

[1] The time-entry query is not representative because my testing data set didn’t include time entries for the current day and I was too lazy to page around to older data.

World Cup 2014 group phase

Published by marco on in Sports

Brazil vs. Croatia (3–1)
Brazil took a little while to get going but showed flashes of brilliance. They got lucky that the momentum didn’t go the other way before getting some fortuitous calls. Brazil almost always gets the benefit of calls. I’m on fence as to whether it’s an in-built, explicit prejudice, an unconscious tendency on the part of referees in awe of their reputation or just a natural outgrowth of their playing so well.
Mexico vs. Cameroon (1–0)
Can’t remember. I think Mexico played pretty well.
Spain vs. Netherlands (1–5)
The Netherlands was on fire that day, with the Arjen Robben/Robin van Persie show delivering four goals for the old-timers. Spain was an utter mess and deserved the shellacking that they got. I, for one, was happy to see that we wouldn’t be treated to a Spanish march to the finals on the back of 0–0 ties and 1–0 wins and 7 million passes.
Chile vs. Australia (3–1)
Despite occasional flashes of brilliance, Australia was definitely outclassed by a more solid Chile. I don’t really remember many details though.
Colombia vs. Greece (3–0)
Colombia showed a class that Greece couldn’t match. Not much more to say about this one.
England vs. Italy (1–2)
Italy and England put on a lovely show, with both offenses producing a lot of imaginative chances and both teams pressing until the very end. Though England played better than I’ve honestly ever seen them play, Italy totally deserved to win.
Uruguay vs. Costa Rica (1–3)
Uruguay started off strong and was upset by Costa Rica, which took the last three goals of the match. I only saw the first half.
Côte d’Ivoire vs. Japan (2–1)
I didn’t see this match, but read that Drogba’s presence on the field inspired his team to two goals in the second half to take the game from Japan.
France vs. Honduras (3–0)
France played a strong game—much stronger than their appearance four years ago in Africa—and Honduras was clearly outmatched. Honduras responded by fouling quite a bit and even ended up losing a man early in the second half.
Switzerland vs. Ecuador (2–1)
Switzerland played with more heart than I’ve seen them play in a long time. They didn’t always play well, but they kept trying hard, kept running hard and even scored the game-winner almost three minutes into extra time in the second half.
Argentina vs. Bosnia and Herzegovina (2–1)
Argentina played an utterly horrible first half, missing passes all over the place and getting a lucky goal. In the second half, Lionel Messi did an utterly brilliant give-and-go, passed three more defenders and bounced a laser off of the opposite post to take Argentina to 2–0. Bosnia clawed a goal back but Argentina had by then organized their defense and held on to the victory.
Germany vs. Portugal (4–0)
Germany utterly dismantled a bewildered Portugal, which could barely put together two passes to say nothing of any offense worth mentioning. Germany had their third goal in extra time of the first half and it was all over but the crying. Speaking of which, Christiano Ronaldo started off strong but his expression eventually descended into his “gonna cry” face, which became permanent when Pepe got his criminal ass thrown off the field for an aggressive and dead-ball head-butt on Müller, who was down after Pepe had already bitch-slapped him to the ground. The second half wasn’t worth mentioning other than seeing Müller round out his hat trick.
Iran vs. Nigeria (0–0)
A boring and terrible game of which we will not speak again. Not too many fouls, but neither team could settle the ball down to get down to the business of actually building a dangerous offense.
Ghana vs. United States (1–2)
Clint Dempsey scored within 30 seconds for the US and that was pretty much all we heard from the US offense for a long time. Ghana, meanwhile, dominated possession and offensive chances and inventiveness, but couldn’t hit the broad side of a barn. The US keeper was tested a few times, but never really brought to desperation. Ghana finally scored to even things up at 80 minutes but the U.S. capitalized on a poorly defended corner to take the lead again. Summary: a hapless Ghanian offense produced a single goal book-ended by U.S. goals.
Belgium vs. Algeria (2–1)
Not many chances on either side in a quiet game interrupted by a shockingly stupid foul in the penalty box by Belgium. An Algerian penalty-kick goal soon followed. I missed the second half, where Belgium rallied with two strong goals.
Brazil vs. Mexico (0–0)
A well-played and tactical but still not uncreative match. Mexico had more than its share of chances, forcing Brazilian goaltender Júlio César to make some spectacular saves. On the other end, though, the goaltending magic sparked even more brightly, with Mexican goaltender Guillermo Ochoa making nearly inhuman saves to maintain the shutout. There were occasional glimpses of Brazil’s style, but only Neymar really shone, as well as perhaps Paulinho. The tie was well-deserved, but there could have easily been goals on either side, if it hadn’t have been for the amazing goal-tending.
Russia vs. South Korea (1–1)
Both sides have a precise midfield game, a strong and well-organized defense and a somewhat ponderous offense (at least for the first third). It kind of felt like watching the FIFA 2015 AI play against itself. Russia became a bit more dangerous at the end of the first half and the beginning of the second half but nothing came of it. South Korea woke up a bit more in the second half as well. Both goalies had their problems holding on to the ball. The Russian keeper Akinfeev just totally flubbed a save and spilled it into the net (Kommentator ZDF[1]: “der ist der Depp des Tages. Ich will nicht despektierlich sein aber man kann das nicht anders bezeichnen”). It looked bad for Russia until substitute Kerzhakov brought a lot of energy to the game and was rewarded for it. From that point on, Russia dominated and had a lot more chances.
Netherlands vs. Australia (3–2)
Australia played better than expected and Holland took a while to find their rhythm. Still, Robben delivered a goal to take the lead, but Cahill equalized for Australia pretty much immediately. Australia got a weak penalty and Van Persie equalized, again almost immediately. DePay took the lead for Netherlands. Australia sought the equalizer until the very end, but Holland had the majority of chances and offense. Australia have a long flight ahead of them.
Spain vs. Chile (0–2)
Spain is still trying to find its rhythm. Initially, the Chileans were buzzing like bees and had the majority of the chances and long passes. Spain stuck to short passes (surprise!) but looked very disorganized, especially at the back. What a goal by Chile! Three beautiful passes and a falling-down finish by Vargas gives Chile the lead. Chile goes up 2–0 at the end of the first half and Spain is starting to panic. Spain takes some ugly fouls but manage to get it back under control, although Chile is still not giving them control of the midfield. Chile continues to build offense while Spain makes some very good attempts on set pieces. It is Chilean goaltender Claudio Bravo’s day, though, and Spain is going home.[2]
Cameroon vs. Croatia (0–4)
Cameroon started off with some very nice offensive flourishes but Croatia went against the run of play early and cashed in on a nice pass. By the middle of the first half, Croatia had established itself in the midfield and Cameroon was less on the offense than at the beginning. A shockingly stupid retaliation foul at the end of 45 minutes for Cameroon results in a red card. Croatia didn’t wait long in the second half to take advantage and pushed it to 2–0. They kept on coming and were at 3–0 at 60 minutes. A lot of the Cameroonians looked like they were going to cry. They still had flashes of offensive brilliance right up until the end, but they were too tired from playing a man down for an entire half to get an Ehrentor.[3]
Colombia vs. Côte d’Ivoire (2–1)
Côte d’Ivoire carried a lot of the play but couldn’t make anything of its chances. Colombia took advantage of some serious organization problems. After going down 2–0, Gervinho made a lovely individual effort to get past the Colombian keeper. They would keep trying heroically to get the equalizer. Colombia continued to probe and cause problems at the back, but weren’t able to score again. They also dawdled disgustingly at the end of the game, pissing away the last eight minutes of the game in a skilled display of time-wasting that even the referee was helpless to stop.
Uruguay vs. England (1–0)
Both teams came out with both barrels but Uruguay slowly but surely took control and put away an absolutely lovely header. In the second half, both teams came out storming again and had excellent chances in the early minutes. Poor Wayne Rooney seems to be absolutely cursed. Until, finally, in the 75th minute, he managed to score the same kind of goal he always scores: he shoveled it in from 2 meters out after Sturridge did all the work of getting the ball there. But even the German commentator falls all over himself, talking about Rooney’s “klasse”, which remains an utter mystery to me. Suarez, on the other hand, makes his own goals and fires them into the back of the net. Maybe there’s some room on the Spanish plane for England.
Honduras vs. Ecuador (1–2)
This was an exciting match with a lot of offense from both sides. The action roared back and forth across the field with Ecuador seeming to get the upper hand by the middle of the first half. Still, Honduras struck first during a collapse of the Ecuadorian defense. This woke up Ecuador, which redoubled its efforts and was nearly immediately rewarded with its own goal. The match continued at a high level of play and may be one of the more exciting matches of the tournament. Ecuador had a nice header off of a set piece to take it to the final score of 2–1.
Argentina vs. Iran (1–0)

A not uninteresting match with Iran playing Argentina to a standstill for the first half, stifling all of its chances and putting Argentina on the back foot. Argentina seemed out of ideas offensively and less organized than they needed to be at the back, but Iran couldn’t figure out how to punish them for it. As the second half unfolded, you could see Iran slowly gaining confidence, realizing that they were playing Argentina on an even footing and weren’t even needing a lot of luck to do it. Iran had the best chances and they took their confidence to the midfield, where they even managed to outdribble the fabled Argentinians a few times.

Towards the end of the match, Argentina had the clear upper hand in the midfield, but was still not coming up with any big ideas at finishing time, partly because Iran’s air game was much, much better. Iran tried to break out a few times, but had mostly collapsed to a turtle shell by then and were playing for the tie. There was an exciting attack at the 85th minute by Reza but the Argentinian keeper did well to parry it. Four minutes of extra time: no way to explain that but that they wanted to make sure that Argentina gets enough time to score. And score they did, with Messi firing a beautiful bender at 30 seconds into extra time. Iranians fans will still be pleased at how well their team played; Argentinians will be happy with the win, but justifiably worried about their team’s overall performance.

Germany vs. Ghana (2–2)

Germany had a lot of early chances and seemed to have Ghana bottled up in the half for a while, but Ghana exploded out of their half in the 30th minute with a few very dangerous attacks. A few minutes later and it was Germany’s turn, but the Ghanian goaltender was up to the task…and Germany was shooting almost directly at him. The first half ended with both teams having played each other to a standstill, each having had some good chances, but no goals.

In the second half, Germany lobbed in a beautiful ball and Götze headed/kneed it in. Ghana didn’t hang their heads and responded three minutes later with a beautiful header of their own. And then Ghana strikes again with a beautiful through pass to a strong finish! And Germany brings on Klose, who finishes a Schweinsteiger header with perfect positioning! And it’s 2–2 and it’s a wide-open game! They’re flying up and down the field like they’re playing basketball! What a game! Ghana trembles at the German offense and vice versa. Watching on SAT-1 with a German commentator is fantastic. He’s so excited but very fair, just happy to be part of such a good game. The US was damned lucky that a different Ghana showed up for their game; this one is playing the best of Germany to a standstill. Best game of the tournament so far.

Nigeria vs. Bosnia and Herzegovina (1–0)
I watched some of this game, but fast-forwarded a bit because I had places to be. The teams looked relatively evenly matched, with Bosnia able to get the ball to the Nigerian net but not really even get close to scoring—other than one goalpost, I saw no finishing skill whatsoever on that day. Nigeria was similar with the exception of one right-wing advance to a cross in front of the net that was hammered home for the sole goal.
Belgium vs. Russia (1–0)
I didn’t see most of this match, but the replay I fast-forwarded through showed a match that remained scoreless until the 87th minute. Just before the goal, the announcer on ZDF said that “at least we’ve had three minutes of decent football” and, after the goal had been scored, he described it as Belgium “capitalizing on the only good pass they’d made all game”.
South Korea vs. Algeria (2–4)
Though Korea made a few good attacks , particularly Son, Algeria soon took the upper hand and capitalized on a wonderful individual effort and a nice header off of a corner kick. They followed up a few minutes later with a lovely long ball to a cross to an easy goal. The German commentators were quite harsh with the Korean team: they said not only that the Algerian team was a class better than Korea, but that Korea might be playing the worst in the tournament. But Korea managed to pull themselves together and Son earned his reputation with a nice turnaround and quick strike to claw back to 3–1. The goal would spark their team awake—and back to life. Korea showed much more class in the second half, at times dominating the midfield even if not exactly producing too many dangerous chances. But they flew too high and played too far forward, opening up the back for an eager Algeria, which made a World Cup goal look easy. Korea would take advantage of disorganization in Algeria’s defense to get one more goal, but that would be it for them.
USA vs. Portugal (2–2)

Portugal establishes themselves as the better team in the beginning, taking the early lead in the 7th minute. The USA continued to make use of their fast forwards to make dangerous stabs toward the net, but nothing that really made the Portuguese keeper sweat. By the middle of the first half, the US was pressing far forward and Portugal seemed to just be weathering the storm. The US had some dangerous and very hard shots from outside that got quite close. Toward the end of the first half, Portugal was able to take advantage of the more open defense to get a few good chances, but Tim Howard in goal stepped up in a big way to keep the game close.

The US had a huge chance in the 55th minute, but Bradley was too careful with the finishing touch. Despite the Portuguese keeper Beto having already been beaten, he played right up the middle of the goal and into the leg of a defender. And then, out of nowhere, Jermaine Jones rips a bending laser just inside the far post: Beto didn’t even have time to move. Portugal may come to regret having tried to sit on a 1–0 lead rather than trying harder for a bigger lead.

The US is playing with heart and very fairly, taking few fouls and only one yellow card so far. Portugal is getting a bit sulky, taking some ugly fouls and starting to dive in the box (no cards, though). Portugal settled down, took control of the middle field more and got down to work of playing actual football. Then the US took control again, built up and, in the 81st minute, blew apart the Portuguese defense and Dempsey put it away with his stomach for a 2–1 lead.

Portugal will, of course, get five minutes to try to prevent an early flight home. And they would get their goal with a really nice ball from the left to a header. The US was dead on its feet and gave up the goal with 25 seconds left in extra time.

Spain vs. Australia (3–0)
I didn’t watch this game.
Netherlands vs. Chile (2–0)
Holland continues to improve and had most of the good chances in this game. Robben continues to amaze with his inventiveness, strength and speed on offense. Even at the very end of the game, he had real wheels and produced a perfect cross in extra time for Memphis to knock in to finally crush Chile’s hopes of winning the group. Fun game.
Cameroon vs. Brazil (1–4)
Brazil comes out firing on all cylinders and Neymar in particular seems to be able to do what he wants where he wants. This finally pays off when the Brazilian left wing just seizes the ball, tears up the line and crosses to Neymar, who puts it away. The Cameroonian keeper did his best to keep it close during the onslaught. His teammates paid him back by copying Brazil’s goal from before, fighting the ball out of the corner and laying off a perfect cross. Neymar responded soon after with his second goal of the game—and fourth of the tournament. Brazil soon picked up another one to seal the win and then kept their promise to “get a lot of goals” with another.
Croatia vs. Mexico (1–3)
I didn’t watch much of this game, but the little I saw had Mexico getting the better chances, including one crossbar on a nice outside attempt. This would finally pay off late in the second half when Croatia’s defense wilted and Mexico profited three times in quick succession from the confusion. The third goal was less a result of collapse and more a failure to mark a man carefully, but it was a nice. sneaky goal. At that point, they needed one more to pull past Brazil to win the group, but Brazil scored soon after to take away that chance. And then Croatia got their Ehrentor in the 86th minute to settle things down a bit.
Italy vs. Uruguay (0–1)

Italy had the majority of the possession but didn’t really do anything with it. Uruguay needs a win; Italy is OK with a tie. Buffon didn’t see much, if any, work in this half. The statistics claim more shots on goal, but I can’t recall having seen anything dangerous.

The second half was a bit more interesting, but Italy was given a red card with about 30 minutes left. Jumping straight to red was far too harsh, but that’s how it is. No video replay, so no checking the ref’s initial reaction. Italy played quite strong with 10 men and Uruguay would have had the chance to do the same if anyone had seen Suárez take a bite out of Chiellini’s shoulder. However, no one saw it—despite the fact that he’s been caught doing it twice before, according to the German commentator—and Suárez got no card at all.

Two minutes later Diego Godín of Uruguay scored with his back on a corner kick. Italy put in a lot of effort and had Uruguay squeezed into one quarter of the field[4], but couldn’t score. Godín played a big role in preventing a goal too, so he’s probably the man of the match for Uruguay. As Buffon said, Italy could not find their offensive rhythm from the first game and went scoreless in the last two. As a colleague wrote, “Italy did not deserve to advance, but that ref is an ASS”. Italy is going home.

Greece vs. Ivory Coast (1–1)
Both teams played well, but I’m forced to admit that Greece had the better plan and better discipline, taking advantage of the Ivory Coast twice to seal their spot in the next round. Poor Didier is going home.
Colombia vs. Japan (4–1)
I didn’t see this match, just the goals.
Nigeria vs. Argentina (2–3)
Messi hammered home a rebound to put Argentina in an early lead in the third minute, but Nigeria responded in the fourth minute to even up the score. After some struggle in the midfield, Argentina grabbed most of the control and had most of the chances. Messi, in particular, is really finding his form and, after being denied at the corners by the Nigerian goaltender, he finally landed one just before the end of extra time in the first half to make it 2–1. The second half saw Nigeria pull even almost immediately. Argentina shrugged it off quickly and scored the game winner soon after. Nigeria would try many times, but wouldn’t find a finish. Good game from both sides.
Bosnia and Herzegovina vs. Iran (3–1)
I didn’t see this match, just the goals. The first Bosnian goal was flat on the ground, from way outside and just off the inside of the post. They would score another precision goal, then Iran pulled within one and Bosnia put it away soon after.
Honduras vs. Switzerland (0–3)
Switzerland came out swinging and got an early couple of chances with an excellent cross to Shaqiri, who was denied by the goaltender. In the sixth minute, though, Shaqiri blasted a beautiful left-footed bender just under the crossbar on the far post to put Switzerland in the lead. And then, just like that, Shaqiri was 2/3 of the way to his hat trick. It was 2–0 at the end of the first half. Shaqiri bagged his third of the match and Switzerland defended the 3–0 lead with the help of Diego Benaglio, who was back in his former form after the horrible game against France. Hopp Schwiiz!
Ecuador vs. France (0–0)
I didn’t see really watch this match. The little I saw showed France with a lot of chances, a lot of pressure and no goals.
United States vs. Germany (0–1)
The US played Germany quite well but Germany played the US badly. Both sides played quite carefully and weren’t very exciting. Both teams would progress to the next round because nothing exciting happened in the Portugal vs. Ghana game either.
Portugal vs. Ghana (2–1)
I didn’t watch much of this game, either. Portugal struck first, Ghana got a quick equalizer. Portugal took the lead again later. Both are still going home.
Korea Republic vs. Belgium (0–1)
Belgium got a late goal to go past Korea and seal their spot at the top of their group with 3 wins. It wasn’t hugely impressive, though.
Algeria vs. Russia (1-1)
Both teams played OK with Algeria really playing a level higher than expected. Russia took the lead, but Algeria picked up the pace and equalized on a corner. The tie was enough for them to move on to the next round with Belgium.

[1] So far, I’m glad that I switched to ZDF/German TV for my world cup coverage. They are so much more knowledgeable than the Swiss commentators—they know player names, histories and they are much more likely to call it as they see it. I haven’t tried British TV yet but know from other years that they are often quite rude and not very good. TSI/Swiss Italian TV was better than SFR/Swiss German TV but ZDF is still better IMHO.
[2] The only goal scored by a team that lost by a good margin. I don’t know that there’s a word for it in English.
[3] Not before they get a full six extra minutes to try to score another goal, though. It always seems like, when a favorite is losing, the extra time is more or less doubled. That was the case again in the Uruguay vs. England game, where England got a full five minutes to try to score an equalizer. Brazil also always seems to get a lot of time—even in the tie against Mexico, you know the five extra minutes were tacked on for Brazil’s benefit.
[4] Despite what that flaming idiot Oliver Khan says, who seemed to have prepared his match report before the game and spent the whole 90 minutes drinking and practicing his Arnold Schwarzenegger impression.

Optimizing data access for high-latency networks II

Published by marco on in Programming

 In the previous article, we discussed a performance problem in the calendar of Encodo’s time-tracking product, Punchclock.

Instead of guessing at the problem, we profiled the application using the database-statistics window available to all Quino applications.[1] We quickly discovered that most of the slowdown stems from the relatively innocuous line of code shown below.

var people = 
  Where(p => p.TimeEntries.Any()).

First things first: what does the code do?

Before doing anything else, we should establish what the code does. Logically, it retrieves a list of people in the database who have recorded at least one time entry.

The first question we should ask at this point is: does the application even need to do this? The answer in this case is ‘yes’. The calendar includes a drop-down control that lets the user switch between the calendars for different users. This query returns the people to show in this drop-down control.

With the intent and usefulness of the code established, let’s dissect how it is accomplishing the task.

  1. The Session.GetList<Person>() portion retrieves a list of all people from the database
  2. The Where() method is applied locally for each object in the list[2]
  3. For a given person, the list of TimeEntries is accessed
  4. This access triggers a lazy load of the list
  5. The Any() method is applied to the full list of time entries
  6. The ToList() method creates a list of all people who match the condition

Though the line of code looks innocuous enough, it causes a huge number of objects to be retrieved, materialized and retained in memory—simply in order to check whether there is at least one object.

This is a real-world example of a performance problem that can happen to any developer. Instead of blaming the developer who wrote this line of code, its more important to stay vigilant to performance problems and to have tools available to quickly and easily find them.

Stop creating all of the objects

The first solution I came up with[3] was to stop creating objects that I didn’t need. A good way of doing this and one that was covered in Quino: partially-mapped queries is to use cursors instead of lists. Instead of using the generated list TimeEntries, the following code retrieves a cursor on that list’s query and materializes at most one object for the sub-query.

var people = Session.GetList<Person>().Select(p =>
  using (var cursor = Session.CreateCursor<TimeEntry>(p.TimeEntries.Query))[4]
    return cursor.Any();

A check of the database statistics shows improvement, as shown below.

 Time-entry queries with cursors

Just by using cursors, we’ve managed to reduce the execution time for each query by about 75%.[5] Since all we’re interested in finding out is whether there is at least one time entry for a person, we could also ask the database to count objects rather than to return them. That should be even faster. The following code is very similar to the example above but, instead of getting a cursor based on the TimeEntries query, it gets the count.

var people =
  Where(p => Session.GetCount(p.TimeEntries.Query) > 0).

How did we do? A check of the database statistics shows even more improvement, as shown below.

 Time-entry queries with COUNTs instead of SELECTs

We’re now down to a few dozen milliseconds for all of our queries, so we’re done, right? A 95% reduction in query-execution time should be enough.

Unfortunately, we’re still executing just as many queries as before, even though we’re taking far less time to execute them. This is better, but still not optimal. In high-latency situations, the user is still likely to experience a significant delay when opening the calendar since each query’s execution time is increased by the latency of the connection. In a local network, the latency is negligible; on a WAN, we still have a problem.

In the next article, we’ll see if we can’t reduce the number of queries being executed.

[1] This series of articles shows the statistics window as it appears in Winforms applications. The data-provider statistics are also available in Quino web applications as a Glimpse plug-in.

It is important for users of the Microsoft Entity Framework (EF) to point out that Quino does not have a Linq-to-Sql mapper. That means that any Linq expressions like Where() are evaluated locally instead of being mapped to the database. There are various reasons for this but the main one is that we ended up preferring a strict boundary between the mappable query API and the local evaluation API.

Anything formulated with the query API is guaranteed to be executed by the data provider (even if it must be evaluated locally) and anything formulated with Linq is naturally evaluated locally. In this way, the code is clear in what is sent to the server and what is evaluated locally. Quino only very, very rarely issues an “unmappable query” exception, unlike EF, which occasionally requires contortions until you’ve figured out which C# formulation of a particular expression can be mapped by EF.

[3] Well, the first answer I’m going to pretend I came up with. I actually thought of another answer first, but then quickly discovered that Quino wasn’t mapping that little-used feature correctly. I added an issue to tackle that problem at a later date and started looking for workarounds. That fix will be covered in the next article in this series.
[4] Please note that cursors are disposable and that the calling application is responsible for cleanup. Failure to dispose of a cursor that has been at least partially iterated will result in an open connection in the underlying database providers associated with the query and will eventually lead to connection-pool exhaustion on those databases.
[5] Please ignore the fact that we also dropped 13 person queries. This was not due to any fix that we made but rather that I executed the test slightly differently…and was too lazy to make a new screenshot. The 13 queries are still being executed and we’ll tackle those in the last article in this series.

5 months Ago

Optimizing data access for high-latency networks: part I

Published by marco on in Programming

 Punchclock is Encodo’s time-tracking and invoicing tool. It includes a calendar to show time entries (shown to the left). Since the very first versions, it hasn’t opened very quickly. It was fast enough for most users, but those who worked with Punchclock over the WAN through our VPN have reported that it often takes many seconds to open the calendar. So we have a very useful tool that is not often used because of how slowly it opens.

That the calendar opens slowly in a local network and even more slowly in a WAN indicates that there is not only a problem with executing many queries but also with retrieving too much data.

Looking at query statistics

This seemed like a solvable problem, so I fired up Punchclock in debug mode to have a look at the query-statistics window.

To set up the view shown below, I did the following:

  1. Start your Quino application (Punchclock in this case) in debug mode (so that the statistics window is available)
  2. Open the statistics window from the debug menu
  3. Reset the statistics to clear out anything logged during startup
  4. Group the grid by “Meta Class”
  5. Open the calendar to see what kind of queries are generated
  6. Expand the “TimeEntry” group in the grid to show details for individual queries

 Time-entry queries are the problem

I marked a few things on the screenshot. It’s somewhat suspicious that there are 13 queries for data of type “Person”, but we’ll get to that later. Much more suspicious is that there are 52 queries for time entries, which seems like quite a lot considering we’re showing a calendar for a single user. We would instead expect to have a single query. More queries would be OK if there were good reasons for them, but I feel comfortable in deciding that 52 queries is definitely too many.

A closer look at the details for the time-entry queries shows very high durations for some of them, ranging from a tenth of a second to nearly a second. These queries are definitely the reason the calendar window takes so long to load.

Why are these queries taking so long?

If I select one of the time-entry queries and show the “Query Text” tab (see screenshot below), I can see that it retrieves all time entries for a single person, one after another. There are almost six years of historical data in our Punchclock database and some of our employees have been around for all of them.[1] That’s a lot of time entries to load.

 Query text for all time entries for one person

I can also select the “Stack Trace” tab to see where the call originated in my source code. This feature lets me pinpoint the program component that is causing these slow queries to be executed.

 Stack trace for superfluous time-entry queries

As with any UI-code stack, you have to be somewhat familiar with how events are handled and dispatched. In this stack, we can see how a MouseUp command bubbled up to create a new form, then a new control and finally, to trigger a call to the data provider during that control’s initialization. We don’t have line numbers but we see that the call originates in a lambda defined in the DynamicSchedulerControl constructor.

The line of code that I pinpoint as the culprit is shown below.

var people = Session.GetList<Person>().Where(p => p.TimeEntries.Any()).ToList();

This looks like a nicely declarative way of getting data, but to the trained eye of a Quino developer, it’s clear what the problem is.

In the next couple of articles, we’ll take a closer look at what exactly the problem is and how we can improve the speed of this query. We’ll also take a look at how we can improve the Quino query API to make it harder for code like the line above to cause performance problems.


Encodo just turned nine years old, but we used a different time-entry system for the first couple of years. If you’re interested in our time-entry software history, here it is:

  1. 06.2005—Start off with Open Office spreadsheets
  2. 04.2007—Switch to a home-grown, very lightweight time tracker based on an older framework we’d written (Punchclock 1.0)
  3. 08.2008—Start development of Quino
  4. 04.2010—Initial version of Punchclock 2.0; start dogfooding Quino

Question to consider when designing APIs: Part II

Published by marco on in Programming

In the previous article, we listed a lot of questions that you should continuously ask yourself when you’re writing code. Even when you think you’re not designing anything, you’re actually making decisions that will affect either other team members or future versions of you.

In particular, we’d like to think about how we can reconcile a development process that involves asking so many questions and taking so many facets into consideration with YAGNI.

Designing != Implementing

The implication of this principle is, that if you aren’t going to need something, then there’s no point in even thinking about it. While it’s absolutely commendable to adopt a YAGNI attitude, not building something doesn’t mean not thinking about it and identifying potential pitfalls.

A feature or design concept can be discussed within a time-box. Allocate a fixed, limited amount of time to determine whether the feature or design concept needs to be incorporated, whether it would be nice to incorporate it or possibly to jettison it if it’s too much work and isn’t really necessary.

The overwhelming majority of time wasted on a feature is in the implementation, debugging, testing, documentation and maintenance of it, not in the design. Granted, a long design phase can be a time-sink—especially a “perfect is the enemy of the good” style of design where you’re completely blocked from even starting work. With practice, however, you’ll learn how to think about a feature or design concept (e.g. extensibility) without letting it ruin your schedule.

If you don’t try to anticipate future needs at all while designing your API, you may end up preventing that API from being extended in directions that are both logical and could easily have been anticipated. If the API is not extensible, then it will not be used and may have to be rewritten in the future, losing more time at that point rather than up front. This is, however, only a consideration you must make. It’s perfectly acceptable to decide that you currently don’t care at all and that a feature will have to be rewritten at some point in the future.

You can’t do this kind of cost-benefit analysis and risk-management if you haven’t taken time to identify the costs, benefits or risks.

Document your process

At Encodo, we encourage the person who’s already spent time thinking about this problem to simply document the drawbacks and concessions and possible ideas in an issue-tracker entry that is linked to the current implementation. This allows future users, maintainers or extenders of the API to be aware of the thought process that underlies a feature. It can also help to avoid misunderstandings about what the intended audience and coverage of an API are.

The idea is to eliminate assumptions. A lot of time can be wasted when maintenance developers make incorrect assumptions about the intent of code.

If you don’t have time to do any of this, then you can write a quick note in a task list that you need to more fully document your thoughts on the code you’re writing. And you should try to do that soon, while the ideas are still relatively fresh in your mind. If you don’t have time to think about what you’re doing even to that degree, then you’re doing something wrong and need to get organized better.

That is, you if you can’t think about the code you’re writing and don’t have time to document your process, even minimally, then you shouldn’t be writing that code. Either that, or you implicitly accept that others will have to clean up your mess. And “others” includes future versions of you. (E.g. the you who, six months from now, is muttering, “who wrote this crap?!?”)

Be Honest about Hacking

As an example, we can consider how we go from a specific feature in the context of a project to thinking about where the functionality could fit in to a suite of products—that may or may not yet exist. And remember, we’re only thinking about these things. And we’re thinking about them for a limited time—a time-box. You don’t want to prevent your project from moving forward, but you also don’t want to advance at all costs.

Advancing in an unstructured way is called hacking and, while it can lead to a short-term win, it almost always leads to short-to-medium term deficits. You can still write code that is hacked and looks hacked, if that is the highest current priority, but you’re not allowed to forget that you did so. You must officially designate what you’re doing as a hot-zone of hacking so that the Hazmat team can clean it up later, if needed.

A working prototype that is hacked together just so it works for the next demonstration is great as long as you don’t think that you can take it into production without doing the design and documentation work that you initially skipped.

If you fail to document the deficits that prevent you from taking a prototype to production, then how will you address those deficits? It will cost you much more time and pain to determine the deficits after the fact. Not only that, but unless you do a very good job, it is your users that will most likely be finding deficits—in the form of bugs.

If your product is just a hacked mess of spaghetti code with no rhyme or reason, another developer will be faster and produce more reliable code by just starting over. Trying to determine the flaws, drawbacks and hacks through intuition and reverse-engineering is slower and more error-prone than just starting with a clean slate. Developers on such a project will not be able to save time—and money—by building on what you’ve already made.

A note on error-handling

Not to be forgotten is a structured approach to error-handling. The more “hacked” the code, the more stringent the error-checking should be. If you haven’t had time yet to write or test code sufficiently, then that code shouldn’t be making broad decisions about what it thinks are acceptable errors.

Fail early, fail often. Don’t try to make a hacked mess of code bullet-proof by catching all errors in an undocumented manner. Doing so is deceptive to testers of the product as well as other developers.

If you’re building a demo, make sure the happy path works and stick to it during the demo. If you do have to break this rule, add the hacks to a demo-specific branch of the code that will be discarded later.

Working with a documented project

If, however, the developer can look at your code and sees accompanying notes (either in an issue tracker, as TODOs in the code or some other form of documentation), that developer knows where to start fixing the code to bring it to production quality.

For example, it’s acceptable to configure an application in code as long as you do it in a central place and you document that the intent is to move the configuration to an external source when there’s time. If a future developer finds code for support for multiple database connections and tests that are set to ignore with a note/issue that says “extend to support multiple databases”, that future developer can decide whether to actually implement the feature or whether to just discard it because it has been deprecated as a requirement.

Without documentation or structure or an indication which parts of the code were thought-through and which are considered to be hacked, subsequent developers are forced to make assumptions that may not be accurate. They will either assume that hacked code is OK or that battle-tested code is garbage. If you don’t inform other developers of your intent when your’re writing the code—best done with documentation, tests and/or a cleanly designed API—then it might be discarded or ignored, wasting even more time and money.

If you’re on a really tight time-budget and don’t have time to document your process correctly, then write a quick note that you think the design is OK or the code is OK, but tell your future self or other developers what they’re looking at. It will only take you a few minutes and you’ll be glad you did—and so will they.