How queries are optimised and executed by ViewX

sbeadle1 · ‎2021-06-09

📖 Home Back
Queries in ClearSCADA utilize SQL (Structured Query Language) under the hood to define what data needs to be displayed and to handle the retrieval of data from the server.

When a query is displayed in, ViewX attempts to optimize the query by breaking it up and executing it in three phases.

ViewX asks the server to parse the query and return the query metadata (information about the columns, parameter and table type)

The query is 'prepared' rather than being executed in it's entirity by the server

The server strores a reference to this query

ViewX requests a dynaset outlining the results of the query.

The dynaset is returned if query can be appropriately optimized

The dynaset includes RecordId which can be used as an index to fetch other column details later.

The dynaset includes any columns required for sorting.

Lots of rows maybe returned

Only a subset of columns are returned. This means less data is transferred to ViewX

When a set of rows is on display ViewX Retrieves all of the column data for the displayed rows

The required 'RecordIds' are passed by parameter, this allows a "Unique By Value" optimization.

ViewX requests the query metadata

The first phase of query execution is to request the 'prepare' the query and fetch the metadata.
The following logs show ViewX requesting that a query be prepared, with GetColumnEnums, and requesting the query metadata. In this case details of 9 columns, 0 parameters and 0 table types are returned.

18-JUL-2007 09:53:03.953 05BC SCX [41] SetClientDetails( IN: Name 'ABSINTH::CQPAccess', Lcid 0x809 )
18-JUL-2007 09:53:03.953 05BC SCX [41] SetClientDetails( OUT: Features 19727, hr 0 -> Ok )
18-JUL-2007 09:53:03.953 05BC SCX [41] Prepare( IN: GetColumnEnums true )
18-JUL-2007 09:53:03.953 05BC SCX [41] SQL: '''SELECT TOP( 10000 ) "RecordId", "RecordTime" AS "~Time", "FormattedValue", "StateDesc", "QualityDesc", "ReasonDesc", "Foreground", "Background", "Blink" FROM CDBHistoric WHERE "Id" = 87 AND "~Time" BETWEEN { OPC 'Hour - 23 Hours' } AND { OP
18-JUL-2007 09:53:03.953 05BC SCX [41] SQL: C 'Hour - 23 Hours+1 Day' } ORDER BY "~Time" DESC'''
18-JUL-2007 09:53:03.953 05BC QP [42] FinalConstruct() ApartmentType 0 (STA), ThreadType 1
18-JUL-2007 09:53:03.953 05BC SCX [41] Prepare( OUT: Table 1, hr 0 -> Ok )
18-JUL-2007 09:53:03.953 05BC SCX [41] GetMetadata( IN: Table 1 )
18-JUL-2007 09:53:03.953 05BC SCX [41] GetMetadata( OUT: hr 0 -> Ok, ColCount 9, ParamCount 0, TableType 0 )

The server logs show the processing of these requests.

18-JUL-2007 09:53:03.953 0A64 ]
18-JUL-2007 09:53:03.953 0A64 ]{}{
18-JUL-2007 09:53:03.953 0A64 ]
18-JUL-2007 09:53:03.953 0A64 [SCX] 1:09 Prepare( OUT: hr 0 -> Ok., TableId 0x1 )
18-JUL-2007 09:53:03.953 0A64 [SCX] 1:09 Write complete, sent 4, remaining 0
18-JUL-2007 09:53:03.953 0A64 [SCX] 1:09 Read blocked, received 0
18-JUL-2007 09:53:03.953 0A64 [SCX] 1:09 Read blocked, received 0
18-JUL-2007 09:53:03.953 0A64 [SCX] 1:09 Read complete, received 8
18-JUL-2007 09:53:03.953 0A64 [SCX] 1:09 GetMetadata( IN: TableId 0x1 )
18-JUL-2007 09:53:03.953 0A64 [SCX] 1:09 GetMetadata( OUT: hr 0 -> Ok., ColumnCount 9, ParamCount 0, TableType 0 )
18-JUL-2007 09:53:03.953 0A64 [SCX] 1:09 Write complete, sent 2236, remaining 0

ViewX requests a dynaset outlining the results of the query

ViewX fetches a dynaset outlining the results of the query. If the query can be optimized appropriately the server returns a dynaset consists of an indexed version of the resultset. This dynaset containing a RecordID column along with any columns used in the "Order By" clause. The RecordID column can be used to uniquely identify a specific row in a table and therefore can be used at a later time to fetch details of any other required column.

The following logs show ViewX requesting the RecordID and RecordTime of all historic data entries for the point where the RecordTime is within the 24hr period.

18-JUL-2007 09:53:03.984 05BC SCX [41] Prepare( IN: GetColumnEnums false )
18-JUL-2007 09:53:03.984 05BC SCX [41] SQL: SELECT TOP( 10000 ) RecordId, RecordTime FROM CDBHISTORIC WHERE ("Id" = 87) AND (RecordTime BETWEEN { OPC 'Hour - 23 Hours' } AND { OPC 'Hour - 23 Hours+1 Day' }) ORDER BY RecordTime DESC
18-JUL-2007 09:53:03.984 05BC QP [43] FinalConstruct() ApartmentType 0 (STA), ThreadType 1
18-JUL-2007 09:53:03.984 05BC SCX [41] Prepare( OUT: Table 2, hr 0 -> Ok )
18-JUL-2007 09:53:03.984 05BC SCX [41] Execute( IN: Table 2, ParamCount 0 )
18-JUL-2007 09:53:03.984 05BC SCX [41] Execute( OUT: hr 0 -> Ok, RowsAffected 0 )
18-JUL-2007 09:53:03.984 05BC SCX [41] GetResultset( IN: Table 2, Dynaset TRUE )
18-JUL-2007 09:53:03.984 05BC SCX [41] GetResultset( OUT: hr 0 -> Ok, ColCount 0, RowCount 3 )
18-JUL-2007 09:53:03.984 05BC QP [43] FinalRelease()
18-JUL-2007 09:53:03.984 05BC SCX [41] DetachTable( IN: Table 2 )
18-JUL-2007 09:53:03.984 05BC SCX [41] DetachTable( OUT: )
18-JUL-2007 09:53:03.984 05BC QP [43] Deleted
18-JUL-2007 09:53:04.000 05BC QP [42] FinalRelease()
18-JUL-2007 09:53:04.000 05BC SCX [41] DetachTable( IN: Table 1 )
18-JUL-2007 09:53:04.000 05BC SCX [41] DetachTable( OUT: )
18-JUL-2007 09:53:04.000 05BC QP [42] Deleted

This query is executed on the server, but only a subset of the results are required. This query requires each RecordTime Field is checked to see if it is within our time constraints, therefore it is a "scan" search which can be a relatively relatively slow operation.

18-JUL-2007 09:53:03.984 0A64 [SCX] 1:09 Prepare( IN: GetColumnEnums false )
18-JUL-2007 09:53:03.984 0A64 [SCX] 1:09 SQL: SELECT TOP( 10000 ) RecordId, RecordTime FROM CDBHISTORIC WHERE ("Id" = 87) AND (RecordTime BETWEEN { OPC 'Hour - 23 Hours' } AND { OPC 'Hour - 23 Hours+1 Day' } ) ORDER BY RecordTime DESC
18-JUL-2007 09:53:03.984 0A64 [SCX] 1:09 Prepare( OUT: hr 0 -> Ok., TableId 0x2 )
18-JUL-2007 09:53:03.984 0A64 [SCX] 1:09 Write complete, sent 4, remaining 0
18-JUL-2007 09:53:03.984 0A64 [SCX] 1:09 Read blocked, received 0
18-JUL-2007 09:53:03.984 0A64 [SCX] 1:09 Read blocked, received 0
18-JUL-2007 09:53:03.984 0A64 [SCX] 1:09 Read complete, received 12
18-JUL-2007 09:53:03.984 0870 [SCX] 1:09 Execute( IN: TableId 0x2, ParamCount 0 )
18-JUL-2007 09:53:03.984 0870 [SCX] 1:09 Generating initial sources
18-JUL-2007 09:53:03.984 0870 [SCX] 1:09 Building resultset
18-JUL-2007 09:53:03.984 0870 [SCX] 1:09 Resultset complete
18-JUL-2007 09:53:03.984 0870 [SCX] 1:09 Query complete
18-JUL-2007 09:53:03.984 0870 [TRANS] 0 SyncExecute Time = 0.000512 seconds
18-JUL-2007 09:53:03.984 0870 [TRANS] 0 QP Execute
18-JUL-2007 09:53:03.984 0870 [TRANS] 0 User Guy
18-JUL-2007 09:53:03.984 0870 [TRANS] 0 ParamCount 0
18-JUL-2007 09:53:03.984 0870 [TRANS] 0 Status 0 -> Ok.
18-JUL-2007 09:53:03.984 0870 [SCX] 1:09 Execute( OUT: Hr 0 -> Ok. )
18-JUL-2007 09:53:03.984 0A64 [SCX] 1:09 Write complete, sent 4, remaining 0
18-JUL-2007 09:53:03.984 0A64 [SCX] 1:09 Read complete, received 9
18-JUL-2007 09:53:03.984 0A64 [SCX] 1:09 GetResultset( IN: TableId 0x2, Dynaset TRUE )
18-JUL-2007 09:53:03.984 0A64 [SCX] 1:09 GetResultset( OUT: hr 0 -> Ok. )
18-JUL-2007 09:53:03.984 0A64 [SCX] 1:09 Write complete, sent 244, remaining 0
18-JUL-2007 09:53:03.984 0A64 [SCX] 1:09 Read complete, received 8
18-JUL-2007 09:53:03.984 0368 [SCX] 1:09 Detach( IN : TableId 0x2 )
18-JUL-2007 09:53:03.984 0368 [SCX] 1:09 Detach( OUT: Hr 0 -> Ok. )
18-JUL-2007 09:53:03.984 0A64 [SCX] 1:09 Write complete, sent 0, remaining 0
18-JUL-2007 09:53:04.000 0A64 [SCX] 1:09 Read complete, received 8
18-JUL-2007 09:53:04.000 0870 [SCX] 1:09 Detach( IN : TableId 0x1 )
18-JUL-2007 09:53:04.000 0870 [SCX] 1:09 Detach( OUT: Hr 0 -> Ok. )
18-JUL-2007 09:53:04.000 0A64 [SCX] 1:09 Write complete, sent 0, remaining 0
18-JUL-2007 09:53:04.000 0A64 [SCX] 1:09 Read complete, received 423

ViewX requests full column data

ViewX renders the query results for the user based on the dynaset. After determining how many results are on display it goes back to the server to fetch the full column details for each row based on the RecordId's for the rows that are on display.

ViewX passes the RecordID by parameters rather than using RecordTime as this is faster.

When the screen is scrolled ViewX needs only to resend this query, passing different RecordIDs

18-JUL-2007 09:53:04.000 05BC SCX [41] Prepare( IN: GetColumnEnums false )
18-JUL-2007 09:53:04.000 05BC SCX [41] SQL: SELECT "RecordId", "RecordTime" AS "~Time", "FormattedValue", "StateDesc", "QualityDesc", "ReasonDesc", "Foreground", "Background", "Blink" FROM CDBHISTORIC WHERE RecordId = ? OR RecordId = ? OR RecordId = ?
18-JUL-2007 09:53:04.000 05BC QP [44] FinalConstruct() ApartmentType 0 (STA), ThreadType 1
18-JUL-2007 09:53:04.000 05BC SCX [41] Prepare( OUT: Table 3, hr 0 -> Ok )
18-JUL-2007 09:53:04.000 05BC SCX [41] Execute( IN: Table 3, ParamCount 3 )
18-JUL-2007 09:53:04.031 05BC SCX [41] Execute( OUT: hr 0 -> Ok, RowsAffected 0 )
18-JUL-2007 09:53:04.031 05BC SCX [41] GetResultset( IN: Table 3, Dynaset FALSE )
18-JUL-2007 09:53:04.031 05BC SCX [41] GetResultset( OUT: hr 0 -> Ok, ColCount 0, RowCount 3 )
18-JUL-2007 09:53:04.031 05BC SCX [41] AdviseSink( IN: Table 3, ColumnCount 0 )
18-JUL-2007 09:53:04.031 05BC SCX [41] SetupAdvise( IN: )
18-JUL-2007 09:53:04.031 05BC SCX [41] ApartmentType 0 (STA), ThreadType 1
18-JUL-2007 09:53:04.031 05BC Sink-App New Thread 0x5bc (With Msg Loop)
18-JUL-2007 09:53:04.031 05BC Sink-App Created Invisible Window 0x307f4
18-JUL-2007 09:53:04.031 0C34 Sink-Thr ]
18-JUL-2007 09:53:04.031 05BC Sink-App WaitForAdvise
18-JUL-2007 09:53:04.031 05BC Sink-App WaitForAdvise - OK on slot 4
18-JUL-2007 09:53:04.031 05BC SCX [41] AdviseCount 0 -> 1
18-JUL-2007 09:53:04.031 05BC SCX [41] SetupAdvise( OUT: Hr 0 -> Ok )
18-JUL-2007 09:53:04.031 05BC SCX [41] AdviseSink( OUT: hr 14009D -> Query does not support interest., Cookie 0 )
18-JUL-2007 09:53:04.031 05BC SCX [41] ClearResults( IN: Table 3 )
18-JUL-2007 09:53:04.031 05BC SCX [41] ClearResults( IN: hr 0 -> Ok )

The query execution can be seen in the server logs:

18-JUL-2007 09:53:04.000 0A64 [SCX] 1:09 Prepare( IN: GetColumnEnums false )
18-JUL-2007 09:53:04.000 0A64 [SCX] 1:09 SQL: SELECT "RecordId", "RecordTime" AS "~Time", "FormattedValue", "StateDesc", "QualityDesc", "ReasonDesc", "Foreground", "Background", "Blink" FROM CDBHISTORIC WHERE RecordId = ? OR RecordId = ? OR RecordId = ?
18-JUL-2007 09:53:04.000 0A64 [SCX] 1:09 Prepare( OUT: hr 0 -> Ok., TableId 0x3 )
18-JUL-2007 09:53:04.000 0A64 [SCX] 1:09 Write complete, sent 4, remaining 0
18-JUL-2007 09:53:04.000 0A64 [SCX] 1:09 Read complete, received 228
18-JUL-2007 09:53:04.031 0870 [SCX] 1:09 Execute( IN: TableId 0x3, ParamCount 3 )
18-JUL-2007 09:53:04.031 0870 [SCX] 1:09 Param 1: VT_BSTR 0000005701C7C91DD0A6821A00000002R
18-JUL-2007 09:53:04.031 0870 [SCX] 1:09 Param 2: VT_BSTR 0000005701C7C91DCE1216B800000001R
18-JUL-2007 09:53:04.031 0870 [SCX] 1:09 Param 3: VT_BSTR 0000005701C7C91DCB04136800000000R
18-JUL-2007 09:53:04.031 0870 [SCX] 1:09 Generating initial sources
18-JUL-2007 09:53:04.031 0870 [SCX] 1:09 Building resultset
18-JUL-2007 09:53:04.031 0870 [SCX] 1:09 Resultset complete
18-JUL-2007 09:53:04.031 0870 [SCX] 1:09 Query complete
18-JUL-2007 09:53:04.031 0870 [TRANS] 0 SyncExecute Time = 0.000540 seconds
18-JUL-2007 09:53:04.031 0870 [TRANS] 0 QP Execute
18-JUL-2007 09:53:04.031 0870 [TRANS] 0 User Guy
18-JUL-2007 09:53:04.031 0870 [TRANS] 0 ParamCount 3
18-JUL-2007 09:53:04.031 0870 [TRANS] 0 Param 0: VT_BSTR 0000005701C7C91DD0A6821A00000002R
18-JUL-2007 09:53:04.031 0870 [TRANS] 0 Param 1: VT_BSTR 0000005701C7C91DCE1216B800000001R
18-JUL-2007 09:53:04.031 0870 [TRANS] 0 Param 2: VT_BSTR 0000005701C7C91DCB04136800000000R
18-JUL-2007 09:53:04.031 0870 [TRANS] 0 Status 0 -> Ok.
18-JUL-2007 09:53:04.031 0870 [SCX] 1:09 Execute( OUT: Hr 0 -> Ok. )
18-JUL-2007 09:53:04.031 0A64 [SCX] 1:09 Write complete, sent 4, remaining 0
18-JUL-2007 09:53:04.031 0A64 [SCX] 1:09 Read complete, received 9
18-JUL-2007 09:53:04.031 0A64 [SCX] 1:09 GetResultset( IN: TableId 0x3, Dynaset FALSE )
18-JUL-2007 09:53:04.031 0A64 [SCX] 1:09 GetResultset( OUT: hr 0 -> Ok. )
18-JUL-2007 09:53:04.031 0A64 [SCX] 1:09 Write complete, sent 510, remaining 0
18-JUL-2007 09:53:04.031 0A64 [SCX] 1:09 Read complete, received 10
18-JUL-2007 09:53:04.031 0A64 [SCX] 1:09 Advise( IN: Port 5500, ClientId 41 )
18-JUL-2007 09:53:04.031 0A64 [SVRADVISE] 1:06 SendEvent: Events 1, ClientId 41, IsUnadvise false
18-JUL-2007 09:53:04.031 01A4 [SVRADVTHR] 1 SendEvent
18-JUL-2007 09:53:04.031 01A4 [SVRADVISE] 1:06 ProcessWrite-1: Events 1, Links 4
18-JUL-2007 09:53:04.031 01A4 [SVRADVISE] 1:06 ProcessWrite-2: Sent 12 bytes to ClientId 41, IsUnadvise false
18-JUL-2007 09:53:04.031 01A4 [SVRADVISE] 1:06 ProcessWrite-3: Events 0, Links 4
18-JUL-2007 09:53:04.031 0A64 [SCX] 1:09 Advise( OUT: Hr 0 -> Ok., AdviseId 1:06 )
18-JUL-2007 09:53:04.031 0A64 [SCX] 1:09 Write complete, sent 0, remaining 0
18-JUL-2007 09:53:04.031 0A64 [SCX] 1:09 Read complete, received 8
18-JUL-2007 09:53:04.031 0870 [SCX] 1:09 AdviseSink( IN: TableId 0x3 )
18-JUL-2007 09:53:04.031 0870 [SCX] 1:09 AdviseSink( OUT: hr 14009D -> Query does not support interest., Cookie 0 )
18-JUL-2007 09:53:04.031 0A64 [SCX] 1:09 Write complete, sent 4, remaining 0
18-JUL-2007 09:53:04.031 0A64 [SCX] 1:09 Read complete, received 8
18-JUL-2007 09:53:04.031 0870 [SCX] 1:09 ClearResults( IN: TableId 0x3 )
18-JUL-2007 09:53:04.031 0870 [SCX] 1:09 ClearResults( OUT: hr 0 -> Ok. )
18-JUL-2007 09:53:04.031 0A64 [SCX] 1:09 Write complete, sent 0, remaining 0

Go: Home Back

How queries are optimised and executed by ViewX