CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using CiviContribute (Moderator: Donald Lobo) »
  • Large Contributions Table
Pages: [1]

Author Topic: Large Contributions Table  (Read 750 times)

Hershel

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4640
  • Karma: 176
    • CiviHosting
  • CiviCRM version: Latest
  • CMS version: Mostly WordPress and Drupal
Large Contributions Table
January 23, 2010, 02:06:51 pm
I have a Drupal/CiviCRM 3.0.4 site with ~470K records in civicrm_contribution. If we run a search for all Contribution records, this is the query:

Code: [Select]
SELECT count(*) FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id INNER JOIN civicrm_contribution_type ON civicrm_contribution.contribution_type_id = civicrm_contribution_type.id LEFT JOIN civicrm_option_group option_group_payment_instrument ON ( option_group_payment_instrument.name = 'payment_instrument') LEFT JOIN civicrm_option_value payment_instrument ON (civicrm_contribution.payment_instrument_id = payment_instrument.value
AND option_group_payment_instrument.id = payment_instrument.option_group_id ) LEFT JOIN civicrm_option_group option_group_contribution_status ON (option_group_contribution_status.name = 'contribution_status') LEFT JOIN civicrm_option_value contribution_status ON (civicrm_contribution.contribution_status_id = contribution_status.value
AND option_group_contribution_status.id = contribution_status.option_group_id ) WHERE ( civicrm_contribution.is_test = 0 ) AND ( 1 )
but it fails to finish. This is due to a script that monitors the server's sql queries for time and disk space. Apparently, it took 1.3 GB of disk space in the tmp table and was therefore terminated. An SQL dump of the full DB is only 180 MB, however. Using parameters in the Contribution search works.

Any thoughts on this?

Thanks.
CiviHosting and CiviOnline -- The CiviCRM hosting experts, since 2007

See here for the official: What to do if you think you've found a bug.

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Large Contributions Table
January 23, 2010, 05:37:59 pm

the contribution query (and other component queries) can be optimized significantly to improve performance. There has been an issue for this for quite a few years. If important to you or your client please consider looking into optimizing the query and/or sponsoring the core development team to look and improve this. I suspect it is a 50 hours or so project. this would be great for scalability of civicrm :)

however your below use case of doing a query and extracting all 470K contributions and contact names will still take some amount of time (though the query can be optimized to make it constant space)

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using CiviContribute (Moderator: Donald Lobo) »
  • Large Contributions Table

This forum was archived on 2017-11-26.