Bug 16076 - DBIx searches - performance issues
Summary: DBIx searches - performance issues
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: master
Hardware: All All
: P5 - low normal (vote)
Assignee: Galen Charlton
QA Contact: Testopia
Depends on:
Blocks: 15342
  Show dependency treegraph
Reported: 2016-03-15 13:20 UTC by Jacek Ablewicz
Modified: 2016-07-08 10:33 UTC (History)
0 users

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

Bug 16076 - DBIx searches - performance issues (1.03 KB, patch)
2016-07-08 09:51 UTC, Jacek Ablewicz
Details | Diff | Splinter Review
Bug 16076 - DBIx searches - performance issues (2.32 KB, patch)
2016-07-08 09:51 UTC, Jacek Ablewicz
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Jacek Ablewicz 2016-03-15 13:20:42 UTC
While testing Bug 11998 and Bug 15341, I did some quick & dirty comparisons on DBI vs DBIx search performace - how long does it take to fetch from the database: one small system preference (marcflavour) and all system preferences (545 rows). Results:

1) one syspref, DBI: 130 microseconds
   ( using 'SELECT value FROM systempreferences WHERE variable = ? LIMIT 1')
2) one syspref, DBIx: 1.25 miliseconds
   ( Koha::Config::SysPrefs->find($var)->value() )
3) all preferences, DBI: 1.8 miliseconds
   ( 'SELECT variable, value FROM systempreferences' )
4) all preferences, DBIx: 21 miliseconds
   ( Koha::Config::SysPrefs->search()->unblessed(), like in Bug 15341)

If I use

    my $all_prefs_rs = Koha::Config::SysPrefs->search(
        { columns => [qw/value variable/] }

in 4), search is a bit quicker (17.1 ms). Maybe it can be improved somehow further, but I don't know how - any ideas?

Those numbers may look not very significant at the first glanace, but remember that it's not uncommon for the Koha script to make 2000+ DBI calls (in such cases, they are usually very repetitive - small stuff fetched from uncomplicated tables, over and over again).
Comment 1 Jacek Ablewicz 2016-07-08 09:51:41 UTC
Created attachment 53214 [details] [review]
Bug 16076 - DBIx searches - performance issues

Experimental method Koha::Objects->search_all_unblessed()
added for testing an impact of DBIx::Class::ResultClass::HashRefInflator
on search performance.
Comment 2 Jacek Ablewicz 2016-07-08 09:51:45 UTC
Created attachment 53215 [details] [review]
Bug 16076 - DBIx searches - performance issues

Quick & dirty test script for benchmarking database search performance
using various DBI and DBIx approaches.
Comment 3 Jacek Ablewicz 2016-07-08 10:33:15 UTC
Benchmark results are in Bug 16079 comment #15