0 Comments

General background

There are two undocumented functions in Microsoft Access, SaveAsText and LoadFromText. These functions were allegedly included in Access to support the Microsoft Source code control Add-In. They save an Access object (e.g. form, report, query, etc.) to a text source file or load/create such an object from a text file. These text files are ideal to store the definition and source code of an Access object in a version control system.

These functions are undocumented but still widely used, because they are very handy and the only build-in way to store the complete definition of a form or report outside the database in text format.

In Ivercy

Ivercy uses these functions internally not only to create the text files that will be passed to your source code control system as described above, but also as input to calculate the checksums for change detection in your database.

Last week I researched some problems reported by an Ivercy user, who has an unusual huge number of queries (~3,500!) in his database. In addition to a hard exception (fixed in the next release) related to this, I noticed that Ivercy has an abysmally slow performance in this scenario.

This certainly is an edge case, but still I wanted to investigate it and document my findings here.

Problem analysis

I had no plausible explanation for the observed behavior, so I created a test database to reproduce this scenario and gather some performance metrics with it. - The results where disturbing.

My test process was as follows.

  1. Use the CreateQueryDef- / CreateForm-methods to create the desired number of objects (50, 100, 250, …)
  2. Store current time in a variable
  3. Export all those objects calling SaveAsText in a loop
  4. Calculate execution time for the whole loop by comparing the stored time to current time

The more objects you are exporting from a database using SaveAsText, the longer it will take; common sense. With an increased number of objects, I expected a linear increase in execution time for the export. This expectation is represented by the grey line in the first chart. I had no expectation of the absolute execution times, so the grey line should be mainly seen as an expected trend.

First result

So here is a chart of the executions times I encountered in my test.

Line chart of the execution time of SaveAsText for all objects in a database

Let’s compare my expectations to the actual time for exporting forms with SaveAsText (orange line in the charts), you’ll see that the actual execution time follows my expected trend quite closely. So nothing unexpected there.

But now look at the executions time of SaveAsText for the queries (blue line). As soon as the number of queries surpasses 100, the execution time begins to increase significantly compared to the execution time exporting forms. With 500 queries it takes four times as long to export 500 queries compared to exporting 500 forms. With 750 objects, which is a huge but not unheard of number in a real world application, is almost ten times as long.

This is a really unpleasant result of this performance test.

Countercheck

To make sure this is not some kind of memory leak or whatever else strange problem just happening when calling SaveAsText so many times in a loop, I set up another test scenario.

The process for this test is basically like the one listed above. But, instead of exporting all objects, now I export a constant number (100) of forms / queries. Between test runs I still add more objects to the database. To be clear: Those additional objects are just in the database. They are not exported! I would have expected the execution times to be fairly constant, no matter how many objects there are in the database.

But, look what’s happening. Here is the chart for this scenario:

Line chart of the execution time of SaveAsText for 100 objects

The export of the forms behaves as expected again, showing a constant execution time around the 1 second mark. So the export time of forms is independent of the total number of objects in the database.

But now look at the queries! Even though the number of exported queries is same in each run, the execution time increases with the number of objects in the database. So when exporting queries with SaveAsText, the execution time is massively dependent on the number of objects in the database. If you have a huge number of queries in your database, it does not only take longer because you need to export more queries, but also because each single call to SaveAsText needs significantly more time to execute.

Bottom line

First of all, please do keep in mind that this problem only affects a very specific scenario. – I work professionally with Microsoft Access for a very long time and I can’t remember to have worked on a database with more than 1000 queries ever.

This surely it is an explanation for the extremely poor performance of Ivercy is this very specific scenario.

As the root cause for this whole Phenomenon is the implementation of the SaveAsText function in Microsoft Access, there is not much we can do about this. It just makes it even more important to further optimize Ivercy’s internal functioning to call these functions as rarely as possible. - That’s what we will be trying to do.

0 Comments

I wrote about the troubles with getting a stable implementation of background thread to work in Ivercy 1.0.15. The bottom line there was, it does not work with the MSSCCI-Provider for Team Foundation Server / Visual Studio Online and therefore it is disabled in that version except for some of the preparation work.

Background information

While the above is true, there is more to it.

  1. There is a reason, we spotted the problem with the background threading only after the feature was mostly complete. We ourselves use SourceGear Vault as source code control system. And our implementation works flawless with it. We only discovered the issues with the TFS-MSSCCI-Client, as we put the first beta-release candidate through our QA process.

    Depending on the SCC-Client, background threading actually works!

  2. As most of our customers use Team Foundation Server, whose MSSCCI-Client does not work with multithreading, we consider our current implementation flawed and disabled it in the current beta. Still we did not remove feature from source code, but added a hidden configuration option to disable it (partially). This configuration option is not visible in the options dialog in the UI, but it can be changed by editing the _IvercyConfig.xml-File with any text editor.

    You can enable background threading in Ivercy 1.0.15 by editing the config file.

Undocumented option StatusUpdatesInBackground

If you want to experiment with this setting, you can open the configuration file and edit the StatusUpdatesInBackground-Option. To do that, look for this line

<StatusUpdatesInBackground>PrepareOnly</StatusUpdatesInBackground>

The value there is PrepareOnly with a fresh installations of Ivercy 1.0.15 or None after the upgrade of an existing installation. You can replace the option value with any of the values listed below.

Here is a short info on the available options.

Option value

Description

None

This will completely disable any multithreading and is the safest option value.

Works with: All supported SCC-systems/providers

With this option value set, we consider the current beta release fit for production use.

PrepareOnly

Only the local change detection is run on a background thread. All SCC-operations run on the main thread.

Works with: All supported source code control systems

There have been some reports about memory leaks and total freezes of Access, which could be related to this option value. These are still unconfirmed and most of the time this should work without issues.

AutomaticQueries

The automatic SCC status updates run in a background thread.

Works with: Sourcegear Vault (stable), TamTamSVN (basic test), PushOK-SVN-SCC (basic test)

This option value does not work with the TFS-MSSCCI-Client

AllNonExplicitQueries 

All SCC-Operations that were not explicitly initiated by the user run in a background thread. This includes the time consuming initialization of the project when you open the database file.

Please note: Immediately after opening an Access file, all objects will display the green plus symbol of new files until the status query is complete. The correct icons will be displayed, as soon as the status update completes. – This is “by design” and not considered a bug.

Works with: Sourcegear Vault (stable), TamTamSVN (basic test), PushOK-SVN-SCC (basic test)

This option value does not work with the TFS-MSSCCI-Client

Warning: Please take care when editing the Ivercy configuration file. If the file is invalid, Ivercy will not load properly.

If you try the beta release, I would love to hear your feedback!

0 Comments

After quite a long time we released a new beta version of Ivercy this week.

This is the first beta for the upcoming new 1.1 release. From now on we will not work on new features until the release of version 1.1. Instead we focus on fixing bugs now. And there is still is some work to do in that regard. In other words: We are feature complete for the next release.

What was planned

Today I would like to compare the features we planned back in October to what is actually in the current beta.

Back then, we declared three major objectives.

  1. Improve Performance
    • Optimize status management and reduce the number of queries to the SCC backend.
    • Move automatic status updates to a non-blocking background thread.
  2. Reduce the number of “false positive” change detections.
  3. Optionally exclude Data&Misc-Objects from source code control.

So let’s look at these one by one, and see what we’ve got.

What we’ve got

I’ll look at the sub items of primary objective first.

Optimize status management and reduce the number of queries to the SCC backend

We improved our internal state management, use Access’ Date Modified property (unfortunately it’s not reliable), cache our checksum calculation and remove every query to the SCC-Backend that was not absolutely necessary. - This all significantly improved performance of Refresh Status and most notable Get Latest.

Move automatic status updates to a non-blocking background thread

Oh boy. This played a major part in wrecking our original schedule. In theory implementing multithreading based on the .Net Framework should not be too difficult. - Reality was quite different.

Multithreading in a COM-Add-In is somewhat different than in normal .Net-WinForms-Applications. You’ll need to manage some of the COM references yourself, otherwise the host (Access) might crash on shutdown. Thread synchronization with the UI-Thread does not work reliably in the way you would expect. – And even thought we tried to work around that, I suspect there still is a problem in version 1.0.15 with that, as I observed some weird application freeze situations since its release.

An even more serious issue exists however with Microsoft’s TFS-MSSCCI-Provider. It reacts quite hostile, by un-initializing itself, if ever invoked by more than one thread from the same process. I don’t think we can solve this problem without some fundamental changes to Ivercy’s design.

As any further work on this would have delayed the new version even more, I decided to disable any multithreaded use of the SCC-Backend. For now we only do the local preparation work for Status Update in the background. That slightly improves the situation, by reducing the time the Access UI is blocked.

So back to the top objective…

Improve Performance

We certainly achieved a lot in that regard. Considering execution time, there is not much left we could have improved further. Still the intrusive, blocking automatic status updates remain. – For now. But the improvements we have got already are so useful, I did not want to withhold them from you any longer.

Reduce the number of “false positive” change detections

Those false positive change detections result from modifications Access makes to the sources automatically. So we added two new features to deal with those. The first one are the SourceProcessingSettings, which allow you to configure lines and blocks of code to remove from the source files and thus ignoring any automatic changes to them.

Second we made it possible to ignore case of the text in the source files. You can configure that with the IgnoreCaseModifications option.

By using these two new options, you can reduce false positives to almost none (see the article above for the limitations). So I think we fully delivered on this objective.

So remaining is the third objective.

Optionally exclude Data&Misc-Objects from source code control

So the answer to this is simple. It’s not implemented at all. We total dropped that from our list for now. In last months we received lots of support emails and feature requests about the first and second objective, but none about this one. So the decision was easy to defer this item to a later release. This makes the other two available sooner, as they provide value to more of our customers; to you.

Check out the beta

Ok that’s it for now. If you are curious check out the most recent beta from the download page (scroll down, the betas are below the regular releases). Version 1.0.15 seems to have an issue with freezing Access on occasion though. I will send out a newsletter as soon as we’ve got a more stable release.

0 Comments

Back in October I announced the next release of Ivercy for January 2016. We are well into February now and there is neither a new general release nor a new beta version out.

And this is mainly my fault. Sorry. I messed up. I overcommitted to too much client consulting work for the past months. This was aggravated by less than planned availability of freelance staff, so we could not dedicate enough time to Ivercy.

On top of those organizational shortcomings we had some unexpected technical issues with the new features in Ivercy. Those lead to the decision to not release a first beta that was planned for last week.

So the bottom line is, I want you to know Ivercy is alive and we are working on bug fixes as well as new features. It will be weeks rather than months until we release a new version. However I do not want to commit to a schedule I’m not sure we can meet for now.

0 Comments

The recently released Microsoft Access 2016 did not impress with many new features. Now Microsoft seems to be planning to step up the development of Access for the next release of Office.

They created the Access Suggestion Box forum on Uservoice to submit feedback to the Access product team.

Now some of the submitted feature requests are actually “Under review”. So Microsoft seems to be listening to what the Access community wants to be enhanced in Access.

It still remains to be seen, what (if anything) will actually be included in the next version of Access, but nevertheless this is an unprecedented opportunity for the Access community so submit feature requests for Access.

I suggest you don’t let this opportunity pass. Visit the feedback forum and submit your own idea or vote on ideas that would help you with your work in Access.