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:
INSTANCESThe 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.
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:
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:
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:
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