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

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

the complete SQL statement is as follows:

   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, and the number of rows per page in the example.

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 be displayed per page when the row engine is set, 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 DESCLI MIT30) as E2 ORDER by PO 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 DESCLI MIT30) as E2 ORDER by PO ID ASC

when you preview to the last page, the rest of the data may be less than a row, so what does 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, 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 Desclim IT10) as E2 ORDER by PO ID ASC

3.3 Report Body Design

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

3.4-ROW Engine Setup

The same previous section of the Setup method, here no longer repeat.

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  

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

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