0022633: Results pagination performance in generated windows decreases with an increasing number of records

Anonymous | Login

Project:

News | My View | View Issues | Roadmap | Summary

View Revisions: Issue #22633		[ All Revisions ] [ Back to Issue ]
Summary	0022633: Results pagination performance in generated windows decreases with an increasing number of records

Revision	2013-05-22 09:05 by jvazquez
Steps To Reproduce	Set up an Openbravo instance with a table that has hundreds of thousands of records, i.e. the product table. Activate the logging of the HQL queries being executed as indicated in http://wiki.openbravo.com/wiki/ERP_2.50:Developers_Guide/Common_Issues,_Tips_and_Tricks#How_can_I_see_what_SQL_is_executed. [^] Log in into the ERP, open the product window, and look for the query executed to paginate and show the results in the window, that should be something like "e315491f 558895 [http-8084-1] DEBUG org.hibernate.SQL - select * from ( select row_.*, rownum rownum_ from ( select product0_.M_Product_ID... product0_.M_Product_ID ) row_ where rownum <= ?) where rownum_ > ?" Now execute this query in your database entering high numbers (i.e. 100000, 200000) and low numbers (i.e. 1, 10) and compare the execution time for both queries. Time will increase dramatically when numbers increase. It seems to me that the query has to iterate through the results up to the number 100000 record, then iterate to the number 200000 record and return the range.

Revision	2013-05-22 09:02 by jvazquez
Steps To Reproduce	Set up an Openbravo instance with a table that has hundreds of thousands of records, i.e. the product table. Activate the logging of the HQL queries being executed as indicated in http://wiki.openbravo.com/wiki/ERP_2.50:Developers_Guide/Common_Issues,_Tips_and_Tricks#How_can_I_see_what_SQL_is_executed. [^] Log in into the ERP, open the product window, and look for the query executed to paginate and show the results in the window, that should be something like "e315491f 558895 [http-8084-1] DEBUG org.hibernate.SQL - select * from ( select row_.*, rownum rownum_ from ( select product0_.M_Product_ID... product0_.M_Product_ID ) row_ where rownum <= ?) where rownum_ > ?" Now execute this execute this query in your database entering high numbers (i.e. 100000, 200000) and low numbers (i.e. 1, 10) and compare the execution time for both queries. Time will increase dramatically.