[OPEN-ILS-GENERAL] SQL Query Performance

[Martha Driscoll]

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