BDE SQL Profiler Results - Database Structure

Applies to AQTime 8.81, last modified on November 27, 2020

When you export the BDE SQL Profiler results to a database, AQTime creates a number of tables in it. The contents of most of these tables correspond to the contents of AQTime panels in which the profiling results are displayed. This topic describes the structure of database table that contain the exported BDE SQL profiler results.

The exported results are stored to the following tables:

The relationship between the mentioned tables is illustrated in the image below. This relationship is similar to the relationship of the data in AQTime’s Explorer, Report and Details panels that contains the BDE SQL profiler results.

Relationship between database tables

Click the image to enlarge it.

The “root” table of the database is the INSTANCES table. It contains the list of all exported result sets and is shared by profilers. Each result set is identified by the INST_ID field value, which is unique among the table. All other database tables also have the INST_ID field that indicates the result set to which data of a specific table item belongs.

The BDE_SQL_PROFILER_SQL_QUERIES table corresponds to the result set that is displayed in the Report panel. Each row in this table corresponds to an SQL query or stored procedure that was executed during the profiling.

The BDE SQL profiler organizes results by threads. The BDE_SQL_PROFILER_THREADS table contains the list of application threads traced by the profiler. The BDE_SQL_PROFILER_SQL_QUERIES table have the PARENT_ID field that indicates the thread, in which the SQL query or stored procedure was executed.

For each SQL query that is shown in the Report panel, AQTime displays the call stack in the Details panel. The call stack data is exported to the BDE_SQL_PROFILER_CALL_STACK table. The PARENT_ID filed of this table specifies the identifier of the SQL query, to which the call stack item belongs. That is, the values stored in the PARENT_ID field correspond to the REC_ID field values stored in the BDE_SQL_PROFILER_SQL_QUERIES table.

The BDE_SQL_PROFILER_CALL_STACK table does not hold information about the routines included in the stack. Instead, its COL_ROUTINERID field contains identifier of the routine. Using this identifier you can retrieve information about the routine from the BDE_SQL_PROFILER_META_ROUTINES table.

Note: The ID and REC_ID columns of the BDE_SQL_PROFILER_xxxx tables are not key columns. They may contain repetitive values. In queries you should use these values along with the INST_ID value (the result set identifier).

Below is detailed information about the tables.

The INSTANCES table

This table contains the list of exported result sets. Each result set is identified by the INST_ID value.

Field Name Description
CAPTION String. The name of the result set.
COUNTER_DESCRIPTION Not used for the BDE SQL Profiler.
COUNTER_FREQUENCY Not used for the BDE SQL Profiler.
COUNTER_NAME Not used for the BDE SQL Profiler.
INST_ID Integer. The result set’s identifier.

The BDE_SQL_PROFILER_CALL_STACK table

This table contains information about the sequence of function calls that led to the execution of SQL queries. Each row in it corresponds to a function call. The data stored in this table is used to display the Call Stack table in the Details panel (see BDE SQL Profiler - Details Panel).

Field Name Description
COL_HITCOUNT Integer. The number of times the function was executed during the application execution by the moment it was included into the given call sequence. This number includes the call that belongs to the given sequence. This is the same value that is displayed in the Hit Count column of the Call Stack table in the Details panel.
COL_ROUTINERID Integer. The identifier of the function that was called. This is the same value that is stored in the REC_ID column of the BDE_SQL_PROFILER_META_ROUTINES table. You can use this identifier to retrieve information about the function from this meta table.
COL_SOURCE_LINE Integer. The source line at which the routine’s implementation starts in the source file. This is the same value that is displayed in the Source Line column of the Call Stack table in the Details panel.
ID Integer. The identifier of the row in the table.
INST_ID Integer. The identifier of the result set to which the row belongs. This value corresponds to the result set’s INST_ID value stored in the INSTANCES table.
PARENT_ID Integer. The identifier of the SQL query to which the call relates. This value corresponds to the ID field of the BDE_SQL_PROFILER_SQL_QUERIES table.
REC_ID Integer. The position of the function call in the stack relative to other calls. 0 means the topmost routine in the call stack.

The BDE_SQL_PROFILER_META_ROUTINES table

This table contains information about routines that are listed in the Call Stack table of the Details panel when it is displaying results of the BDE SQL Profiler (see BDE SQL Profiler - Details Panel).

Field Name Description
COL_ACTIONACTIVE Integer. Specifies whether the routine is an active action. This field contains -1 if the routine is an action and this action is enabled. Otherwise it contains 0. This value is not displayed in the Call Stack table.
COL_ACTIONDISABLEROUTINE Not used.
COL_ACTIONPLACEMENT Integer. Specifies the action’s execute type. This field can contain one of the following values:
  • 0 - The action is executed at the beginning of the routine.
  • 1 - The action is executed at the end of the routine.

This value is not displayed in the Call Stack table.

COL_ACTIONTYPE Integer. Specifies the action type. This field can contain one of the following values:
  • 0 - Enable profiling
  • 1 - Disable profiling
  • 2 - Get results
  • 3 - Clear results

This value is not displayed in the Call Stack table.

COL_ADDRESS Decimal. The relative-virtual address of the function in memory. This value is not displayed in the Call Stack table.
COL_ANALYSIS_RESULT String. Specifies whether the function was instrumented or not. If it was, the field is empty. Else, the filed contain of the following strings that specify the cause of why the instrumentation failed:
  • Less than 5 bytes (for 32-bit applications)
  • Less than 6 bytes (for 64-bit applications)
  • No line info
  • Unsafe code
  • No ret instruction
  • Duplicated code

This field is not shown in the Call Stack table of the Details panel.

COL_CLASS_NAME String. The type of the class to which the function belongs. This is the same value that is displayed in the Class Name column of the Call Stack table in the Details panel.
COL_CODE_TYPE String. Specifies the type of the function’s code (x86, x64 and so on). This value is not shown in the Call Stack table of the Details panel.
COL_HIT_COUNT String. Specifies the total number of times the function was called during the profiling. This value is not shown in the Call Stack table of the Details panel.
COL_MODULE_NAME String. The fully-qualified name of the module that contains the routine’s implementation. This value is used to display the module name in the Module Name column of the Call Stack table in the Details panel.
COL_SOURCE_FILE String. The name of the file that contains the function’s code. This is the same value that is displayed in the Source File column of the Call Stack table.
COL_SOURCE_LINE Integer. The number of the source line at which the function’s code starts in the source file.
COL_PROFILELINES Integer. Used for helper purposes. Always contains 0.
COL_ROUTINE_NAME String. Specifies the function’s name. This is the same value that is displayed in the Routine Name column of the Call Stack table in the Details panel.
COL_SYMBOL_MONIKER String. Used for helper purposes.
COL_TRIGGERACTIVE Integer. Specifies whether a routine is an active trigger. This field contains -1 if the routine is a trigger and this trigger is enabled; otherwise it contains 0. This value is not displayed in the Call Stack table.
COL_TRIGGERCYCLING Integer. Specifies whether the routine is a cycling trigger. This field contains -1 is the trigger’s Cycling option is checked; otherwise it contains 0. This value is not displayed in the Call Stack table.
COL_TRIGGERDISABLEROUTINE Not used.
COL_TRIGGERENABLING Integer. Specifies whether the routine is an on-trigger or an off-trigger. If the routine is an on-trigger, this field contains -1. Otherwise, if the routine is an off-trigger or not a trigger, it contains 0. This value is not displayed in the Call Stack table.
COL_TRIGGERGLOBAL Integer. Specifies whether the trigger’s call count is taken over all threads. If the trigger’s For All Threads option is checked, this field contains -1. Otherwise it contains 0. This value is not displayed in the Call Stack table.
COL_TRIGGERGLOBALHITCOUNT Not used.
COL_TRIGGERPASSCOUNT Integer. The trigger’s pass count. This value is not displayed in the Call Stack table.
COL_TRIGGERWORKCOUNT Integer. The trigger’s work count. This value is not displayed in the Call Stack table.
COL_UNIT_NAME String. Specifies the name of the unit that contains the function’s code. This value is not displayed in the Call Stack table.
ID Integer. The identifier of the row in the table.
INST_ID Integer. The identifier of the result set to which the row belongs. This is the same value as the result set’s INST_ID value in the INSTANCES table.
PARENT_ID Integer. Not used.
REC_ID Integer. The identifier of the function in the result set.

The BDE_SQL_PROFILER_SQL_QUERIES table

This table contains information about SQL queries or stored procedures that were executed by the application. Each row in it corresponds to an executed SQL query or stored procedure. The data stored in this table is used to display the BDE SQL Profiler results in the Report panel (see BDE SQL Profiler - Report Panel).

Field Name Description
COL_CLASS_NAME String. The type of the VCL class that performed the query. This is the same value that is displayed in the Class Name column of the Report panel.
COL_INDEX Integer. The routine’s ordinal number. This is the same value that is shown in the # column of the Report panel.
COL_OBJECT_NAME String. Name of the object that represents the query or stored procedure. This is the same value that is shown in the Object Name column of the Report panel.
COL_OPERATION_TYPE String. The type of database operation. This is the same value that is displayed in the Operation Type column of the Report panel.
COL_SQL_EXPRESSION String. The code of the executed SQL query or stored procedure. This is the same value that is displayed in the SQL Expression column of the Report panel.
COL_TIME Integer. The execution time of the query or stored procedure in machine cycles. This is the same value that is displayed in the Time column of the Report panel (AQTime automatically converts this value to other units: seconds, milliseconds or microseconds).
ID Integer. The identifier of the call in the table.
INST_ID Integer. The identifier of the result set to which the row belongs. This is the same value as the result set’s INST_ID value in the INSTANCES table.
PARENT_ID Integer. The identifier of the thread in which the SQL query or stored procedure was executed. This value corresponds to the ID field in the BDE_SQL_PROFILER_THREADS table.
REC_ID Integer. The identifier of the call in the result set.

The BDE_SQL_PROFILER_THREADS table

The BDE SQL profiler groups SQL queries in profiler results by threads. This table contains information about threads.

Field Name Description
COL_WIN32THREADID String. The identifier of the thread.
ID Integer. The identifier of the row in the table.
INST_ID Integer. The identifier of the result set to which the row belongs. This value corresponds to the INST_ID field of the INSTANCES table.
PARENT_ID Integer. Reserved.
REC_ID Integer. The identifier of the thread in the result set.

The RELATIONS table

This table contains the information about parent-child relationships between the database tables.

Field Name Description
COL_CHILD_TABLE String. The name of a child table.
COL_PARENT_TABLE String. The name of a parent table. If this field contains NULL, it means that the table specified by the COL_CHILD_TABLE is a top-level table.
ID Integer. The identifier of a specific relation within the RELATIONS table.
INST_ID Integer. The identifier of the INSTANCES table item corresponding to the result set whose data the tables hold.

See Also

Exporting Profiling Results to Database
BDE SQL Profiler - Overview

Highlight search results