Query Performance Considerations

For performance reasons, you should avoid using too many large queries in one report. In addition, you should only run a query from script in cases where you cannot directly bind the query. In general, it is more efficient to use the 'Data Binding' dialog box to bind the query to an element, which allows the query to automatically run as part of report generation.

There are two complimentary methods for controlling and improving the performance of a report.

• Size limit: You can limit the number of rows that the query returns.
• Time limit: You can limit the amount of time a query may execute.

For example, if you only use the first few rows of a query, you should set a tight limit on the query size. You can make these settings at the query level or data binding level. See Advanced Toolbar Buttons in the Data Modeler to limit at query level, and Precautions and Safeguards in the Report Designer to limit at binding level.

 
 

Query Example

Assume that you need to create a table containing sales summary information: Total sales volume, product with highest volume, and customer with the highest volume. There are several possible ways that you can design this table:

• Write a stored procedure to return the three unrelated aggregates as the result of a single query, and bind the table to this query. However, designing such a stored procedure is awkward and requires advanced knowledge of database programming. Therefore, this approach is not recommended.
• Use a Data Worksheet to compute the three desired aggregates, and merge these into a single Data Block. Then bind the table to this Data Block. (See the Data Worksheet for details).
• Design three simple queries to return the three desired aggregates. Use script to independently run the three queries, and then explicitly assign the results to corresponding table cells.

The following example illustrates the third approach. Assume that you have defined the following three simple queries in the Data Modeler:

• 'total sales' – returns total sales volume
• 'top product' – returns product with highest volume
• 'top customer' – returns customer with the highest volume

Follow the steps below to create the table that displays these values:

1. Create a new table with three rows and two columns.
2. Select each of the three cells in the left column, and enter the following strings into the cells:
1. “Total Sales”
2. “Top selling product”
3. “Highest Volume Customer”
3. Right-click the table, and select 'Script' from the context menu. This opens the Script Editor for the table.
4. Enter the following script. This runs each query in turn, and assigns the result to the corresponding table cell.

var sales = runQuery('total sales');

var product = runQuery('top product');

var customer = runQuery('top customer');

table[0][1] = formatNumber(sales[1][0], "$#,###.00");

table[1][1] = product[1][0];

table[2][1] = customer[1][0];

More Articles About Data Pipelines

Baseball Data Visualization - Professional baseball teams utilize data visualization software to analyze and present vast amounts of data in a visually engaging and intuitive manner. Here are several ways in which they leverage data visualization software: Performance Analysis: Teams collect and analyze various performance metrics of players, such as batting average, on-base percentage, slugging percentage, fielding efficiency, pitch velocity, and more. Data visualization software helps them create interactive charts, graphs, and heat maps to identify patterns, trends, and outliers, allowing teams to evaluate individual and team performances effectively. Scouting and Recruitment: Data visualization tools assist in visualizing scouting reports and player statistics. Teams can compare and contrast potential recruits based on their performance data, making informed decisions about player acquisitions and trades. Heat maps and scatter plots can showcase...

Cost Savings of a Small Footprint Dashboard Application - InetSoft's unique selling proposition lies in its small footprint, cloud first design philosophy. Compared to traditional dashboard solutions, InetSoft's small footprint platform is distinct in that it: Requires significantly less computing power and resources Minimizes skill training and administration with an all-in-one web app for dashboard design and data preparation, for both professional designers and self-service business users Offers consistent high performance with an online mini warehouse that avoids IT heavy processes work safety dashboard example InetSoft can be deployed to a cloud instance as small as two virtual cores (vCore) for basic use. Indeed one cannot even purchase a physical machine this small since two vCores is the same as one physical core. In contrast, traditional enterprise software usually needs machines or instances with at least 4 to 8 physical cores...

Create a Shape Chart - To visualize product type as different shapes, start with an existing dot plot and drag the product dimension from the data source to the 'Color', 'Shape', 'Size', or 'Text' region. Summary This page has gone over a few of the techniques used to create a dynamic and powerful manufacturing dashboard. You saw how to create shape and radar charts, and how to use them to analyze the most important aspects of manufacturing. InetSoft makes it quick and easy to display data in a wide variety of different styles for exploration and interaction. InetSoft additionally provides many other features to facilitate the creation of professional dashboards, such as embedding, hyperlinks, drill-downs, positioning and sizing aids, device-adaptive layout, advanced scripting, and much more. InetSoft is a complete solution for modern...

Vertical-focused Cloud Based Embedded Reporting - Vertical-focused cloud-based applications are tailored to meet the specific needs of distinct industries or sectors. These applications provide industry-specific functionalities, ensuring that businesses can operate more efficiently and effectively within their particular domain. They often come with robust dashboard reporting features that offer deep insights into key performance indicators (KPIs) relevant to that industry. Here, we will explore several vertical-focused cloud-based applications, discussing the dashboard reporting features built into each and the KPIs they include. 1. Salesforce Health Cloud Industry: Healthcare Overview: Salesforce Health Cloud is a patient relationship management...

Previous: Execute a Query in a Report Script

Next: Query Compatibility