Performance improvements

The code generated by the test version of the online modeler has had some modifications made in order to achieve better performing insert triggers and perspectives. Currently, these modifications have only been made for the unitemporal models and only affect the historized parts of the model. Thanks to the “Live Query Statistics” in SQL Server, a bottleneck with the scalar-valued functions finding previous and following values in the insert trigger was identified. This code has instead been inlined. When writing new BIML generation for the sisula ETL framework, an apparently faster way to find the latest version was found, using ROW_NUMBER and a “superfluous” GROUP BY to retain table elimination. This has only been tested on SQL Server 2016 so far, but should work since version 2008 and onwards. As a side note, the SSIS packages generated from the BIML are magnitudes faster than using the insert triggers, so we recommend using these for large volume data warehouses or when priming with historical data. Please test and report back, and if no issues are found this will go into the production version as a new release.

Published by

Lars Rönnbäck

Co-developer of the Anchor Modeling technique. Programmer of the online modeling tool. Site maintainer. Presenter and trainer.

Leave a Reply

Your email address will not be published. Required fields are marked *