[Evergreen-dev] Optimize SQL query

[Jason Stephenson]

Tiffany,

Do you need the information from metabib.rec_descriptor? If not, 
dropping the view from your query speeds things up for me.

I think you're getting duplicates because of the join on 
biblio.monograph_part. I suspect you're getting 1 output row for every 
part. You can eliminate the duplicates by adding 'distinct' after 'select'.

I made these changes in the attached file, and it runs reasonably 
quickly on my test database with production data.

HtH,
Jason
-------------- next part --------------
select distinct
acp.barcode,
bre.tcn_value,
acn.label as call_number,
aou.shortname as circ_lib,
acl.name as shelving_location,
acp.create_date,
acp.circulate,
acp.holdable,
acp.ref,
acp.opac_visible,
ccs.name as status
from asset.copy acp
join asset.call_number acn on acn.id=acp.call_number
join asset.copy_location acl on acl.id=acp.location
join biblio.record_entry bre on bre.id=acn.record
left join asset.copy_part_map acppm on acppm.target_copy=acp.id
join biblio.monograph_part bmp on bmp.record=bre.id
join actor.org_unit aou on aou.id=acp.circ_lib
join config.copy_status ccs on ccs.id=acp.status
where acp.deleted='f' and acppm.id is null and bmp.id>0 and bmp.deleted='f' and acp.holdable='t' and ccs.holdable='t' and acl.holdable='t';