Should we invest time in non-functional requirements or new features? Deciding between these priorities isn't always easy. In our opinion, continuous improvement of time cockpit's performance is important. It has to go side by side with new features. In August we focused on possible improvements in our database access layer. You will see first positive results in the upcoming September release and even more in the next one scheduled for October. In this blog article I describe what we changed.
Note that this text lifts the curtain. It lets you take a look at what happens in time cockpit behind the scenes. Therefore it assumes some knowledge about databases in general and SQL in particular.
Time Cockpit's Sophisticated Query Generation Engine
One of the key differentiators of time cockpit is its extensible data model. Without programming, you can add tables, fields, relations, validation rules, etc. Behind the scenes, time cockpit is built on top of our Cockpit Framework (aka CoFX). It contains the engine that manages the customer-specific data model. Additionally, it is responsible for interacting with the underlying database (Azure SQL Database when working online, SQL Compact Edition when working offline). This includes the generation of SQL SELECT statements.
As you might know, time cockpit comes with its own domain-specific query language called Time Cockpit Query Language (aka TCQL). If you are not familiar with TCQL, you find more information in our online help. A key feature of TCQL is that it understands the richness of time cockpit's data model and generates queries accordingly. Let's look at an example that describes what that means.
Imagine two model entities (=database tables) Project and Customer. Each project is assigned to one customer. Among other things, time cockpit supports the creation of calculated properties. Here is an example of a calculated property Fullname containing a descriptive name for a project by concatenating customer code and project code:
/* Note that Current refers to the currently processed Project record */
:Iif(Current.Customer = Null, '(n/a).', Current.Customer.Code + '.') + Current.Code
If you create a TCQL query selecting the
Project.Fullname property (e.g.
From P In Project Select New With { P.Code, P.Fullname }), time cockpit recognizes that the formula references the
Customer model entity, too. Therefore time cockpit will generate a SQL SELECT statement in the background joining the
Project and
Customer tables so that it can correctly calculate the
Fullname property.
This logic even works with recursive calculated properties (i.e. a calculated property using another calculating property that again uses a third calculated property ...). Additionally, it applies to other aspects of the time cockpit data model like validation rules and permissions, too. This sophisticated query generation logic is completely transparent. When using TCQL, you do not need to worry about it. Time cockpit and CoFX will do the heavy lifting for you.
Performance Implications
Our customers love time cockpit's data model flexibility and use it extensively. They add business logic in the form of calculated properties, validation rules, default values, permissions, etc. As the complexity of their data models grow, seemingly simple TCQL queries can become large and complex SQL SELECT statements in the background. There are time cockpit data models where a simple TCQL query returning a time sheet record ends up in a SQL SELECT statement with dozens of joined tables. Execution time of such statements isn't the problem as they filter the result and return only a few rows. However, the database server needs quite some time to parse and compile the huge SQL SELECT statements.
Performance Enhancements for Azure SQL Database (Server)
In the past, the same TCQL query executed multiple times ended up in slightly different SQL SELECT statements. The reason was the generation logic of column and table aliases, parameter names, etc. This isn't a problem for simple SQL SELECT statement. It becomes a problem in large and complex data models. Therefore, we decided to rework our SQL generation logic. The goal was to generate identical SQL SELECT statements for every execution of a given TCQL query. With this, Azure SQL Database can use its caches much better. It detects identical queries, skips parsing and compiling, and uses a cached version of the corresponding execution plan.
The following screenshot shows a typical SQL SELECT statement as generated by time cockpit's graphical time sheet calendar. Although it is based on a rather simple data model, the statement joins 10 tables. Note that the parse and compile time exceeds the execution time by a factor of 10.
As the generated query differed slightly from execution to execution, time cockpit could not benefit from Azure SQL Database's caches in the past. The following screenshot shows a cache usage statistic from a time cockpit test database. Note that the usecounts KPI is 1 which means that execution plans are not reused:
With our reworked SQL generation logic, the picture changes. Azure SQL Database can now reuse existing execution plans. Parse and compile time is reduced to nearly zero in case of cache hits.
Users of time cockpit will benefit from this enhancements when working with the browser version or in time cockpit's Server-mode.
Performance Enhancements for SQL Compact Edition (Client)
SQL Compact Edition does not have a sophisticated caching mechanism like Azure SQL Database. Therefore the optimization mentioned above does not help when working on the client. However, we wanted to enhance performance there, too. Therefore, we evaluated using prepared SQL SELECT statements. It turned out that they can speed up many queries remarkably. So we reworked our data access layer to use a cache of prepared statement.
When Will You See the Improvements in Time Cockpit?
We will launch some of the Azure SQL Database related optimizations in the upcoming September 2014 release of time cockpit. To be honest, you cannot expect the performance to drastically improve in that update already. Performance will be better, but only slightly.
The next version (October 2014) will get all the optimizations. We have already completed development but couldn't finish testing for the September release. Therefore we had to postpone some of the improvements until October. We will deliver a closed beta version during September. If you are interested to participate, contact support@timecockpit.com.
So keep your time cockpit up to date and tell us what you think about the performance improvements. Are they notable? We would love to get your feedback at support@timecockpit.com.
comments powered by