Optimizing ODBC Queries

ODBC > Using the ODBC Driver > Optimizing ODBC Queries

Optimizing ODBC Queries

ODBC performance can be improved by fine-tuning queries according to the recommendations below. By making queries more explicit it can reduce the time needed to gather the information of interest.

Notes

Some ODBC-capable applications, such as Crystal Reports, request the entirety of the Data Dictionary, even when the query is fully qualified.

If a database service shuts down during the lifetime of the connection to the CygNet ODBC driver, and the first query against that service occurs while it has shutdown, the error message will indicate that the table was not found. If a query has been successfully executed against that service before the connection is lost, the error message will indicate the CygNet service is not in service. If the service is restarted after a table not found error, the query will still be executed fine.

The only services that will be available to the current ODBC connection are the services that are in the OK state at the time the connection is made.

Recommendations

Since the data dictionary is loaded on-demand, if no table owner is specified in the query, the entire data dictionary for every service is loaded, which can slow down the initial run of the query dramatically. To optimize the query, specify the table owner. For example, use select device_id from acme4_dds.dds_dds_device_header instead of select device_id from dds_device_header.
During initial development of reports or applications in tools like Microsoft Access, use a custom DSN to minimize the amount of time it takes to load the list of tables. When deploying the applications, custom DSN do not need to be used.
Specify the table_owner column when querying the OA tables for table, column, and index information to avoid loading the data dictionary for all services.
Specify only the columns you're going to use in your query. This can increase performance of queries against services such as the PNT and FAC which have many columns.
When performing a join against the pnt_header_record table that includes PointId or PointIdLong, list pointid or pointidlong first in the join criteria, before site and service. This helps the ODBC driver pick a better index.
When performing a join against the fac_header table that includes facility_id list facility_id first in the join criteria, before facility_site and facility_service. This helps the ODBC driver pick a better index.
Use Stored Procedures to complete tasks such as:

VHS Service Rollup
Point in Time
Delayed Writes

Use Indexes as Views to optionally choose exact indexes in order to fine-tune performance. This method provides the following benefits:

Eliminate manual intra-service joins where an index already provides that join. This is most visible on the DDS, such as in the C2 and C3 indexes.
Provide access to indexes that are sparse, such as the DDS in-progress transaction index and the DDS non-template device index.
Provide explicit index selection to improve query performance.
Read from the index rather that the data record, since the index records are often smaller than the data records, thereby increasing access time if the data has not already been cached by the service.

Stored Procedures

Stored procedures provide extended CygNet functionality when using ODBC.

VHS Service Rollup

VHS service rollup functionality is exposed through a stored procedure called HistoryRollup. Values must be specified for the following parameters in the order they appear in the table:

Parameter	Description
Tag	A comma-delimited list of tags. Format options are: short (MYSITE.UIS:00000001) long (MYSITE.UIS:MYSITE_UIS_SYPROCQUE) FACILITY.UDC (MYSITE.UIS::MYSITE_UIS.SYPROCQUE) The tag format can be mixed within a single call to the stored procedure. Wildcards are not supported. If the Facility.UDC format is used, the CVS specified in the tag must be running at the time of query. Up to 300 tags are allowed per call to the stored procedure. The long tag format is the most optimal as no point resolution has to be performed.
EarliestTime	The start time of the rollup.
LatestTime	The end time of the rollup.
RollupType (one of these types)	CalcWeightedAverage	Returns a time-weighted rolling average for the interval with a timestamp that reflects the end of the rollup interval. Example If the data window is a three-day period and the rollup interval is one day, the result would be one value per day that represents a time-weighted average for the three-day period.
	CalcMean	Returns the average of the values with a timestamp that reflects the end of the rollup interval.
	CalcMin	Returns the minimum value for the interval with a timestamp that reflects the end of the rollup interval.
	CalcMax	Returns the maximum value for the interval with a timestamp that reflects the end of the rollup interval.
	CalcDelta	Returns the difference between the minimum and maximum values. If there is no difference, the returned value is zero.
	CalcLast	Returns the last value received.
	ThinMedian	Returns the middle value from the set of values. If there is an even number of values collected, the value returned is the one that falls before the true middle. If no values are collected, no values are returned.
	ThinMinMax	Returns the minimum and maximum values. If only one value is collected, it returns the value. If no values are collected, no values are returned.
	ThinLast	Returns the last value collected. If no values are collected, no values are returned.
RollupUnits	Units of the rollup values. Options are: Seconds, Minutes, Hours, or Days.
RollupPeriod	Specifies the duration of the rollup, applied to RollupUnits.
TopSubUnit	Defines an offset from the normal date/time. It provides a method to allow for start of day, time zone, or scheduling differences. Example If you are using a rollup period of 1 day (RollupUnits as Days and RollupPeriod of 1), and your data day starts at 8:00 A.M. (instead of midnight), you can offset the period by entering an "8" in this field. Note: This field uses 24-hour time.

The returned columns are:

Return Column	Description
Tag	The original format of the tag as requested in the stored procedure.
Site	The resolved site for the tag.
Service	The resolved service for the tag.
PointIdLong	The resolved pointidlong for the tag.
Value	Rolled-up value.
PointTime	Timestamp of the value returned from the rollup, depends upon the RollupType.
UnreliableFlag	Y if the value is unreliable or N if it is reliable.

History Rollup Sample

Option Explicit

Dim objConnVhs

Set objConnVhs = WScript.CreateObject("ADODB.Connection")

objConnVhs.ConnectionString = "DSN=CygNet;ServiceFilter=*.*"

objConnVhs.Open()

Dim objRS

Set objRS = CreateObject("ADODB.RecordSet")

objRS.Open "call mysite_vhs.historyrollup('MYSITE.UIS:3450520367_SPEED', '2019-05-24 07:30:00', '2019-05-24 13:30:00', 'CalcMean', 'Minutes', 30, 0)", objConnVhs

objRS.MoveFirst()

While Not objRS.EOF

WScript.Echo "Value: " & objRS.Fields("Value") & ". PointTime: " & objRS.Fields("PointTime")

objRS.MoveNext()

Wend

objConnVhs.Close

Also see History Rollups for more information.

Point in Time

VHS service point in time functionality is exposed through a stored procedure called EnableExactTimeRetrieval. This procedure modifies the behavior of retrieval for VHS queries that specify exact pointtime or recordtime values (not ranges). This option is connection specific.

The only parameter is Enable, the accepted values are:

ON, ENABLE, YES, Y, TRUE, T, or 1 to enable this behavior
OFF, DISABLE, NO, N, FALSE, F, or 0 to disable this behavior

If EnableExactTimeRetrieval is enabled, only a single value for the given timestamp would be returned. If there are multiple values with the same timestamp only one will be returned. This may not be the desired behavior. To ensure all values with the same timestamp are returned, disable the EnableExactTimeRetrieval feature.

This is analogous to the History Playback feature in CygNet Studio.

By default, the behavior is disabled.

Point in Time Example

Option Explicit

Dim objConnVhs

Set objConnVhs = WScript.CreateObject("ADODB.Connection")

objConnVhs.ConnectionString = "DSN=CygNet;ServiceFilter=*.*"

objConnVhs.Open()

Dim objRS

Set objRS = CreateObject("ADODB.RecordSet")

objRS.Open "call mysite_vhs.enableexacttimeretrieval('enable')", objConnVhs

Delayed Writes

Delayed writes for bulk updates is exposed through a stored procedure called EnableDelayedWrites. If enabled, data will be visible in the database as they occur but will only be written to the disk at 30 second intervals. This feature reduces the number of writes required for each record update. This option is only effective on queries with multiple updates and all changes will be written to disk at the completion of the query regardless of time interval. It is connection specific. This option is only available to services that have read/write ODBC Access.

The only parameter is Enable, the accepted values are:

ON, ENABLE, YES, Y, TRUE, T, or 1 to enable this behavior
OFF, DISABLE, NO, N, FALSE, F, or 0 to disable this behavior

By default, the behavior is disabled.

Delayed Writes Example

Option Explicit

Dim objConn

Set objConn = WScript.CreateObject("ADODB.Connection")

objConn.ConnectionString = "DSN=CygNet;ServiceFilter=*.*"

objConn.Open()

Dim objRS

Set objRS = CreateObject("ADODB.RecordSet")

objRS.Open "call mysite_pnt.enabledelayedwrites('enable')", objConn

Indexes as Views

Database indexes for the CygNet DBS-based services are exposed as views, which allow query writers to optionally choose exact indexes in order to fine-tune performance.

Note: Database indexes for DBS-based services can be viewed using CygNet Explorer:

Select Search (right-click in the service pane, then select Select Search…) This option is not available for the ACS, DDS, and MSS.
DBS Tables (right-click on the service in the service hierarchy, select Service Details, then DBS Tables)

Intra-service Joins

Manual intra-service joins can be eliminated where an index already provides that join. This is most visible on the DDS, such as in the dds_c2_index and dds_c3_index tables.

Example

The dds_c2_index includes the Device Category, Device ID, DataGroup Type, Data Group Ordinal, and Data Group Description. The following queries return equivalent results (although not necessarily in the same order):

select device_category, device_id, data_group_type, data_group_ordinal, data_group_description, l2key, queuekey from "cygdemo.dds".dds_device_header DH inner join "cygdemo.dds".dds_data_group_header DGH on DH.queuekey = DGH.level1_foreignkey;

With indexes as views, use the following query:

select * from "cygdemo.dds".dds_c2_index;

Example

The dds_ir_index includes the Remote Device ID and its primary Comm ID.

One way to query:

select DH.device_id, comm_id1 from "cygdemo.dds".dds_device_header DH inner join "cygdemo.dds".dds_rem_detail_header_ex RDH on DH.queuekey = RDH.level1_foreignkey;

With indexes as views, the following reduces the complexity of the query:

select device_id, comm_id1 from "cygdemo.dds".dds_ir_index;

Sparse Indexes

Index views provide access to indexes that are sparse, such as the DDS in-progress transaction index and the DDS non-template device index.

Example

There are two HyperPoint indexes on the PNT that have an implicit "HyperPointEnabled = 'Y'" condition on them (pnt_h1_index and pnt_h2_index). Doing a full scan of either index only needs to read point data for the points that are actually HyperPoints.

Since CygNet ODBC is unaware of the conditions that are on sparse indexes, the following query will never use that index:

select * from "cygdemo.pnt".pnt_header_record where HyperPointEnabled = 'Y';

Note: CygNet ODBC does not perform well with conditions such as "where this column does not equal some value." But since CygNet DBS-based services allow construction of indexes that can be filtered with "where this column does not equal some value," a query author could define a custom index, which matches the main part of the query. This should only be done after careful optimization, and by limiting the custom index to a small number of values, because creating too many indexes will hinder database write times, as well as increase disk usage. As a built-in example, the dds_c1_index table implicitly filters out device templates. See Adding a New Index to a DBS-Based Service for instructions on adding a new index.

Explicit Index Selection

Including the explicit index selection will improve query performance.

When using a query like this:

select site, service, facilityid, uniformdatacode from " cygdemo.pnt".pnt_header_record where site = 'cygdemo' and service = 'uis' and facilityid = 'cygdemo_uis';

index selection is generally poor. The query essentially searches over all points that match site = 'cygdemo' and service = 'uis', but ignores the facilityid criteria.

If the query is changed to use the pnt_s2_index table:

select site, service, facilityid, uniformdatacode from "cygdemo.pnt".pnt_s2_index where site = 'cygdemo' and service = 'uis' and facilityid = 'cygdemo _uis';

query time and data selection is improved. If you need any other columns from the table, you'll need to perform a join against the pnt_header_record table.

Note: The order of the columns represents the order of the fields in the index, which will influence the index that you should use. In the pnt_s2_index example above, the pnt_f1_index can also satisfy that request, but doesn't do it as well because the site and service columns come after columns that have no condition on them (UniformDataCode and PointIdLong), but the order in the pnt_s2_index table is Site, Service, and then FacilityId, so the query is able to use all of the conditions to reduce the search set.

When joining against another table, it is likely that processing the "index" tables first in the join will perform better than if it being processed later. To force the join order, you can use Join Hints to test what is best given the query and the expected data set.

Join Hints

From the v7.0 SQL Engine Programmer's Guide:

The OpenAccess SDK SQL engine syntax includes specification of the preferred processing order as a HINT at the end of the SELECT statement.

HINT JOIN QUERY ORDER
HINT JOIN (tablex, tabley, tablez)

Examples

SELECT * FROM emp, dept WHERE empno = 1 AND emp.deptno = dept.deptno HINT JOIN QUERY ORDER

SELECT * FROM emp, dept WHERE empno = 1 AND emp.deptno = dept.deptno HINT JOIN (emp, dept)

Reading from the Index Record

Since the index records are often smaller than the data records, and if one of the indexes has all of the columns that you are concerned about, and the query is unbounded, reading from the index will incur less disk I/Os if the data has not already been cached by the service.