unapi.bre and slow views

Bug #1374091 reported by Ben Shum on 2014-09-25
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Evergreen
Undecided
Unassigned
2.6
Undecided
Unassigned
2.7
Undecided
Unassigned

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.

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.

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.

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) on 2014-10-03
no longer affects: evergreen/master
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
Changed in evergreen:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers