Feature #9966

Add additional database indexes to improve hierarchical query speed

Added by David Juhasz about 4 years ago. Updated over 3 years ago.

Status:VerifiedStart date:06/07/2016
Priority:MediumDue date:
Assignee:Dan Gillean% Done:

0%

Category:Performance / scalabilityEstimated time:8.00 hours
Target version:Release 2.4.0
Google Code Legacy ID: Tested version:2.3
Sponsored:No Requires documentation:

Description

Initial test show a signficant performance increase when testing treeview performance for a large fonds (122,191 descriptions).

Default AtoM database indexes:

- SQL query: ~ 70 s
- Data formatting: ~ 3 s
- JS request/load: ~ 5 s

The developer added the following index on the 'lft' column in the information_object table:

CREATE INDEX lft ON information_object(lft) USING BTREE;

Which dramatically improved query times (test repeated four times):

SQL query:     1.64  1.59  1.65  1.50 s
Request time:  4.85  4.87  4.92  5.09 s
JS work:       8.28  8.25  8.35  8.52 s

History

#1 Updated by David Juhasz about 4 years ago

The "term" table should benefit from a similar index on the 'lft' column

#2 Updated by David Juhasz about 4 years ago

  • Estimated time set to 8.00

#3 Updated by José Raddaoui Marín almost 4 years ago

  • Status changed from New to Code Review
  • Assignee set to Nick Wilkinson
  • Target version set to Release 2.4.0

#4 Updated by José Raddaoui Marín almost 4 years ago

  • Project changed from AtoM Wishlist to Access to Memory (AtoM)

#5 Updated by Nick Wilkinson almost 4 years ago

  • Assignee changed from Nick Wilkinson to Mike Cantelon

#6 Updated by Nick Wilkinson almost 4 years ago

  • Assignee changed from Mike Cantelon to Jesús García Crespo

#7 Updated by José Raddaoui Marín almost 4 years ago

  • Status changed from Code Review to QA/Review
  • Assignee changed from Jesús García Crespo to Dan Gillean

Merged in qa/2.4.x

#8 Updated by Dan Gillean over 3 years ago

  • Category set to Performance / scalability
  • Status changed from QA/Review to Verified

Also available in: Atom PDF