Bug 16076

Summary: DBIx searches - performance issues
Product: Koha Reporter: Jacek Ablewicz <abl>
Component: Architecture, internals, and plumbingAssignee: Galen Charlton <gmcharlt>
Status: NEW --- QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low    
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16079
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=15341
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Bug Depends on:    
Bug Blocks: 15342    
Attachments: Bug 16076 - DBIx searches - performance issues
Bug 16076 - DBIx searches - performance issues

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(
        undef,
        { columns => [qw/value variable/] }
    )->unblessed();

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