CRM-18562 Performance issues with many discounts

          Details

          • Type: Bug
          • Status: Done/Fixed
          • Priority: Minor
          • Resolution: Done
          • Affects Version/s: 4.6
          • Fix Version/s: Unscheduled
          • Component/s: None
          • Labels:
            None
          • Documentation Required?:
            None
          • Funding Source:
            Needs Funding

            Description

            (Cross-posted from https://github.com/dlobo/org.civicrm.module.cividiscount/issues/154 – what's the best place for issues on this extension?)

            I have a site here with over 150 active discount codes, all based on either event ID or event type, none of which are set for Automatic Discounts.

            When loading any event registration page, the page takes around 30 seconds to load. Xdebug profiling output shows that this delay is caused by CRM_CiviDiscount_DiscountCalculator::checkDiscountsByEntity(), which performs a 'get event' API call on every one of those discount codes, in order to check whether any events meet the requirements of the discount code.

            Of course, CiviDiscount has to know if the event has an active discount code, so that it can display or hide the Discount Code field.

            So at this point I'm thinking that the data structure is the real problem here: currently, the storage mechanism associating a discount code with one or more events, or one or more event types, etc., is a single column delimited with CRM_Core_DAO::VALUE_SEPARATOR. This makes it hard to run a single query that can tell what discounts are associated with any given event, which is why the code is running one 'get event' API call for every discount code.

            Is it worth discussing changes to that schema for the sake of avoiding long page loads with large numbers of discount codes?

              Attachments

                Activity

                [CRM-18562] Performance issues with many discounts
                Allen Shaw added a comment - - edited

                TODO: It should be possible to optimize this by querying for "all events using this discount" with something like 

                 ... WHERE `[correct_column_name]` LIKE  '%[CRM_Core_DAO::VALUE_SEPARATOR][event_id][CRM_Core_DAO::VALUE_SEPARATOR]%'

                , thus avoiding this issue without refactoring the data schema.

                Allen Shaw added a comment -

                My suggestion above turns out to be a little naive, as there are a wide variety of potential criteria – especially when you consider 'advanced filters' – meaning that you really do need to examine each discount code against whatever API parameters are stored in the cividiscount_item table.

                I've found one performance improvement for cases where limiting only by entity ID (e.g., only for specific events), and will post a PR for that fix.

                Allen Shaw added a comment -

                PR: https://github.com/dlobo/org.civicrm.module.cividiscount/pull/157

                Coleman Watts added a comment -

                Thanks for your work on this Allen Shaw I've merged the first PR. Feel free to keep 'em coming.

                Allen Shaw added a comment -

                Thanks Coleman Watts! I think that PR contains all of the easy wins. That's probably all I'll have on this issue.

                  People

                  • Assignee:
                    Allen Shaw
                    Reporter:
                    Allen Shaw

                    Dates

                    • Created:
                      Updated:
                      Resolved: