Hello all,
This is a query I’ve been thinking about for quite some time that I would like to optimize to run efficiently in MySQL. It began life in an ASP/Access app about 12 years ago that ran a straight (no joins) query against an Access database of contacts returning about 600 rows. That was saved in an array which was then looped and each row ran another query against a couple of tables that pulled out yearly grade information. It’s not the most efficient way of doing it but it worked.
Now we’re using a more sophisticated CRM on PHP/MySQL called CiviCRM and I’m rebuilding this part of the system as a custom plugin.
In order to achieve the same results as the old system, I’ve duplicated what we had originally done to get a baseline to start with. It’s dead slow compared to the old ASP/Access version so I have some work to do.
Here’s how I’m approaching it:
I have a main query to pull in the contact information as follows:
$sql = "SELECT contact.id
, contact.last_name
, contact.first_name
, contact.external_identifier
, contact.user_unique_id
, membership.membership_type_id
, membership.id AS membership_id
, membership.join_date AS member_since
, membership_type.name AS member_type
FROM contact
INNER JOIN membership
ON contact.id = membership.contact_id
INNER JOIN membership_type
ON membership.membership_type_id = membership_type.id
WHERE contact.first_name IS NOT NULL
AND contact.last_name IS NOT NULL
ORDER BY contact.last_name";
The CRM has a database abstraction layer that allows me to get a data access object and I can either loop in into an array or loop and execute a query for each row in my main query. I’ve found it is slightly more efficient looping directly from the dataset so here’s how that part works (I’ve skipped a bunch of the html cruft involved to keep the concept from getting muddled up):
Anything in the code below that is prefixed with $dao-> is a value from the results of the query above and specific to the row that is in focus during that iteration of the While loop.
// Execute the query above and return a dataset object
$dao = CRM_Core_DAO::executeQuery($sql);
// START HTML string variable to hold a table of results $report_table = "<table>, etc..";
while( $dao->fetch( ) ) {
$sql = "SELECT professional_development_categories.id AS id
, professional_development_categories.category AS category
, SUM(professional_development_activities.credits) AS credits
, professional_development_categories.minimum
, professional_development_categories.maximum
, professional_development_categories.description
FROM professional_development_categories
LEFT OUTER JOIN professional_development_activities
ON professional_development_activities.category_id = professional_development_categories.id
AND professional_development_activities.contact_id = " . $dao->id . "
AND EXTRACT(YEAR FROM professional_development_activities.credit_date) = " . $_SESSION["report_year"] . "
GROUP BY professional_development_categories.id";
// Populate the variable that holds the HTML table of results
$report_table .= '<tr>';
$report_table .= '<td>' . $dao->last_name . '</td>';
$report_table .= '<td>' . $dao->first_name . '</td>';
$report_table .= '<td>' . $dao->member_type . '</td>';
$report_table .= '<td>' . $dao->member_since . '</td>';
// Execute the sub query for each row in the main query to get this years scores for that individual
$subdao = CRM_Core_DAO::executeQuery($sql);
$total_credits = 0;
$sub_cells = "";
while( $subdao->fetch( ) ) {
$total_credits += abs($subdao->credits);
$sub_cells .= '<td>' . abs($subdao->credits) . '</td>';
}
$report_table .= '<td>' . $total_credits . '</td>';
$report_table .= $sub_cells;
$report_table .= '</tr>';
}
// END HTML to create a table of results $report_table .= "</table>, etc..";
So hopefully that isn’t too convoluted and makes some sense. I’m looking to make some improvements to it and speed it up. I’d like to combine the queries but I’m not sure how I can do that.
Thanks,
Andrew