I have a relatively small store with 875 customers making annual purchases. When I go to the Customer report at admin/store/reports/customers it takes 2 minutes to load the initial page and 2 more minutes per any of the 28 pages of the pager. Other reports work fine and load within a reasonable delay.

I have another store, a bigger one, with some 8,600 users. I'm completely unable to load the report even after 30 minutes of processing - that's insane! :-/

Any suggestions?

p.s. the very same store (8,600 users) in Drupal 6 loads the Customer report in less than 2 sec.

CommentFileSizeAuthor
#4 indexes-before-optimization.png25.38 KBbisonbleu
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

bisonbleu created an issue. See original summary.

bisonbleu’s picture

bisonbleu CreditAttribution: bisonbleu commented
Priority: Major » Normal

I don't think this is a bug per se. But it's not really a support or feature request either. It's most likely a performance issue. So changing priority from major to normal (although this remains major for my use case).

The function responsible for the Customer reports is in uc_reports.admin.inc: uc_reports_customers().

I'm thinking that either the db_select query but most likely the foreach loop needs to be optimized to handle my use case. Here are the stats.

  • number of customers with at least 1 order: 7,000
  • number of customers with 0 order: 3,000
  • total number of customers: 10,000
  • number of orders per customer: between 0 and 15
  • total number of orders: 11,500

Questions about the foreach loop:

  1. Could the loop be performed only on the rows of the current page? If so, any pointers about the best way to achieve this?
  2. Can the result of the loop be cached ? Right now, as far as I can see, the whole report is generated each time one selects a different page in the pager. For the current use case, this means 30 minutes per page - not good.

I'm willing to do most of the work here (patch and all) but I need some php and sql guidance for that. Any help would be much appreciated.

Cheers!

bisonbleu’s picture

bisonbleu CreditAttribution: bisonbleu commented
Title: Displaying each page of the Customer report is extremely slow » Displaying the Customer report stalls for 10,000 customers with multiple orders

Better title.

bisonbleu’s picture

bisonbleu CreditAttribution: bisonbleu commented
Status: Active » Closed (works as designed)
FileSize
indexes-before-optimization.png25.38 KB

SOLVED.

After much analyzing and looking around in many directions, rebuilding the indexes in uc_orders did it*. After that was done, the query used the index and scanned only one row (?) instead of 21,500 and returned within 2-3 sec.

*Note - This website was recently migrated from D6 to D7 and 2 of the 3 indexes in uc_orders were empty.

Indexes before optimization

Using the following command fixed them: OPTIMIZE TABLE uc_orders

TR’s picture

TR CreditAttribution: TR commented

Thanks for the information.

Normally what I would suggest is to first use the Devel module to see how long the queries take, which tells you whether the problem is in the database or code. Then if a query takes a long time, look at your MySql settings - large databases usually need different configuration settings than smaller ones.

The uc_reports module is currently a dead end; we intend to replace it with pre- built Views in D8, so we'd rather put the development effort into building the Views than improving the current queries. If this is something you're interested in we'd appreciate the help.

bisonbleu’s picture

bisonbleu CreditAttribution: bisonbleu commented

Can you expand on what you mean by "The uc_reports module is currently a dead end" ? Meaning too rigid ?

I thought about replicating the report with Views.

I'll gladly help if we can first start by refactoring D7.
I will need guidance though.
Can you suggest a step by step plan?

TR’s picture

TR CreditAttribution: TR commented

"Dead end" is an expression, meaning there is no where left to go, no future.

uc_reports was originally written using direct SQL queries, because at that time Views wasn't able to query arbitrary tables. Views has changed a lot since then and is now part of Drupal core as of D8.

Reports generated by uc_reports can't be customized or enhanced by the end user. So while uc_reports provides reports that are useful in general, these reports don't meet the needs of some sites. If we want to make uc_reports better, we would have to build a UI to customize the queries that display the reports - basically we would have to re-invent Views. There is no need to do this because Views can already do this.

We've decided that the best strategy is to replace all the reports in uc_reports with pre-built Views in Drupal 8. This will allow sites to customize their reports, and will make better use of core Drupal, since Views is part of core in D8 - we won't have to maintain our own code to provide reports.

So there will be no further development (no new features or improvements) of uc_reports, we will only be fixing bugs in D7. We want to replace ALL the reports in uc_reports with Views. The Views UI is essentially the same in D7 and D8, so if someone does the work to re-create the reports in Views then we can provide those Views for both D7 and D8.

The plan would be to reproduce the reports one-by-one, using Views. This is a little tricky, because most of the reports are more than simple tables, they have header and footer areas so the View needs to be a compound View. Likewise, most of the reports require aggregation beyond what is available in the Views module, so a helper module like views_aggregator is needed.

I don't have time to do this myself, but I would be happy to answer questions and help test any work you could contribute. I suggest using the issue #568854: Replace uc_reports with Views as a place for the discussion about replacing uc_reports with Views.

bisonbleu’s picture

bisonbleu CreditAttribution: bisonbleu commented

Ok, thanks, all clear. I've replicated the Customer reports. I'll post the export to #568854: Replace uc_reports with Views.