[Deep Dive] Mitigating performance-hungry SQL queries with WebReports - Part 2

In my previous blog post, I highlighted some of the problems that WebReports customers may experience, and I shared some of the features of WebReports that are designed to reduce performance issues. You can download the free PDF if you haven’t already.

This blog is going to complete the topic of performance issues. 

Some background Mitigating performance-hungry SQL queries with WebReports

I’ve heard it suggested that using LiveReports to manage any DB queries (as WebReports does) is inefficient and may add to performance issues.  It is true that if developers leave LiveReports with their default settings on, the SQL queries may not operate as efficiently as raw query executions. However, correctly configured LiveReports add very little extra processing to any SQL query. If a developer starts to use some of the more specialized LiveReports features (such as SQL templates), then that could certainly add time to each query. However, that would be a design decision just as a developer might decide to write similar code in OScript on top of a DB query.  Sometimes, extra server-side code can make a heavyweight and complex SQL query more performant. 

The bottom line is that any form of customization technology that requires data from Content Server will need to execute some kind of API call or a direct DB call. In OScript development these calls may not necessarily be separated architecturally (developers choice) but the WebReports module uses LiveReports as a clean way of separating the Database calls and the SQL used therein, from the application code. In a WebReport based application, all of the SQL queries (or the search queries for that matter) are separate objects that can be searched for or reported on without having to dig into the application itself.  Each WebReport object that uses a DB source communicates via a distinct API to access these SQL queries. Customers can also grant permission to view or edit WebReports reportviews without opening up access to the LiveReports on production systems if necessary. 

Of course, there are a variety of different ways to mitigate performance-hungry SQL queries, some by making good use of WebReports or client software capability.. Ultimately much of this comes down to application design as it would with any other technology. Here are some common considerations:

• Optimization of tables and creating indices (sometimes this can be difficult, or not allowed)
• Well-structured SQL queries – also using any built-in Content Server approaches.
• Using any built-in caching mechanisms (e.g. via WebReports functions) in the most optimum way (note that sometimes a badly used cache can worsen performance)
• Design considerations to determine whether certain features such as parsing, sorting, filtering, etc. are best done on the DB, Server, or Client (there are valid cases for each). 
• Design considerations to determine whether all data is pulled in one query, progressively in response to manual actions, or via “lazy loading”. 
• For non-real time requirements possible off-peak scheduled runs. 
• Use of secondary, flattened tables.

Application Architecture

This is a big topic but one part of this that has an important impact is the set of decisions around how the application is partitioned between the DataBase, Content Server (WebReports functionality), and the client. Here are some of the concerns we consider:

• What built-in Content Server functionality can we leverage? 
• What other customer tools or functionality can we leverage? (Also, are there any other implications to using these technologies?)
• Is the client capable of running a script (for example browser clients can run JavaScript as opposed to things like emails or static documents) 
• Do we require any client features that force us to perform functions like sorting and filtering on the client?
• Do we require any WebReports features that require us to perform functions like sorting and filtering in the WebReport? 
• How can we use the DB to do what it does best, (assuming the DB isn’t the weak performance link)
• What other systems do we have to interact with and what features do we require to make the integration work? 

WebReports Component Design

Given that WebReports has a very rich feature set, it should be no surprise that there are many ways to achieve any task. Ideally, an initial architectural design will help to pick the best set of features for a given application; however, sometimes it is necessary to adjust the approach based on testing under load. 

Either way, we at Ravenblack can help with pre-design or adaptive design considering things like application logic or code optimization. Contact us today to take advantage of any and all features that could help you with performance and any other areas of concern.