%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.Init() 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, 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 CACHE DB you need to have %All to execute the Run() method.

Method Inventory

Parse()
Purge()
Run()

Properties

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()

Methods

classmethod Parse(sql) as %String

classmethod Purge() as %Status [ SQLProc = PurgeBench ]

Projected as the stored procedure: 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.

Queries

query QueryWithParameters()

SQL Query as view "Query_With_Parameters":
SELECT P.Id, P.NameSpace, P.QueryText, P.RunTime, P.JobNumber, 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()

Inherited Members

Inherited Methods

Storage

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

^%sqlcq($NAMESPACE,"PTools","Bench","Queries","D")(ID,"PValues",n)

PValues(n)

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

^%sqlcq($NAMESPACE,"PTools","Bench","Queries","D")(ID)

%%CLASSNAME

QueryText

NameSpace

RunTime

JobNumber