Shared Queries - Making Your arcplan / Longview Analytics Application Faster
One of the most frequent requests that we receive just before an application is promoted into production is how can we make our arcplan application faster. There are several techniques to optimize the user application speed of your arcplan application. Some of these items include simplifying and optimizing arcplan documents, as well as a hardware and network configuration. This blog post focuses on improving application performance through the use of the arcplan Shared Query Cache.
To some degree an arcplan application is only as fast the data source it is accessing. Arcplan is capable of accessing a myriad of database sources, and often the most appropriate optimization techniques occur at the data level. However, the Shared Query Cache is a technique that can be implemented to prepopulate the initial (defaulted) query results of an arcplan database object. Combined with arcplan's Internal Database capabilities, query automation can be utilized to automate the initial load performance of a system or create an integrated in memory Micro Cube environment. (Figure 1)
The following is a step by step guide on configuring Shared Query with an arcplan Enterprise environment.
Step 1: Application Publication and Setting Object Properties (if needed)
Shared Queries must be activated via the arcplan Administration Console when the application is published. There are two options available when activating Shared Queries: "active", and "active for selected objects".
Shared Queries that are "active":
In order to allow the arcplan Server to automatically run all Shared Queries within the given parameters, select the "active" option. Set the parameters for at least one of the options (time limit or data sets returned) under the section titled "Cache if all selected conditions are met". It is not necessary in this case to configure the affected objects in the application itself. This automatic configuration, while easy to implement, is a very blunt approach to enhance performance, and by default may not result in the expected application performance gain.
Shared Queries that are "active for selected objects":
An arcplan application may be tuned even more finely by selecting the "active for selected objects" option when the application is published. The parameters for time limit and/or datasets returned must still be set as described above. In addition, when this option is selected, the "Shared query" option on the "Database" tab of the "Database Properties…" dialogue box must be selected on the application objects on which running the shared query is desired. In this way only those Shared Queries which have the option selected on their objects and are within the given time and data parameters will run, giving application designers greater control over their applications. By utilizing application tracing an application designer can identify database objects that can potentially slow the system and therefore are good candidates for participation in the Shared Query.
Step 2: Configure the Shared Query Cache
In the left hand pane of the arcplan Administration Console, click on "Load Balancing". Then right-click on the arcplan Application Server in the right hand pane and select "Server settings…". On the "General" tab under "Shared Query Cache", set the "BufferSize", "UpdateTime", and "IgnoreTime".
From the arcplan Administrative Console Help document:
The BufferSize (SharedQueryBufferSize) is a threshold value. As soon as the cache has reached or exceeded this threshold value and a new query is to be stored in the cache, an old query and its results will be deleted. That means the actual size of the cache can change from query to query. If the BufferSize is not set to a different value, the default size of 50 MBytes is assumed. The minimum BufferSize is 1 MB and the maximum is 1000 MBytes.
The UpdateTime (SharedQueryUpdateTime) defines the time after which the results of a cached query are marked as "to be updated". When the user quits the application by closing the browser the marked requests will be executed again. The cache will be updated before the connection is closed. There is no strict rule for executing the update exactly after the specified UpdateTime has elapsed. The decisive point is the frequency of user logins and logouts. If the UpdateTime is not set to a different value, the default of 60 minutes is assumed. The minimum UpdateTime is 30 minutes and the maximum is 2800 minutes.
The IgnoreTime (SharedQueryIgnoreTime) defines the time after which cached queries and their results are discarded. If the IgnoreTime is not set to a different time, the default of 15 hours is assumed. The minimum IgnoreTime is 1 hour and the maximum is 168 hours.
Step 3: Update the Shared Query With an arcplan Server Task
Shared Queries can be updated in a nightly cycle with an arcplan Server Task in order to ensure that the data in the cache is fresh each morning. In order to accomplish this, the application developer must create a document within the application in question that contains an executable button that runs the REFRESHSHAREDQUERYCACHE() function. An arcplan Server Task is then created to execute this button. For more information on creating an arcplan Server Task, please consult the Help document for the arcplan Administration Console.