Evergreen

unapi.bre and slow views

Bug #1374091 reported by Ben Shum
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Evergreen
Fix Released
Undecided
Unassigned
2.6
Fix Released
Undecided
Unassigned
Evergreen 2.6.4
2.7
Fix Released
Undecided
Unassigned
Evergreen 2.7.1

Bug Description

Evergreen 2.6 / 2.7 / master

We noticed extremely poor performance with searching and record retrieval in the catalog. Looking deeper, Mike Rylander and I found that there was slowness especially within the unapi.bre (actually, unapi.mra) function and some views within that. A single call was taking something like 1600ms and we were expecting it to be much faster.

Mike pinpointed issues with this view metabib.record_attr_flat and proposed changing it to point directly at the tables.

Branch forthcoming.

Revision history for this message
Mike Rylander (mrylander) wrote :

And here is that fix: http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/miker/record_attr_flat_flat

tags: added: pullrequest
Revision history for this message
Ben Shum (bshum) wrote :

Well this does speed up search, but we just found out that it seems to only pass up the uncontrolled values for us now, not the coded value ones.

The coalesce part of the select is only grabbing the first value for our system.

Sample outputs pre-change and post-change with the branch for...

SELECT * FROM metabib.record_attr_flat WHERE id = 1348457 ORDER BY attr;

1348457|audience|j
1348457|bib_level|m
1348457|cat_form|a
1348457|conf|0
1348457|ctry|nyu
1348457|date1|1987
1348457|date2|9999
1348457|fest|0
1348457|icon_format|book
1348457|ills|a
1348457|indx|0
1348457|item_lang|eng
1348457|item_type|a
1348457|language|eng
1348457|lit_form|0
1348457|lit_form|0
1348457|mr_hold_format|book
1348457|oclc|ocm15017231
1348457|pub_status|m
1348457|search_format|book

vs.

1348457|cat_form|a
1348457|conf|0
1348457|ctry|nyu
1348457|date1|1987
1348457|date2|9999
1348457|fest|0
1348457|ills|a
1348457|indx|0
1348457|language|eng
1348457|oclc|ocm15017231
1348457|pub_status|m

I'm not sure yet, but this seems to affect things like acquisitions loading and matching. Further testing pending.

Revision history for this message
Mike Rylander (mrylander) wrote :

I've force-pushed a union variant that returns the expected results, and uses a fast plan. Also included the upgrade script. Thanks for the further testing, Ben.

Revision history for this message
Mike Rylander (mrylander) wrote :

I've updated the branch with an improvement to the metarecord variant of the mra unAPI function. This speeds up metarecord retrieval via unAPI.

Ben Shum (bshum)
no longer affects: evergreen/master
Revision history for this message
Ben Shum (bshum) wrote :

Happier database thus far. Picked to master and backported to rel_2_7 and rel_2_6. Thanks Mike!

Changed in evergreen:
status: New → Fix Committed
Evergreen Bug Maintenance (bugmaster)
Changed in evergreen:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.