Home » Categories » CHARTrunner » CR Data Source

Query to fetch the Last N rows of data from a table or view

Revision Date: 2009-05-28

Problem:

I have tables and views in my database that contain a large number of rows of data. My CHARTrunner charts using this data take a long time to render. I only want to chart the most recent rows from the data source. I have tried setting "Result records to include" on the "Data definition" tab to "Last N = 25" so that I only chart the last 25 rows, but that doesn't seem to help. Why does it take so long for the chart to render, and what can I do to improve the speed of the charts?

Solution:

When CHARTrunner prepares to render a chart, it fetches all the data from the data source into memory, even if you have specified to chart only the last 25 rows of data (via "Result records to include = Last 25 rows" on the Data definition tab). If your data source contains lots of rows of data, such as the 5000 rows shown below in the LotsOData table, then CHARTrunner is essentially wasting time and memory by reading and processing those thousands of data rows when you want to only display the last 25 rows of data on the chart. When the database is on a remote server all this data must travel across your network. The key point to bear in mind is to query your data in such a way that results in only the relevant data for the chart being fetched from the data source.

Often a filter is used to limit the amount of data that is fetched from the data source. For instance, using the LotsOData table you could specify to only include rows where the DateTime column is greater than a specified "starting date." This is an acceptable method for limiting the number of rows of data, but as new data arrives over time you will fetch more data each time the chart is rendered unless you edit the chart definition and adjust the "starting date" you are filtering on.

An alternative to using a filter is to use a special type of SELECT statement that only fetches the LAST N rows of data from the data source.

Consider the following example data table named LotsOData:

This data table contains 5000 rows and more rows could be added each day. We want to define a custom query so that each time we request the chart, it will include only the most recent 25 rows of data.

Microsoft Data Sources

For a Microsoft data source like SQL Server, Access and Excel, the following SQL query entered as a "Custom query" on the CHARTrunner Data definition tab will work:

SELECT * FROM
   (SELECT TOP 25 * FROM LotsOData ORDER BY DateTime DESC) B
ORDER BY DateTime

This is an example of a nested query which contains a subquery (the SELECT statement in parenthesis). The outer query orders the rows in ascending order by the DateTime column - which is how we want the data to appear on the chart. However, the inner subquery orders the rows in descending order by the DateTime column and it requests that only the "TOP 25" rows be returned by the database engine. The top 25 rows in descending time order are the same as the last 25 rows in ascending time order - except that they are in reverse order. In this case, the inner subquery ensures that we get only the rows we are interested in charting (i.e. the last 25) and the outer query re-orders those rows to ensure they are in the correct ascending order for the chart. The "B" after the closing parenthesis gives the subquery an alias name of B - it is necessary for proper syntax even though it looks out-of-place.

To make this LAST N query work for your specific data, copy the SELECT statement shown above above into your chart definition as a custom query and make these changes:

1) Change LotsOData to the name of your table or view.

2) Change DateTime to the name of the column you want to order by. It does not have to be a date column but it should be column that establishes the order you desire for your data.

3) Change the 25 to the "last N number of rows" of data you want to fetch from the data source. If you typically display the last 25 rows of data on the chart then you still might want to fetch more rows of data (perhaps the last 100 rows of data) so that you have the ability to edit the chart definition and adjust the "Result records to include" to any value up to 100 rows. What's a good number of rows to fetch? Fetching 100 rows of data will typically not be a problem, but fetching 5000 rows will definitely result in reduced performance.

This technique has been tested with data in SQL Server, Access, and Excel.

Oracle Database

Oracle doesn't support the "TOP N" syntax that is used by the Microsoft database engines. But it is possible to achieve the same goal using ROWNUM in the WHERE clause.

The following SQL query entered as a "Custom query" on the CHARTrunner Data definition tab will work for Oracle:

SELECT * FROM
   (SELECT * FROM Scott.LotsOData ORDER BY DateTime DESC)
WHERE ROWNUM <= 25 ORDER BY DateTime

This is an example of a nested query which contains a subquery (the SELECT statement in parenthesis). The outer query orders the rows in ascending order by the DateTime column - which is how we want the data to appear on the chart - and it only uses rows 1 through 25 from the inner subquery. The inner subquery orders the rows in descending order by the DateTime column, which results in the last row we want having a ROWNUM of 1, and so on. The first 25 rows in descending time order are the same as the last 25 rows in ascending time order - except that they are in reverse order. In this case, the inner subquery returns a result set that is ordered backwards, i.e. the last row we want is ROWNUM = 1, and so on. The outer query re-orders the rows from the inner subquery to ensure they are in the correct ascending order for the chart, plus it only fetches ROWNUM 1 through 25.

To make this LAST N query work for your specific data, copy the SELECT statement shown above above into your chart definition as a custom query and make these changes:

1) Change Scott.LotsOData to the name of your table or view.

2) Change DateTime to the name of the column you want to order by. It does not have to be a date column but it should be column that establishes the order you desire for your data.

3) Change the 25 to the "last N number of rows" of data you want to fetch from the data source. If you typically display the last 25 rows of data on the chart then you still might want to fetch more rows of data (perhaps the last 100 rows of data) so that you have the ability to edit the chart definition and adjust the "Result records to include" to any value up to 100 rows. What's a good number of rows to fetch? Fetching 100 rows of data will typically not be a problem, but fetching 5000 rows will definitely result in reduced performance.

This approach was tested with Oracle 10g and should work with other versions of Oracle.

Sample Access and Excel Chart and Data

In the attached ExamplesForChartingLastN.zip file, you will find example CHARTrunner chart definitions and data which demonstrate this query using both an Access database and an Excel spreadsheet. Unzip this into a temporary folder then make this the working folder in CHARTrunner to view the example charts and see how they are defined.

Attachments (1) Attachments
Related Articles RSS Feed
CR: Charting from a Paradox password protected database
Viewed 1826 times since Fri, Nov 9, 2007
CR: Using Oracle Stored Procedures
Viewed 5232 times since Fri, Nov 9, 2007
CR: Chart refresh is not refreshing when data is added
Viewed 2874 times since Mon, Mar 3, 2008
CR: Excel Data Source Requirements
Viewed 2473 times since Fri, Nov 9, 2007
CR: Charting from a FoxPro Data Source
Viewed 2130 times since Fri, Nov 9, 2007
CR: Installable ISAM Error
Viewed 2249 times since Thu, Feb 23, 2012
CR: Fetching Specification Limits from the Data Source
Viewed 1747 times since Fri, May 30, 2008
CR: Using a Password Protected Microsoft Access Database
Viewed 2571 times since Fri, Nov 9, 2007
CR: Charting from a Paradox Data Source
Viewed 1977 times since Fri, Nov 9, 2007
CR: Lotus Data Source
Viewed 1788 times since Fri, Nov 9, 2007