%SYS.PTools.SQLUtilities
persistent class %SYS.PTools.SQLUtilities extends %Library.Persistent
SQL Table Name: %SYS_PTools.SQLUtilities
Property Inventory
Method Inventory
- BuildPPcost()
- ChangeCost()
- ClearResults()
- ClearStatements()
- GetSQLStatements()
- IndexUsage()
- JoinIndices()
- PossiblePlans()
- PossiblePlansClose()
- PossiblePlansExecute()
- PossiblePlansFetch()
- PossiblePlansStatsClose()
- PossiblePlansStatsExecute()
- PossiblePlansStatsFetch()
- TableScans()
- TempIndices()
- map()
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 ]
Projected as the stored procedure: 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
do ##class(%SYS.PTools.SQLUtilities).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
classmethod JoinIndices(PopTable As %Integer = 1, SystemTables As %Integer = 0, IgnoreEns As %Integer = 1, Display As %Integer = 0) as %Status [ SQLProc = JoinIndices ]
Projected as the stored procedure: 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
do ##class(%SYS.PTools.SQLUtilities).JoinIndices(1)
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 PossiblePlansFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) 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 ]
Projected as the stored procedure: 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
do ##class(%SYS.PTools.SQLUtilities).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
classmethod TempIndices(PopTable As %Integer = 1, SystemTables As %Integer = 0, IgnoreEns As %Integer = 1, Display As %Integer = 0) as %Status [ SQLProc = TempIndices ]
Projected as the stored procedure: 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
do ##class(%SYS.PTools.SQLUtilities).TempIndices(1)
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()
SQL Query:
SELECT %EXACT(Type), Count(*) FROM %SYS_PTools.SQLUtilities GROUP BY Type
SELECT %EXACT(Type), Count(*) FROM %SYS_PTools.SQLUtilities GROUP BY Type
query IndexUsage(schema)
SQL Query:
SELECT SchemaName, Tablename, IndexName, UsageCount FROM %SYS_PTools.SQLUtilResults WHERE OptionName ='IU' and (SchemaName = :schema or :schema IS NULL) ORDER BY UsageCount
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
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)
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
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
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
Indexes
index (IDKEY on ) [IdKey, Type = key];
Index methods: IDKEYCheck(), IDKEYDelete(), IDKEYExists(), IDKEYOpen(), IDKEYSQLCheckUnique(), IDKEYSQLExists(), IDKEYSQLFindPKeyByConstraint(), IDKEYSQLFindRowIDByConstraint()
Inherited Members
Inherited Methods
- %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()
Storage
Storage Model: CacheStorage (%SYS.PTools.SQLUtilities)
^%sqlcq($NAMESPACE,"PTools","Utils","Queries","D")(ID) | = | %%CLASSNAME
Type
Name
SQLText
ImportPackage
|