Optimizing Smart Reporting performance

To configure data source connection restrictions on Smart Reporting

1. Log in to Smart Reporting as an administrator.
2. Select Administration > Admin Console.
3. Open the Data Sources panel.
4. Click the connection.
5. On the Connection page, open the Connection Pool panel.
   
6. To optimize data source connections, complete the following steps:

   • In the Min Connections and Max Connections fields, enter values to designate the number of connections to use from within a connection pool.
     Consider the following example: 
     When you set the maximum connections to 5, only 5 connections will be allowed. When a user accesses a report, one connection is used. However, if a user accesses a Dashboard that lists three reports, three connections are used. Set the value of Max Connections to restrict the number of connections that Smart Reporting can use. When the maximum number of connections is reached, other users cannot access the source until these reports have completed processing.

     Note:

     Increasing the value of Max Connections does not affect Smart Reporting performance unless many users access a large number of reports.

   • In the Refresh Time field, enter the time (in hours) after which database connections are refreshed.
     The refresh recycles the connections after the set period. Recycling ensures that no connections are stale or left open even though the user is no longer using them.
   • In the Timeout field, enter the time (in seconds) allowed for the SQL statement to return results.
     The value defined should be able to accommodate the queries run against the data source.

7. To configure restrictions forSmart Reporting query results, complete the following steps:

   1. Open the Usage Parameters panel.
      
   2. Turn on the Max Rows Returned toggle.
   3. In the Max Rows Returned field, enter the maximum numbers of query results you want returned.
8. Click Save.

To configure view-level restrictions for data to be displayed

Define view-level data restrictions for specific modules that are expected to return a large number of records. For example, if your change management reports are not expected to return many records, but your incident reports will return many records, you can define view-level settings for the Incident Management view.

1. In Smart Reporting, open a view in Edit mode.
2. Click View Optionsin the toolbar.
3. Open the Performance panel, modify the View Row Limit parameter.
   This parameter sets the limit for rows returned from the database.

To configure AR System for setting restrictions on Smart Reporting data

1. Log in to AR System.
2. Select Applications > AR System Administration> AR System Administration Console.
3. Select System > General > Server Information.
4. On the Configuration tab of the Server Information page, modify the value of the Max entries returned by GetList field.
   This field sets a row limit for any query sent using the JDBC driver.
   

To configure the data connection limit on Smart Reporting

Configure the Smart Reporting Connection settings to define restrictions on the data connection to the AR System server. The Custom Parameters for the Smart Reporting data connection settings allows you to control the data returned from the AR System server depending on expected volume of data being retrieved for Smart Reporting.

1. Log in to the Mid Tier as an administrator, and launch the Mid Tier.
2. Open the Smart Reporting console.
3. Select Administration > Admin console > Data sources.
4. Open the AR System data source.
5. On the Connection panel, update the row limit in the Custom Parameters field, such as mode=new;refreshCache=60;rowLimit=10000.
   
6. Click Test Connection. 
7. Click Save.

Additionally, you can also restrict the number of rows that can be retrieved from the AR System database by using the CCS parameter named Jdbc-Row-Limit. The default value of this parameter is 10,000. However, an administrator can change this value as required based on the data volume.

When retrieving records from the database, the lower value of either the Jdbc-Row-Limit parameter defined in AR System or rowLimit parameter defined in Smart Reporting is considered.  For example, if  Jdbc-Row-Limit has value 5000 and rowLimit has value 3000, the maximum number of rows that will be retrieved from the database is 3000. Similarly, if Jdbc-Row-Limit has value 4000 and rowLimit has value 5000, the maximum number of rows that will be retrieved from the database is 4000.

See the following table for more examples of how you can use the row_limit and Jdbc_Row_Limit parameters to control the maximum number of records retrieved from the database:

For more information about the Jdbc-Row-Limit parameter, see Centralized configuration in AR System online documentation.