[Evergreen-dev] Optimize SQL query

[Tiffany Little]

Thanks Jason, that really helps!

I don't necessarily need the mrd info, but I've pivoted completely in a
different direction now that you've solved one of my issues. Now I'm trying
to pull in the 245 and the 300 info, since that can help me isolate in my
list things that had a part erroneously applied. For example, being able to
see the 300$a can tell me that if the record says something like "245
pages" it might just be a regular monograph and not need parts, but someone
added them for whatever reason. And the 245$a and $n can tell me if it's a
manga record for just one volume, which we probably want to move those
items to a series record.

So all that to say, I'm now joining in metabib.wide_display_entry and it is
taking *forever*. Is that just pretty normal, or is there a better way to
get that info? I've attached my current query.

(I'm hoping this discussion is helpful for someone else too, since I know
thinking about parts can be an angsty endeavor.)

Thanks!
Tiffany

Tiffany Little, PINES Bibliographic Projects Manager

------------------------------

Georgia Public Library Service

2872 Woodcock Blvd, Suite 250 | Atlanta, GA 30341

(404) 235-7161 | tlittle at georgialibraries.org

Join our email list <http://georgialibraries.org/> for stories of Georgia
libraries making an impact in our communities.


On Thu, Aug 31, 2023 at 12:58 PM Jason Stephenson via Evergreen-dev <
evergreen-dev at list.evergreen-ils.org> wrote:

> 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
> _______________________________________________
> Evergreen-dev mailing list
> Evergreen-dev at list.evergreen-ils.org
> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-dev
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://list.evergreen-ils.org/pipermail/evergreen-dev/attachments/20230901/ac4eca90/attachment.htm>
-------------- next part --------------
select distinct
acp.barcode,
bre.tcn_value,
acn.label as call_number,
mwde.title,
mwde.physical_description,
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
join metabib.wide_display_entry mwde on mwde.source=bre.id
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';