Huge Designer Speed-Up for Large Databases

Posted: Friday, July 14, 2017 | 2 comment(s)

For large databases, this means passing some hundred tables including their field structure and sort orders. When using relations, tables could even appear multiple times in the structure, as base tables or 1:1 related tables. You quickly end up with literally millions of API calls and thousands of database queries. While we paid a visit to treeview performance before, this was merely a cure for the symptoms. The start up speed of the Designer was still way slower than desirable.

With LL23, we finally are able to implement a feature we've been thinking of for quite a long while – delay loaded data for the Designer. This feature works for all implementations that use a data provider, i.e. for .NET and C++ applications. To give you some numbers I used the Adventure Works SQL Server demo database as a reference. This is what I got on my workstation:

 

Measured Variable	LL22	LL23
Process Memory	67,5 MB	41,8 MB
Number of DB Queries	630	2
Time for Opening the Designer	5,7 s	0,9 s

 

The remaining two queries are required to get information on key fields and relations.

This tremendous start up performance increase now allows to pass whole databases where a selection had to be made before. This saves you from offering your own preselection dialogs or having to recompile your application with "just one more table". For your users, the psychological effect will be great – a cut in the start up delay of more than 80% as in my test case above is absolutely remarkable for them. And your database admin will also love this update.

To give you an impression of the tremendous impact of this change look at the two movies below, one for LL22 and another one for LL23. It shows the startup on a colleague's machine for the Adventure Works database, this time not using a local server (as for my data in the table above) but using a SQL Server on our intranet. In the "Diagnostic Tools" window on the right hand side you see the memory footprint including CLR heaps, thus a larger number than my task manager data in the table above, the queries that hit the database and the CPU usage. The Designer opens after 13,8 seconds for LL22. As for LL23, see for yourself.

Startup time with List & Label 22:


Startup time with List & Label 23:


The Web Designer will profit even more from this feature – before, we had to transfer the whole field information from the server to the client, resulting in both huge spikes in server CPU and network bandwidth usage. Now, we just have to pass some basic information about the tables and relations and delay the expensive requests until they're really needed. In a couple of weeks, I'll blog more on the new Web Designer's performance on a related topic – stay tuned.

---

Author: Jochen Bartlau
Head of Development List & Label

Jochen Bartlau leads the development at combit as Managing Director. He's a Microsoft .NET enthusiast driving innovation & agile project management. The mobile devices geek who used to be a physicist in his first life loves to spend his spare time with his family.

   

---