Report performance optimization scenario single data set pagination SQL implementation Layer Report

1. Overview

We know that the row engine by page fetch only applies to oracle,mysql,hsql and sqlserver2008 and above database, other database, such as access,sqlserver2005,sqlite, etc. must write paging SQL.

The following is an example of an Access database that describes how a database that needs to write paged SQL uses a row-style engine to implement a layered report.

Solution Delivery Tools: report Development Tools Finereport

2, solve the idea

For a database such as MySQL, which can use a row-style engine to implement a layered report, if the row engine is checked, the program will automatically create the constituent page SQL, for example, I created a new dataset ds1, from the MySQL database, the basic SQL statement is:

SELECT * FROM Order Details

If you do not define paged SQL, the Shang engine option, when previewing a report, the program converts the above SQL statement to the following statement to fetch one page of data:

Select COUNT (*) as Totalrowcount from (SELECT * from order detail) T

If the database is a database of access that is not directly a component of page SQL, then you need to write paged SQL.

3. Operation procedure

Take the Frdemo built-in SQLite as an example to illustrate how SQLite writes paged queries.

Note: sqlserver2005 and SQLite operate the same steps.

3.1 New Data set

New Data Set Ds1:select * from Order details.

3.2 Add paged Query SQL statement

Click the Paging Query button in the Data query panel to edit the paging SQL statement, such as:

 SELECT *          FROM  (                  SELECT  *                        from (                        SELECT *                              FROM  Order Details  ORDER BY  order Id asc limit ${fr_pagesize*fr_ pagenumber}                          )  AS e1 ORDER BY  Order Id desc  limit ${                              if (Fr_pagenumber == int (((fr_rowcount-1)/fr_ pagesize) (+1)),fr_rowcount -  (fr_pagesize* (fr_pagenumber-1)), fr_pagesize)                                }           )  AS e2 ORDER BY  Order ID ASC

Note: When the above code is placed in the paging SQL panel, the subsequent comment statements are deleted, and the three order by must not be lost in the statement.

· Paging SQL statement Comments

${if (Fr_pagenumber==int ((((fr_rowcount-1)/fr_pagesize +1)), Fr_rowcount-(fr_pagesize* (fr_pagenumber-1)), Fr_ pagesize)} means:

In the case of the last page, take the remaining rows of the last page, if not the last page, the number of rows to display per page, the number of rows per page in the example is 30 rows.

Fr_pagenumber: The number of pages currently browsed, if the 2nd page preview, then fr_pagenumber=2;

Fr_rowcount: The total number of data bars for the current data set;

Fr_pagesize: Represents the number of rows to display per page when setting up the row engine, which is fr_pagesize=30 in the example.

When previewing, a paged query is set based on the values of 3 variables, which generate a database query, such as FR_PAGENUMBER=2,FR_PAGESIZE=30, when the report is previewed on the web side, and when previewed to page 2nd, the SQL statement above translates to:

SELECT * FROM (SELECT * from Order Details ORDER by order ID ASC limit) as E1 ORDER by order ID DESClimit30) as E2 Orde R by order ID ASC

When previewing to page 3rd, the Fr_pagenumber=3,sql statement translates to:

SELECT * FROM (SELECT * from-Order Details order BY order-ID ASC limit) as E1 ORDER by order ID DESClimit30) as E2 Order By order ID ASC

When you preview to the last page, the remaining data may be less than 30 rows, so what will the SQL statement turn into?

If fr_rowcount=100,fr_pagesize=30, that is, the total number of rows of data is 100 lines, each page shows 30 rows, preview to the last page, that is, 4th page, the Fr_pagenumber=4,sql statement will be converted to:

SELECT * FROM (SELECT * from-Order Details order BY order-ID ASC limit) as E1 ORDER by order ID DESClimit10) as E2 Order By order ID ASC

3.3 Report Body Design

Drag a column of data from the dataset to the cell.

3.4-ROW Engine Setup

The Setup method in the previous section is not mentioned here.

3.5 Effect View

Click Page Break preview, as follows:

For the completed template, please refer to:%FR_HOME%\WEBREPORT\WEB-INF\REPORTLETS\DOC\ADVANCED\PAGINGSQL.CPT

3.6 Paging SQL statements for databases

   SELECT *                 FROM  (                   SELECT TOP ${                                   if (Fr_pagenumber == int ((((fr_rowcount-1)/fr_pagesize) +1), Fr_ rowcount -  (fr_pagesize* (fr_pagenumber-1)), fr_pagesize)                                   } *                        from (                              SELECT TOP ${fr_pagesize*fr_pagenumber} *                              FROM  Order Details  ORDER BY  order id asc                              )  AS e1 ORDER BY  Order id desc                 )  AS e2 ORDER BY  Order ID  asc

Report performance optimization scenario single data set pagination SQL implementation Layer Report