Coresuite Designer - Query Logging Improvement – Helpdesk

Introduction

Query logging in a Designer is a functionality which allows you to log all queries executed by Designer on layouts before they are executed. Results can be then used for debugging.

How the logging works

You can turn on the logging in the Designer's configuration section:

Administration -> Add-Ons -> coresuite designer -> Configuration -> General (tab) -> Log Queries (option)

When this option is checked, then every SQL query executed in/by Coresuite Designer is logged into the file:

%temp%\swald\%companyDBname%\queries\queries.txt

What was changed

In the Coresuite Designer 6.95, we improved the performance of the logging mechanism and also the output of logged Designer's queries.

This logging is more robust. It uses caching, what leads to an overall performance improvement of the Designer. In addition to old file queries.txt, the new mechanism creates a new log file queries.sql, which is generated in the same path.

We kept the old mechanism with the old file, so there is no breaking change introduced.

Benefits of the new logging mechanism

each query has a header:
- timestamp
- header text (if it was supplied)
queries are minimized - there is one query per line, to provide higher readability throughout the file. You can use 3d party tools like Poor Man's T-SQL Formatter for Notepad++ or Microsoft SSMS for a selected query to format it into multiple lines so that you can see all the details.
file is being kept open for up to 5 seconds - performance improvement - Designer doesn't have to close and open the file again immediately for each logging action.
the content being logged is cached and the cache is kept up to 5 seconds before it's fully flushed into the file - performance improvement.
the file is being archived on daily basis (at or after midnight) - that means it's being kept using the following file name template queries.YYYYmmDD.sql (eg queries.20191231.sql).

Notes

Wait 5 seconds after the last action within Designer to see all the content of the log file.
If there is some custom code, that still uses the old logging mechanism, the logged content will be in the old file. Please, check both files to see all the content that is supposed to be logged.
There is an older, existing Designer's functionality to check the swald folder during the startup and to remove the content, that is older than 3 months.

How to migrate custom code

The old logging mechanism was used like this:

System.IO.StreamWriter writer = LayoutHelper.Settings.queryLogWriter;
writer.WriteLine("text");

// or in one line:
LayoutHelper.Settings.queryLogWriter.WriteLine("text");

We do not support the old logging method anymore and we embrace you to use the new mechanism like this:

using swissLD.Common.Data.Query.Log;
IQueryLogger logger = QueryLoggerFactory.CreateNew();
logger.Log("text");

// or in one line:
swissLD.Common.Data.Query.Log.QueryLoggerFactory.CreateNew().Log("text");

// you can also use additional parameters:
logger.Log(sqlStatement, minimizeSqlStatement);
logger.Log(sqlStatement, headerText);
logger.Log(sqlStatement, headerText, minimizeSqlStatement);