InterSystems IRIS Data Platform 2020.4 > Class Reference > %SYS namespace > %SYS.PTools.SQLBenchMarkQueries


persistent class %SYS.PTools.SQLBenchMarkQueries extends %Library.Persistent

SQL Table Name: %SYS_PTools.SQLBenchMarkQueries

This Class gets populated from the %SYS.PTools.SQLStats.startSQLStats() method when gathering stats is turned on. There is more info on how to run Stats in %SYS.PTools.SQLStats.

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.

Property Inventory

Method Inventory


property JobNumber as %String;
Property methods: JobNumberDisplayToLogical(), JobNumberGet(), JobNumberGetStored(), JobNumberIsValid(), JobNumberLogicalToDisplay(), JobNumberLogicalToOdbc(), JobNumberNormalize(), JobNumberSet()
property NameSpace as %String;
Property methods: NameSpaceDisplayToLogical(), NameSpaceGet(), NameSpaceGetStored(), NameSpaceIsValid(), NameSpaceLogicalToDisplay(), NameSpaceLogicalToOdbc(), NameSpaceNormalize(), NameSpaceSet()
property PValues as array of %String;
Property methods: PValuesBuildValueArray(), PValuesCollectionToDisplay(), PValuesCollectionToOdbc(), PValuesDisplayToCollection(), PValuesDisplayToLogical(), PValuesGet(), PValuesGetObject(), PValuesGetObjectId(), PValuesGetStored(), PValuesGetSwizzled(), PValuesIsValid(), PValuesLogicalToDisplay(), PValuesLogicalToOdbc(), PValuesNormalize(), PValuesOdbcToCollection(), PValuesSet(), PValuesSetObject(), PValuesSetObjectId()
property QueryText as %String (MAXLEN = 15000);
Property methods: QueryTextDisplayToLogical(), QueryTextGet(), QueryTextGetStored(), QueryTextIsValid(), QueryTextLogicalToDisplay(), QueryTextLogicalToOdbc(), QueryTextNormalize(), QueryTextSet()
property RunTime as %TimeStamp;
Property methods: RunTimeDisplayToLogical(), RunTimeGet(), RunTimeGetStored(), RunTimeIsValid(), RunTimeLogicalToDisplay(), RunTimeNormalize(), RunTimeOdbcToLogical(), RunTimeSet()
property SQLIndexHash as %String (MAXLEN = 32);
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: Also, refer to the following methods in the %SYS.SQLSRVR.MAC routine: - SaveSQLIndex(...) - UnfreezePlan(...) - FreezePlanSchema(...) - CleanSQLIndex(...) - SaveSQLIndexImport(...) - FreezePlan(...) - FreezePlanRelation(...) - ... - UpdateSQLIndex(...) - FreezePlanAll(...) - DeleteSQLIndex(...)
Property methods: SQLIndexHashDisplayToLogical(), SQLIndexHashGet(), SQLIndexHashGetStored(), SQLIndexHashIsValid(), SQLIndexHashLogicalToDisplay(), SQLIndexHashLogicalToOdbc(), SQLIndexHashNormalize(), SQLIndexHashSet()


classmethod Parse(sql) as %String [ Language = objectscript ]
classmethod Purge(ns As %String = "", rtn As %String = "", ph3 As %Integer = 0, clearErrs As %Integer = 0, returnType As %Integer = 0, ByRef ptInfo As %ArrayOfObjects) as %Status [ Language = objectscript, SQLProc = PurgeBench ]
This method is called to remove SQL Benchmark Stats data from the following two classes: - %SYS.PTools.SQLBenchMarkQueries - %SYS.PTools.SQLBenchMarkResults Backward-compatible Entry Point: NOTE: For additional details, see the following methods in their respective aforementioned classes: - clearSQLBenchMarkQry() - clearSQLBenchMarkRslt()
classmethod Run(NameSpace As %String, Display As %Integer = 1) as %Status [ Language = objectscript ]

This is a simple example of what can be done with the data stored in this table.

This method 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) [ Language = objectscript ]
Perform the OnDelete Trigger operations for this class...
classmethod clearSQLBenchMarkQry(ns As %String = "", rtn As %String = "", ph3 As %Integer = 0, clearErrs As %Integer = 0, returnType As %Integer = 0, ByRef ptInfo As %ArrayOfObjects) as %Status [ Language = objectscript, SQLProc = 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 -------------------------------------------------------------------------------


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


trigger OnDelete (AFTER event DELETE);

Inherited Members

Inherited Methods


Storage Model: Storage (%SYS.PTools.SQLBenchMarkQueries)


Storage Model: Storage (%SYS.PTools.SQLBenchMarkQueries)