Home > Class Reference > ENSLIB namespace > %SYS.PTools.SQLUtilities

%SYS.PTools.SQLUtilities

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

SQL Table Name: %SYS_PTools.SQLUtilities

Property Inventory (Including Private)

Method Inventory (Including Private)

Properties (Including Private)

property ImportPackage as %String;
comma delimited list of Package names to use compiling queries
Property methods: ImportPackageDisplayToLogical(), ImportPackageGet(), ImportPackageGetStored(), ImportPackageIsValid(), ImportPackageLogicalToDisplay(), ImportPackageLogicalToOdbc(), ImportPackageNormalize(), ImportPackageSet()
property Name as %String;
Property methods: NameDisplayToLogical(), NameGet(), NameGetStored(), NameIsValid(), NameLogicalToDisplay(), NameLogicalToOdbc(), NameNormalize(), NameSet()
property SQLText as %Stream.GlobalCharacter;
Property methods: SQLTextDelete(), SQLTextGet(), SQLTextGetObject(), SQLTextGetObjectId(), SQLTextGetStored(), SQLTextGetSwizzled(), SQLTextIsValid(), SQLTextNewObject(), SQLTextOid(), SQLTextOpen(), SQLTextSet(), SQLTextSetObject(), SQLTextSetObjectId(), SQLTextUnSwizzle()
property Type as %String;
Property methods: TypeDisplayToLogical(), TypeGet(), TypeGetStored(), TypeIsValid(), TypeLogicalToDisplay(), TypeLogicalToOdbc(), TypeNormalize(), TypeSet()

Methods (Including Private)

classmethod BuildPPcost(qoqn, dmt, dmts, dalg, PPcost)
classmethod ChangeCost(PPcost, num, level, dmt, dmts, dalg)
classmethod ClearResults() as %Status
classmethod ClearStatements() as %Status
classmethod GetSQLStatements(cachedQueries=1, classQueries=1, classMethods=1, routines=1, SystemTables, Display=0) as %Status
This Method returns a ResultSet that contains: Location, Type, SQLText
classmethod IndexUsage(PopTable As %Integer = 1, SystemTables As %Integer = 0, IgnoreEns As %Integer = 1, IgnoreIDKeys As %Integer = 1, Display As %Integer = 0) as %Status [ SQLProc = IndexUsage ]

This method finds all the SQL Queries in a namespace, generates a Show Plan for each one, keeps a count of the indices used and then lists the totals for all the indices in the namespace.
This can be used to find and remove unneeded indices.

Test this by invoking this procedure from an xDBC client:
  call %SYS_PTools.IndexUsage(1)
  SELECT %EXACT(Type), Count(*) As QueryCount FROM %SYS_PTools.SQLUtilities GROUP BY Type
  SELECT SchemaName, Tablename, IndexName, UsageCount FROM %SYS_PTools.SQLUtilResults WHERE OptionName ='IU' ORDER BY UsageCount
  
This method can also be run from a command prompt:
  do ##class(%SYS.PTools.SQLUtilities).IndexUsage(1)
  
Then in $SYSTEM.SQL.Shell()
  SELECT %EXACT(Type), Count(*) As QueryCount FROM %SYS_PTools.SQLUtilities GROUP BY Type
  SELECT SchemaName, Tablename, IndexName, UsageCount FROM %SYS_PTools.SQLUtilResults WHERE OptionName ='IU' ORDER BY UsageCount
  
classmethod JoinIndices(PopTable As %Integer = 1, SystemTables As %Integer = 0, IgnoreEns As %Integer = 1, Display As %Integer = 0) as %Status [ SQLProc = JoinIndices ]

This method looks at all the SQL queries on a system.
For any query doing a JOIN it will look at the fields from both tables that are part of the join and then see if there is an index that supports the join.

The Property JoinIndexFlag has 4 values:
    0 - No index to support the join. Some version of the suggested index should be created to improve this queries performance.
    1 - There is an index to support the join but it does not contain all the join fields. This will produce poor performance and for that reason is rarely used.
    2 - There is an index to support the join but it is not an exact match, the first index field is not part of the join. This might produce OK performance, but improvements should be made.
    3 - There is an index to support the join but it is not an exact match, the first index field is part of the join but there are additional fields. This will produce OK performance, but improvements can be made.
    4 - Index fully supports the join. This is not included in the table as there is nothing to improve.

Creating a new index in the case of JoinIndexFlag = 0 or 1 should show good performance gains
Creating a new index for JoinIndexFlag = 2 will help, improvements will depend on the number of leading subscripts and their selectivity
Creating a new index for JoinIndexFlag = 3 could help, in most cases you will only see small improvements.

The property IndexFields would be an index we think could help improve performance.
The Order of the subscripts does not matter for the join, but could make a difference in performance.
The property with the lowest selectivity should be first.

Test this by invoking this procedure from an xDBC client:
  call %SYS_PTools.JoinIndices(1)
  SELECT %EXACT(Type), Count(*) As QueryCount FROM %SYS_PTools.SQLUtilities GROUP BY Type
  SELECT SchemaName, TableName, IndexFields As IndexNeeded, JoinIndexFlag FROM %SYS_PTools.SQLUtilResults WHERE OptionName ='JI' ORDER BY 1,2,3
  
This method can also be run from a command prompt:
  do ##class(%SYS.PTools.SQLUtilities).JoinIndices(1)
  
Then in $SYSTEM.SQL.Shell()
  SELECT %EXACT(Type), Count(*) As QueryCount FROM %SYS_PTools.SQLUtilities GROUP BY Type
  SELECT SchemaName, TableName, IndexFields As IndexNeeded, JoinIndexFlag, COUNT(*) AS QueryCount 
  FROM %SYS_PTools.SQLUtilResults
  WHERE JoinIndexFlag < 4 and OptionName ='JI' 
  GROUP BY SchemaName, TableName, IndexFields
  ORDER BY 4,5 DESC
  
classmethod PossiblePlans(sql, PPcost=0, num=0, level=0, arr, showstats=0, packages, schemapath="", preparse=0, hash="")
classmethod PossiblePlansClose(ByRef qHandle As %Binary) as %Status
classmethod PossiblePlansExecute(ByRef qHandle As %Binary, sql As %String) as %Status
classmethod PossiblePlansFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) as %Status
classmethod PossiblePlansStatsClose(ByRef qHandle As %Binary) as %Status
classmethod PossiblePlansStatsExecute(ByRef qHandle As %Binary, sql As %String, ids As %String) as %Status
classmethod PossiblePlansStatsFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) as %Status
classmethod TableScans(PopTable As %Integer = 1, SystemTables As %Integer = 0, IgnoreEns As