Creating Signal Names, that query fast, using Seeq SQL v2 Connector

[Brian Crandall]

It is often useful to construct Signal names using an amalgamation of several columns in the table or view of the RDBMS.  However, querying samples and capsules using these constructed names can have slow performance because these fields are not all defined as primary keys within the tables and views.  If the constructed names perform fine in the runtime query - that's fine and no additional work is needed.  But for the databases that query slow, with the Seeq SQL v2 connector, the users can have it both ways - a long descriptive name that performs well at runtime!  All that is needed is a column in the table or view is a primary key for the signal tag.  The example JSON configuration below shows a Signal that is created using 'SELECT DISTINCT' along with COALESCE to concatenate 3 columns in the view to create a variable called "signalTag".  Along with that, we created a "signalId" variable that holds the [PointInfoID] field, which is a Primary Key in this view; we used this next in the "Sql" field, shown as variable ${signalId}.  What about our friendly Signal name?  That is defined in the "Name" property using the ${signalTag} variable from the COALESCE statement.

    {
      "Name" : "SignalsFromTable",
      "Type" : "SIGNAL",
      "Sql" : "SELECT [vDATA_PointReading].[TimeStamp], [vDATA_PointReading].[Value] FROM [vDATA_PointReading] WHERE [CFG_PointInfoID] = '${signalId}'",
      "Enabled" : true,
      "TestMode" : false,
      "Variables" : [ {
        "Names" : [ "signalTag", "signalId" ],
        "Values" : [ [ "${result}" ] ],
        "Sql" : "SELECT DISTINCT COALESCE([vCFG_AverageInfo].[UnitID], '') + ' - ' + COALESCE([vCFG_AverageInfo].[PointName], '') + ' - ' + COALESCE(CAST([vCFG_AverageInfo].[PointDescription], '')  + ')', [PointInfoID] FROM [SiteInfoView].[dbo].[vCFG_AverageInfo]"
      } ],
      "Properties" : [ {
        "Name" : "Name",
        "Value" : "${signalTag}",
        "Sql" : null,
        "Uom" : "string"
      }, {
        "Name" : "Interpolation Method",
        "Value" : "linear",
        "Sql" : null,
        "Uom" : "string"
      }, {
        "Name" : "Maximum Interpolation",
        "Value" : "7day",
        "Sql" : null,
        "Uom" : "string"
      } ],
      "CapsuleProperties" : null
    }

Seeq's SQL v2 Connector provides a flexible interface to create human-readable signals that perform fast.  My recommendation is to start simple and then add complexity to address those aspects, as needed.