Class Reference
IRIS for UNIX 2019.2
InterSystems: The power behind what matters   
Documentation  Search
  [%SYS] >  [%SYS] >  [PTools] >  [SQLBenchMarkQueries]
Private  Storage   

persistent class %SYS.PTools.SQLBenchMarkQueries extends %Persistent

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.


Parameters Properties Methods Queries Indices ForeignKeys Triggers
6 3 1


JobNumber NameSpace PValues QueryText RunTime

%AddToSaveSet %AddToSyncSet %BMEBuilt %CheckConstraints
%CheckConstraintsForExtent %ClassIsLatestVersion %ClassName %ComposeOid
%ConstructClone %Delete %DeleteExtent %DeleteId
%DispatchClassMethod %DispatchGetModified %DispatchGetProperty %DispatchMethod
%DispatchSetModified %DispatchSetMultidimProperty %DispatchSetProperty %Exists
%ExistsId %Extends %GUID %GUIDSet
%GetLock %GetParameter %GetSwizzleObject %Id
%InsertBatch %IsA %IsModified %IsNull
%KillExtent %KillExtentData %LoadFromMemory %LockExtent
%LockId %New %NormalizeObject %ObjectIsNull
%ObjectModified %Oid %OnBeforeAddToSync %OnDetermineClass
%Open %OpenId %OriginalNamespace %PackageName
%PhysicalAddress %PurgeIndices %Reload %RemoveFromSaveSet
%ResolveConcurrencyConflict %RollBack %Save %SaveDirect
%SaveIndices %SerializeObject %SetModified %SortBegin
%SortEnd %SyncObjectIn %SyncTransport %UnlockExtent
%UnlockId %ValidateIndices %ValidateObject Parse
Purge Run


• property JobNumber as %String;
• property NameSpace as %String;
• property PValues as array of %String;
• property QueryText as %String(MAXLEN=15000);
• property RunTime as %TimeStamp;
• 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(...)


• classmethod Parse(sql) as %String
• classmethod Purge() as %Status [ SQLProc = PurgeBench ]
This method is called to remove data from the %SYS.PTools.SQLBenchMarkQueries and %SYS.PTools.SQLBenchMarkResults.
• classmethod Run(NameSpace As %String, Display As %Integer = 1) as %Status

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.


• 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

Copyright (c) 2019 by InterSystems Corporation. Cambridge, Massachusetts, U.S.A. All rights reserved. Confidential property of InterSystems Corporation.