We are working on a script to query the Evergreen database for
circulation statistics for a given set of copy barcodes. We use Syrup
for course reserves which is a separate database that has the copy
barcodes for each item on reserve.
We were finding the queries very slow, up to 45 seconds to count circs
for about 20 items. Then we did some experimenting. It turns out that
querying the copy table by barcode takes a long time, like 2620 ms to
query 11 barcodes. Querying the same 11 copies by copy.id took 3.6 ms.
If we add 'deleted = false' to the barcode query, we get 3.6 ms.
In our database of 3,161,189 copies, cutting out the deleted copies
eliminates only 904 copies. Why does testing for deleted improve
searching so significantly?
Here are the specific queries:
SELECT id from asset.copy
WHERE barcode in (
'36052001712495',
'36052001643823',
'36052001718112',
'36052001743151',
'36052001731610',
'36052001729903',
'36052001738771',
'36052001738813',
'36052001698058',
'36052001675759',
'36052001698009'
)
2620.654 ms
SELECT barcode from asset.copy
WHERE id in (
1697551,
2432041,
2891221,
2919157,
2981459,
2996116,
3021567,
3044444,
3047655,
3076340,
3088677
)
3.6 ms
SELECT id from asset.copy
WHERE barcode in (
'36052001712495',
'36052001643823',
'36052001718112',
'36052001743151',
'36052001731610',
'36052001729903',
'36052001738771',
'36052001738813',
'36052001698058',
'36052001675759',
'36052001698009'
)
and deleted = false
3.6 ms
--
Martha Driscoll
Systems Manager
North of Boston Library Exchange
Danvers, Massachusetts
www.noblenet.org
