Certain Canned Reports run slow or time out due to inefficient scoping in SQL queries

Products

• IT Management Suite

Issue/Introduction

The following canned reports are known to time out or run very slowly when sifting large database.

• All Cost Types
• All Purchase Requests
• All Purchase Requests WP
• Assets by Owner's Department
• Count of Computers by Operating System
• Count of Logical Disk by File System Type
• Count of Software Packages Installed
• Incomplete Procurement Items
• Primary User by Computer
• Primary User on Multiple Computers
• Requestors - parameter
• Suppliers - parameter
• Total User Accounts by Computer
• User Profiles Across Multiple Machines

Environment

ITMS 8.x

Cause

The security scoping process in the SQL behind the reports is not optimized. They use inline select statements instead of a more efficient JOIN.

Resolution

On recent versions these reports should be optimized with current tables and indexes. In case you still percive slowness on any of the mentioned reports above:

Extract the zip file attached to this article to a local folder

1. Start the SMP Console and go to “Reports > All Reports”
2. Right click on a folder of your choice (or the root folder) and select “Import”
3. Browse to the saved file and import it.  A folder named “Revised Reports” will be created and inside of it will be the reports I revised.
4. Run the SQL in the file “MoveReportsToCorrectFolders.sql” against your database.

When your refresh the console the “Revised Reports” folder should be empty.  Go ahead and delete it.