The class contains the SQLText, the Job Number, the Time Stamp from when the query was run, the SQLIndexHash, which is a unique ID for the SQL Statement Index, and the Parameters that were used.
The Method Run() will loop over all the saved queries and execute them one after the other.
Because ^%SYS.PTools.BenchMarkResults stores data in the "IRISLOCALDATA" database you need to have %All to execute the Run() method.
Internal unique statement hash used as the ID for the SQL Statement Index (DPV4651):
^rINDEXSQL("sqlidx",1,{SQLIndexHash},...)
^rINDEXSQL("sqlidx",2,{rtn},{SQLIndexHash})
^rINDEXSQL("sqlidx",3,{table},{SQLIndexHash})
For additional details, refer to the following: http://twiki.iscinternal.com/twiki/bin/view/ISC/SQL20Statement20Indexing
Also, refer to the following methods in the %SYS.SQLSRVR.MAC routine:
- SaveSQLIndex(...) - UnfreezePlan(...) - FreezePlanSchema(...) - CleanSQLIndex(...)
- SaveSQLIndexImport(...) - FreezePlan(...) - FreezePlanRelation(...) - ...
- UpdateSQLIndex(...) - FreezePlanAll(...) - DeleteSQLIndex(...)
This is a simple example of what can be done with the data stored in this table.
This methods take 2 parameters:
NameSpace - the NameSpace where you want to run the Benchmark
Display - 1 or 0 to control output to the screen
This method will loop over the queries that are stored in this classes
and execute them as Dynamic SQL statements
SQLStats is turned off for this run and the state is returned when the code finishes
The code will display and save data for fetching the first row and for fetching all the rows
The data saved will be: Global Refs, Lines of Code, Rows Returned, and Total Time.
All the results are stored in the %SYS.SQLBenchMarkResults class.
classmethod TriggerOnDelete(timing)
Perform the OnDelete Trigger operations for this class...
deprecated classmethod clearSQLBenchMarkQry(ns As %String = "", rtn As %String = "", ph3 As %Integer = 0, clearErrs As %Integer = 0, returnType As %Integer = 0, ByRef ptInfo As %RawString) as %Status [ SQLProc = PT_clearSQLBenchMarkQry ]
Projected as the stored procedure: PT_clearSQLBenchMarkQry
-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --
Method: clearSQLBenchMarkQry [SQL: PT_clearSQLBenchMarkQry]
Replaced BY: N/A
Status: New Functionality
Purpose: Delete all of the data stored in the '%SYS.PTools.SQLBenchMarkQueries'
class, based on the specified parameters...
Invocation: This method can be invoked in the following ways:
ObjectScript: ##class(%SYS.PTools.SQLBenchMarkQueries).clearSQLBenchMarkQry(...)
SQL: CALL %SYS_PTools.PT_clearSQLBenchMarkQry(...)
SELECT %SYS_PTools.PT_clearSQLBenchMarkQry(...)
NOTE: This 'SqlProc' method can be invoked with either the
CALL-interface or the SELECT-interface. Both interfaces
execute the method: the CALL-interface does not return
any resulting value, while the SELECT-interface does.
Examples: The following examples shows the use of this method:
#1 Delete all of the data stored in the '%SYS.PTools.SQLBenchMarkQueries'
class in the 'SAMPLES' namespace:
set stats=##class(%SYS.PTools.SQLBenchMarkQueries).clearSQLBenchMarkQry("SAMPLES")
Data Storage: ^%sqlcq($NAMESPACE,"PTools","Bench","Queries","{D|I|C|S}")
Parameters:
ns - The namespace in which to clear SQL Statistics
If none provided, use the current namespaces where SQL Stats collected
[DEFAULT: Current Namespace]
rtn - The routine for which to clear SQL Statistics
If none provided, clear all routines in the given 'ns'
[OPTIONAL]
ph3 - Placeholder Parameter for future extensibility
clearAll - 0 = Perform a normal clear, but leave the 'INFO' rows
1 = Clear everything including the 'INFO' rows
[DEFAULT: 0]
clearErrs - 0 = Don't delete the 'PTools' Application Errors
1 = Delete all of the 'PTools' Application Errors
[DEFAULT: 0]
returnType - 0 = Return a %Status code of either $$$OK or $$$ERROR()
1 = Return the number of Stats rows deleted from the
following class: %SYS.PTools.SQLBenchMarkQueries
[DEFAULT: 0]
ptInfo - A Pass By Reference information array that returns
detailed information to the user in the following format:
ptInfo(category,variable)=value OR $LB(val1,...,valn)
Where category = { "cnt" | "curr" | "term" | ... }
variable = A var corresponding to the given 'category'
Example:
ptInfo("cnt","clearSQLBenchMarkQry")=The number of rows deleted
via this method [Routine Method]
RETURN Value: Based on the value of the 'returnType' parameter, return one of
the following:
0: Return a %Status code of either $$$OK or $$$ERROR()
1: Return the number of Stats rows deleted from the following class;
Otherwise, return an error message if an error occurred:
%SYS.PTools.SQLBenchMarkQueries
Queries
query QueryWithParameters()
SQL Query as view "Query_With_Parameters": SELECT P.Id, P.NameSpace, P.QueryText, P.RunTime, P.JobNumber, P.SQLIndexHash, PV.PValues
FROM %SYS_PTools.SQLBenchMarkQueries P
LEFT OUTER JOIN %SYS_PTools.SQLBenchMarkQueries_PValues PV ON P.ID = PV.SQLBenchMarkQueries
Indexes
index (IDKEY on ) [IdKey, Type = key];
Index methods: IDKEYCheck(), IDKEYDelete(), IDKEYExists(), IDKEYOpen(), IDKEYSQLCheckUnique(), IDKEYSQLExists(), IDKEYSQLFindPKeyByConstraint(), IDKEYSQLFindRowIDByConstraint()
