Bulk disable of indexes

Recently we did some performance assessment of our system using DynamicsPerf and identified unused indexes. Some of these indexes were heavily updated but were not used. So a decision was made to disable these indexes. The indexes were in a csv file (in the format tableName, indexName).

Following script was used to disable the indexes instead of manually doing the task. As we use source control, the script checks if the table is in source control and if so, it checks it out before disabling it. If not it adds the table to source control after disabling the index. It also creates a private project with all the affected tables added to it. DB synchronization will have to be done manually.

 static void readAndDisableIndexes(Args _args)  
 {  
   #AOT  
   #File  
   #SysVersionControl  
   
   TreeNodePath        path;  
   TextIo              file;  
   container           data;  
   FileIOPermission    permission;  
   int                 totalTableAdded, totalIndexesDisabled;  
   str                 fileName = 'C:\\Temp\\UnUsedIndex.csv';  
   Set                 set = new Set(Types::String);  
   Set                 setAddedToVS = new Set(Types::String);  
   boolean             inVS;  
   boolean             vcsEnabled = SysVersionControlParameters::isVCSEnabled();  
   
   UtilElements        currUtilElement;  
   IdentifierName      tableName, indexToDisable;  
   IdentifierName      projectName = 'MSPerfPorject';  
   
   ProjectSharedPrivate    projectType = ProjectSharedPrivate::ProjPrivate;  
   ProjectNode             projectNode = SysTreeNode::getPrivateProject().AOTfindChild(projectName);  
   SysVersionControlSystem vcsSys = VersionControl.parmSysVersionControlSystem();  
   
   ProjectNode createProject(IdentifierName _projectName, ProjectSharedPrivate _projectType)  
   {  
     ProjectNode projectNodeLoc = SysTreeNode::getPrivateProject().AOTfindChild(_projectName);  
   
     info(strFmt('Project name : %1', _projectName));  
     if (projectNodeLoc)  
     {  
       projectNodeLoc.AOTdelete();  
       info('Project deleted as it already existed.');  
     }  
   
     projectNodeLoc = SysTreeNode::createProject(_projectName, _projectType);  
     info('Project created.');  
   
     return projectNodeLoc;  
   }  
   
   void addTableToProject(ProjectNode _projectNode, IdentifierName _tableToAddName)  
   {  
     _projectNode.addUtilNode(UtilElementType::Table, _tableToAddName);  
     info(strFmt('Table added: %1', _tableToAddName));  
   }  
   
   boolean checkoutIfInVS(SysVersionControlSystem _vcsSys, UtilElements _utilElement)  
   {  
     boolean ret = false;  
     TreeNode treeNode;  
     SysVersionControllable controlable;  
   
     treeNode = SysTreeNode::findNodeInLayer(_utilElement.recordType, _utilElement.name, _utilElement.parentId, currUtilElement.utilLevel);  
     controlable = SysTreeNode::newTreeNode(treeNode);  
   
     if(!_vcsSys.allowCreate(controlable))  
     {  
       ret = true;  
       _vcsSys.commandCheckOut(controlable);  
       info('Table checked out as it is part of VCS.');  
     }  
   
     return ret;  
   }  
   
   void addToVS(SysVersionControlSystem _vcsSys, UtilElements _utilElement)  
   {  
     TreeNode treeNode;  
     SysVersionControllable controlable;  
   
     treeNode = SysTreeNode::findNodeInLayer(_utilElement.recordType, _utilElement.name, _utilElement.parentId, currUtilElement.utilLevel);  
     controlable = SysTreeNode::newTreeNode(treeNode);  
   
     if(_vcsSys.allowCreate(controlable))  
     {  
       _vcsSys.commandAdd(controlable);  
       info('Table added to VCS.');  
     }  
   }  
     
   void disableIndex(IdentifierName _tableName, IdentifierName _indexNameSQL)  
   {  
     DictIndex  dictIndex;  
     DictTable  dictTable = new DictTable(tableName2id(_tableName));  
     int counter;  
       
     if(dictTable)  
     {  
       counter = dictTable.indexNext(counter);  
       while (counter)  
       {  
         dictIndex = dictTable.indexObject(counter);  
   
         if(dictIndex.enabled() && dictIndex.name(DbBackend::Sql) == _indexNameSQL)  
         {  
           dictIndex.modify(false, dictIndex.allowDuplicates(), false);  
           info(strFmt('Disabled index : %1 - %2', dictIndex.name(), _indexNameSQL));  
         }  
   
         counter = dictTable.indexNext(counter);  
       }  
     }  
   }  
   
   // project initialisation  
   projectNode = createProject(projectName, projectType);  
   projectNode.lockUpdate();  
   
   // file operations  
   permission = new FileIOPermission(fileName, #io_read);  
   permission.assert();  
   file = new TextIO(fileName, #io_read);  
   file.inFieldDelimiter(",");  
   
   //Read file  
   While (file.status() == IO_Status::Ok)  
   {  
     data = file.read();  
   
     if (conlen(data))  
     {  
       inVS = false;  
   
       tableName   = conPeek(data, 1);  
       indexToDisable = conPeek(data, 2);  
   
       // check and add table to project  
       currUtilElement = xUtilElements::find(UtilElementType::Table, tableName);  
       path = xUtilElements::getNodePath(currUtilElement);  
   
       if(!set.in(path))  
       {  
         addTableToProject(projectNode, tableName);  
         set.add(path);  
   
         if(vcsEnabled)  
         {  
           inVS = checkoutIfInVS(vcsSys, currUtilElement);  
         }  
       }  
         
       // Make the change  
       disableIndex(tableName, indexToDisable);  
   
       // if not in VS Add to Version Control  
       if (vcsEnabled && !inVS && !setAddedToVS.in(tableName))  
       {  
         addToVS(vcsSys, currUtilElement);  
       }  
       setAddedToVS.add(tableName);  
     }  
   }  
   
   // Save the project  
   projectNode.unlockUpdate();  
   projectNode.AOTsave();  
   projectNode.AOTrestore(true);  
     
   // Close file  
   file = Null;  
   CodeAccessPermission::revertAssert();  
   
   info('Finished process.');  
 }  

This posting is provided "AS IS" with no warranties. Use code at your own risk.