Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0022633Openbravo ERPA. Platformpublic2012-12-19 12:162013-10-03 19:12
Reporterjvazquez 
Assigned ToAugustoMauch 
PrioritynormalSeveritymajorReproducibilityalways
StatusnewResolutionopen 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0022633: Results pagination performance in generated windows decreases with an increasing number of records
DescriptionOpenbravo generated windows use a query that is not very efficient to paginate results when scrolling. The performance of this query decreases exponentially when a window has ten of thousands or hundreds of thousands of records.
Steps To ReproduceSet 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.
Proposed SolutionChange the way for paginating results in generated windows. See http://stackoverflow.com/questions/6618366/improving-offset-performance-in-postgresql/6619087#6619087 [^]
Additional Information
TagsPerformance
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2012-12-19 12:16jvazquezNew Issue
2012-12-19 12:16jvazquezAssigned To => AugustoMauch
2012-12-19 12:16jvazquezModules => Core
2012-12-19 12:16jvazquezTriggers an Emergency Pack => No
2013-05-22 06:31eintelauIssue Monitored: eintelau
2013-05-22 09:02jvazquezTag Attached: Performance
2013-05-22 09:05jvazquezSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=4740#r4740
2013-10-03 19:12iciordiaProposed Solution updated

There are no notes attached to this issue.