Bug 33989 - Inventory tool performs unnecessary authorized value lookups
Summary: Inventory tool performs unnecessary authorized value lookups
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Tools (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: David Cook
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2023-06-13 04:01 UTC by David Cook
Modified: 2023-12-28 20:47 UTC (History)
2 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
23.11.00,23.05.02,22.11.08


Attachments
Bug 33989: Only look up authorized value descriptions for items in results (3.51 KB, patch)
2023-06-13 04:56 UTC, David Cook
Details | Diff | Splinter Review
Bug 33989: Only look up authorized value descriptions for items in results (3.55 KB, patch)
2023-06-14 15:23 UTC, Sam Lau
Details | Diff | Splinter Review
Bug 33989: Only look up authorized value descriptions for items in results (3.62 KB, patch)
2023-06-26 12:04 UTC, Martin Renvoize
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description David Cook 2023-06-13 04:01:16 UTC
The inventory tool will look up the authorized value description for location, notforloan, itemlost, damaged, and withdrawn for every single barcode - even if it doesn't need to show this data to a user. 

By only performing these lookups when a user will view this data (ie for items that appear in the "results"), we gain enormous speed improvements.
Comment 1 David Cook 2023-06-13 04:02:15 UTC
I've already written a patch for this. Just going to have some lunch then going to write up a test plan and upload. 

Then have some more ideas for inventory tool improvements/workarounds...
Comment 2 David Cook 2023-06-13 04:56:19 UTC
Created attachment 152287 [details] [review]
Bug 33989: Only look up authorized value descriptions for items in results

This patch changes the inventory tool so that it only populates the
authorized value descriptions for items that show up in the results.
That is, items that will be viewed by a human.

This change saves a lot of time by not performing unnecessary
database lookups.

Test plan:

0. Apply patch and koha-plack --restart kohadev
1. Go to create a SQL report from SQL
/cgi-bin/koha/reports/guided_reports.pl?phase=Create%20report%20from%20SQL
2. Save a report with the following SQL:
SELECT barcode FROM items where barcode <> '';
3. Run the report
4. Download as CSV
5. Edit the CSV and remove the "barcode" heading

6. Go to edit item for barcode 3999900000001
/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=1&itemnumber=1
7. Add "Damaged status" of "Damaged"
8. Click "Save changes"

9. koha-mysql kohadev
10. update items set notforloan = 9 where barcode = 3999900000001;

11. Go to inventory tool
http://localhost:8081/cgi-bin/koha/tools/inventory.pl
12. Upload barcode file via "Choose file"
13. Uncheck "Compare barcodes list to results"
14. Open F12 tools
15. Click "Submit"
16. Click "OK" on confirm box

17. Note that the inventory job takes around 30 seconds to run
instead of 60 seconds
17b. The exact figures may vary. If you compare with before patch
inventory runs, you'll be saving roughly 18 seconds.
18. Note that "Damaged" appears in the "Damaged" column, which
demonstrates that the authorized value lookup was completed for
the item in the result list
Comment 3 Sam Lau 2023-06-14 15:23:03 UTC
Created attachment 152343 [details] [review]
Bug 33989: Only look up authorized value descriptions for items in results

This patch changes the inventory tool so that it only populates the
authorized value descriptions for items that show up in the results.
That is, items that will be viewed by a human.

This change saves a lot of time by not performing unnecessary
database lookups.

Test plan:

0. Apply patch and koha-plack --restart kohadev
1. Go to create a SQL report from SQL
/cgi-bin/koha/reports/guided_reports.pl?phase=Create%20report%20from%20SQL
2. Save a report with the following SQL:
SELECT barcode FROM items where barcode <> '';
3. Run the report
4. Download as CSV
5. Edit the CSV and remove the "barcode" heading

6. Go to edit item for barcode 3999900000001
/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=1&itemnumber=1
7. Add "Damaged status" of "Damaged"
8. Click "Save changes"

9. koha-mysql kohadev
10. update items set notforloan = 9 where barcode = 3999900000001;

11. Go to inventory tool
http://localhost:8081/cgi-bin/koha/tools/inventory.pl
12. Upload barcode file via "Choose file"
13. Uncheck "Compare barcodes list to results"
14. Open F12 tools
15. Click "Submit"
16. Click "OK" on confirm box

17. Note that the inventory job takes around 30 seconds to run
instead of 60 seconds
17b. The exact figures may vary. If you compare with before patch
inventory runs, you'll be saving roughly 18 seconds.
18. Note that "Damaged" appears in the "Damaged" column, which
demonstrates that the authorized value lookup was completed for
the item in the result list

Signed-off-by: Sam Lau <samalau@gmail.com>
Comment 4 David Cook 2023-06-19 01:44:34 UTC
Note that when bug 34051 is applied to fix a AV caching issue, bug 33989 only saves about .5 seconds for a barcode list of 961, but it's still useful, especially on larger data sets.
Comment 5 Martin Renvoize 2023-06-26 12:04:50 UTC
Created attachment 152680 [details] [review]
Bug 33989: Only look up authorized value descriptions for items in results

This patch changes the inventory tool so that it only populates the
authorized value descriptions for items that show up in the results.
That is, items that will be viewed by a human.

This change saves a lot of time by not performing unnecessary
database lookups.

Test plan:

0. Apply patch and koha-plack --restart kohadev
1. Go to create a SQL report from SQL
/cgi-bin/koha/reports/guided_reports.pl?phase=Create%20report%20from%20SQL
2. Save a report with the following SQL:
SELECT barcode FROM items where barcode <> '';
3. Run the report
4. Download as CSV
5. Edit the CSV and remove the "barcode" heading

6. Go to edit item for barcode 3999900000001
/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=1&itemnumber=1
7. Add "Damaged status" of "Damaged"
8. Click "Save changes"

9. koha-mysql kohadev
10. update items set notforloan = 9 where barcode = 3999900000001;

11. Go to inventory tool
http://localhost:8081/cgi-bin/koha/tools/inventory.pl
12. Upload barcode file via "Choose file"
13. Uncheck "Compare barcodes list to results"
14. Open F12 tools
15. Click "Submit"
16. Click "OK" on confirm box

17. Note that the inventory job takes around 30 seconds to run
instead of 60 seconds
17b. The exact figures may vary. If you compare with before patch
inventory runs, you'll be saving roughly 18 seconds.
18. Note that "Damaged" appears in the "Damaged" column, which
demonstrates that the authorized value lookup was completed for
the item in the result list

Signed-off-by: Sam Lau <samalau@gmail.com>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 6 Martin Renvoize 2023-06-26 12:05:46 UTC
No regressions found and the change makes logical sense.

Passing QA
Comment 7 Tomás Cohen Arazi 2023-06-26 14:41:26 UTC
Pushed to master for 23.11.

Nice work everyone, thanks!
Comment 8 Martin Renvoize 2023-07-17 14:41:26 UTC
Thanks for all the hard work!

Pushed to 23.05.x for the next release
Comment 9 Pedro Amorim 2023-07-18 11:54:55 UTC
Nice work everyone!

Pushed to 22.11.x for next release