This is documentation for Caché & Ensemble.

For information on converting to InterSystems IRIS, see the InterSystems IRIS Adoption Guide and the InterSystems IRIS In-Place Conversion Guide, both available on the WRC Distributions page (login required).

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

Method Inventory

Properties

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

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 %Integer = 1, Display As %Integer = 0) as %Status [ SQLProc = TableScans ]

This method looks at all the SQL Queries in a namespace and list out the ones that are doing a table scan. The Table scan could be over an index or the master map.
For some queries a table scan can't be avoided, but any query in this list should be reviewed to see if an index could help.

Test this by invoking this procedure from an xDBC client:
  call %SYS_PTools.TableScans(1)
  SELECT %EXACT(Type), Count(*) As QueryCount FROM %SYS_PTools.SQLUtilities GROUP BY Type
  SELECT SQLPointer->Type As "Routine Type", SQLPointer->Name As "Routine Name", SchemaName, TableName, ModuleName, ExtentSize FROM %SYS_PTools.SQLUtilResults WHERE OptionName ='TS' ORDER BY ExtentSize DESC
  
This method can also be run from a command prompt:
  do ##class(%SYS.PTools.SQLUtilities).TableScans(1)
  
Then in $SYSTEM.SQL.Shell()
  SELECT %EXACT(Type), Count(*) As QueryCount FROM %SYS_PTools.SQLUtilities GROUP BY Type
  SELECT SQLPointer->Type As "Routine Type", SQLPointer->Name As "Routine Name", SchemaName, TableName, ModuleName, ExtentSize FROM %SYS_PTools.SQLUtilResults WHERE OptionName ='TS' ORDER BY ExtentSize DESC
  
classmethod TempIndices(PopTable As %Integer = 1, SystemTables As %Integer = 0, IgnoreEns As %Integer = 1, Display As %Integer = 0) as %Status [ SQLProc = TempIndices ]

This method looks at all the SQL queries in a namespace and displays any query that is building a temp table.
The results of this method and the Table scan method might have a large overlap.
Not all temp indices can be avoided, but often the structure of a temp indice could be the basis of a class index to help speed up the query.

Test this by invoking this procedure from an xDBC client:
  CALL %SYS_PTools.TempIndices(1)
  SELECT %EXACT(Type), Count(*) As QueryCount FROM %SYS_PTools.SQLUtilities GROUP BY Type
  SELECT SchemaName,TableName, IndexFields, DataValues, ExtentSize FROM %SYS_PTools.SQLUtilResults WHERE OptionName ='TI' ORDER BY ExtentSize DESC
  
This method can also be run from a command prompt:
  do ##class(%SYS.PTools.SQLUtilities).TempIndices(1)
  
Then in $SYSTEM.SQL.Shell()
  SELECT %EXACT(Type), Count(*) As QueryCount FROM %SYS_PTools.SQLUtilities GROUP BY Type
  SELECT SchemaName,TableName, $LISTTOSTRING(IndexFields) As "Index Fields", $LISTTOSTRING(DataValues) As "Data Fields", ExtentSize FROM %SYS_PTools.SQLUtilResults WHERE OptionName ='TI' ORDER BY ExtentSize DESC
  
classmethod map(data, mt, mts, alg, qnum, str)

Queries

query FindSQL()
Selects QueryType As %String, QueryCount As %Integer
SQL Query:
SELECT %EXACT(Type), Count(*) FROM %SYS_PTools.SQLUtilities GROUP BY Type
query IndexUsage(schema)
Selects SchemaName As %String, TableName As %String, IndexName As %String, Count As %Integer
SQL Query:
SELECT SchemaName, Tablename, IndexName, UsageCount FROM %SYS_PTools.SQLUtilResults WHERE OptionName ='IU' and (SchemaName = :schema or :schema IS NULL) ORDER BY UsageCount
query JoinIndices(schema)
Selects SchemaName As %String, TableName As %String, IndexNeeded As %String, JoinIndexFlag As %Integer, UsageCount As %Integer, SQLText As %String
SQL Query:
SELECT %Exact(SchemaName), %Exact(TableName), %Exact($LISTTOSTRING(IndexFields)) As IndexNeeded, JoinIndexFlag, (SELECT COUNT(*) FROM %SYS_PTools.SQLUtilResults Sub WHERE Sub.SchemaName= Main.SchemaName AND Sub.TableName = Main.Tablename AND Sub.IndexFields = Main.IndexFields AND Sub.JoinIndexFlag < 4) As UsageCount, SQLPointer->SQLText FROM %SYS_PTools.SQLUtilResults Main WHERE JoinIndexFlag < 4 AND OptionName ='JI' and (SchemaName = :schema or :schema IS NULL) GROUP BY JoinIndexFlag, SchemaName, TableName, IndexFields ORDER BY JoinIndexFlag, UsageCount DESC
query PossiblePlans(sql As %String)
Selects ID As %Integer, Cost As %Integer, MapType As %String(MAXLEN=15), StartingMap As %String(MAXLEN=30), Plan As %String(MAXLEN=15)
query PossiblePlansStats(sql As %String, ids As %String)
Selects ID As %Integer, Cost As %Integer, StartingMap As %String, GlobalRef As %Integer, Commands As %Integer, TotalTime As %Integer, RowsReturned As %Integer, Plan As %String
query TableScans(schema)
Selects SchemaName As %String, TableName As %String, Type As %String, Class/Routine Name As %String, ModuleName As %String, Map Type As %String, ExtentSize As %Integer, Block Count As %Integer, SQLText As %String
SQL Query:
SELECT SchemaName, TableName, SQLPointer->Type, SQLPointer->Name, ModuleName, MapType, ExtentSize, BlockCount, SQLPointer->SQLText FROM %SYS_PTools.SQLUtilResults WHERE OptionName ='TS' and (SchemaName = :schema or :schema IS NULL) ORDER BY BlockCount DESC
query TempIndices(schema)
Selects SchemaName As %String, TableName As %String, Type As %String, Class/Routine Name As %String, IndexFields As %String, DataFields As %String, ExtentSize As %Integer, Block Count As %Integer, SQLText As %String
SQL Query:
SELECT SchemaName, TableName, SQLPointer->Type, SQLPointer->Name, $LISTTOSTRING(IndexFields) As "Index Fields", $LISTTOSTRING(DataValues) As "Data Fields", ExtentSize, BlockCount, SQLPointer->SQLText FROM %SYS_PTools.SQLUtilResults WHERE OptionName ='TI' and (SchemaName = :schema or :schema IS NULL) ORDER BY BlockCount DESC

Inherited Members

Inherited Methods

Storage

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

^%sqlcq($NAMESPACE,"PTools","Utils","Queries","D")(ID)
=
%%CLASSNAME
Type
Name
SQLText
ImportPackage
FeedbackOpens in a new window