Hi @Sanjeeb2022
-
What is the execution time of the query ( in ideal case).
This dataset/query is run on a daily schedule, and typically requires 2 hours.
-
Also can you please check when the query is executed in mysql, any other operation is happening on the tables used in the query.
The DB I’m querying is used for reporting, so it’s being regularly updated with new data from external sources, and other queries are running on it.
-
Please check the server utilization that time and if possible, share more log details to understand what is happening.
The server is running well under capacity during this time frame. The error log for the query only shows one line:
(sql_pq.cc:2985)
- Troubleshoot with the above error message is bit top, you can also post the query ( if it did not contain any sensitive info).
Here is the query:
SELECT STRAIGHT_JOIN
activity.accelo_deployment,
activity.engagement_id,
activity.staff_id,
time_allocation_link.billable/3600 AS "Billable Hours",
time_allocation_link.nonbillable/3600 AS "Non Billable Hours",
(time_allocation_link.billable + time_allocation_link.nonbillable)/3600 AS "Total Hours",
IFNULL(
time_allocation_link.rate_charged,
0
) AS "Billable Rate",
IFNULL(
time_allocation_link.cost_rate_charged,
0
) AS "Cost Rate",
time_allocation_link.charged AS "Billable Value",
time_allocation_link.costing AS "Cost Value",
activity.id AS activity_id,
activity.class_id,
IFNULL(
activity_class.title,
"-Not Selected-"
) AS "Activity Class",
CASE activity.medium
WHEN "email" THEN "Email"
WHEN "note" THEN "Note"
WHEN "meeting" OR "report" THEN "Meeting"
WHEN "call" THEN "Phone Call"
WHEN "postal" THEN "Postal"
ELSE activity.medium
END AS "Medium",
CASE time_allocation_link.status
WHEN "unapproved" THEN "Unapproved"
WHEN "invoiced" THEN "Invoiced"
WHEN "approved" THEN "Approved"
WHEN "locked" THEN "Locked"
ELSE time_allocation_link.status
END AS "Activity Status",
CASE activity.engagement_table
WHEN "affiliation" THEN "Company"
WHEN "job" THEN "Project"
WHEN "milestone" THEN "Project"
WHEN "issue" THEN "Ticket"
WHEN "request" THEN "Request"
WHEN "prospect" THEN "Sale"
WHEN "contract" THEN "Retainer"
WHEN "contract_period" THEN "Retainer"
WHEN "account_invoice" THEN "Invoice"
WHEN "account_purchase" THEN "Purchase"
WHEN "asset" THEN "Asset"
WHEN "deployment" THEN "Deployment"
ELSE activity.engagement_table
END AS "Kind of Work",
CASE activity.engagement_table
WHEN "affiliation" THEN company.name
WHEN "job" THEN job.title
WHEN "milestone" THEN job.title
WHEN "issue" THEN issue.subject
WHEN "request" THEN request.title
WHEN "prospect" THEN prospect.title
WHEN "contract" THEN contract.title
WHEN "contract_period" THEN contract.title
WHEN "account_invoice" THEN account_invoice.subject
WHEN "account_purchase" THEN account_purchase.title
END AS "Work Title",
CASE activity.engagement_table
WHEN "job" THEN job_type.title
WHEN "milestone" THEN job_type.title
WHEN "issue" THEN issue_type.title
WHEN "prospect" THEN prospect_type.title
WHEN "contract" THEN contract_type.title
WHEN "contract_period" THEN contract_type.title
END AS "Work Type",
activity.record_date AS "Date Logged",
CONCAT(staff.firstname, ' ', staff.surname) AS "Logged By",
IF(
staff.status = "active",
"Active",
"Inactive"
) AS "Staff Status",
IFNULL(
rate.title,
"-No Rate-"
) AS "Billable Rate Title",
IFNULL(
cost_rate.title,
"-No Rate-"
) AS "Cost Rate Title",
company.id AS "Company ID",
IFNULL(
company.name,
"-None-"
) AS "Company",
IFNULL(
(
SELECT division.title
FROM division
JOIN division_link
ON division.accelo_deployment = division_link.accelo_deployment
AND division_link.link_table = "company"
AND division.id = division_link.division_id
WHERE activity.accelo_deployment = division.accelo_deployment
AND company.id = division_link.link_id
ORDER BY division_link.id ASC
LIMIT 1
),
"-Unassigned-"
) AS "Primary Client Division",
IFNULL(
(
SELECT title
FROM division
JOIN division_membership
ON division.accelo_deployment = division_membership.accelo_deployment
AND division.id = division_membership.division_id
WHERE activity.accelo_deployment = division.accelo_deployment
AND activity.staff_id = division_membership.staff_id
ORDER BY division_membership.id ASC
LIMIT 1
),
"-Unassigned-"
) AS "Primary Staff Division",
IFNULL(
(
SELECT CONCAT(staff.firstname, " ", staff.surname)
FROM staff
WHERE activity.accelo_deployment = staff.accelo_deployment
AND staff.id = (
SELECT manager_id
FROM manager
WHERE activity.accelo_deployment = manager.accelo_deployment
AND manager.link_id = company.id
AND manager.link_table = "company"
AND manager.nature = "professional"
ORDER BY id ASC
LIMIT 1
)
),
"-Unassigned-"
) AS "Primary Account Manager"
FROM activity
JOIN time_allocation_link
ON activity.accelo_deployment = time_allocation_link.accelo_deployment
AND time_allocation_link.engagement_id = activity.id
AND time_allocation_link.engagement_table = "activity"
JOIN staff
ON activity.accelo_deployment = staff.accelo_deployment
AND activity.staff_id = staff.id
JOIN object_map
ON activity.accelo_deployment = object_map.accelo_deployment
AND activity.engagement_table = object_map.object_table
AND activity.engagement_id = object_map.object_id
JOIN company
ON activity.accelo_deployment = company.accelo_deployment
AND object_map.company_id = company.id
LEFT JOIN activity_class
ON activity.accelo_deployment = activity_class.accelo_deployment
AND activity.class_id = activity_class.id
LEFT JOIN rate
ON activity.accelo_deployment = rate.accelo_deployment
AND time_allocation_link.rate_id = rate.id
LEFT JOIN rate as cost_rate
ON activity.accelo_deployment = cost_rate.accelo_deployment
AND time_allocation_link.cost_rate_id = cost_rate.id
LEFT JOIN job
ON activity.accelo_deployment = job.accelo_deployment
AND object_map.job_id = job.id
AND activity.engagement_table IN ('job','milestone')
LEFT JOIN issue
ON activity.accelo_deployment = issue.accelo_deployment
AND activity.engagement_id = issue.id
AND activity.engagement_table = 'issue'
LEFT JOIN request
ON activity.accelo_deployment = request.accelo_deployment
AND activity.engagement_id = request.id
AND activity.engagement_table = 'request'
LEFT JOIN prospect
ON activity.accelo_deployment = prospect.accelo_deployment
AND activity.engagement_id = prospect.id
AND activity.engagement_table = 'prospect'
LEFT JOIN contract
ON activity.accelo_deployment = contract.accelo_deployment
AND object_map.contract_id = contract.id
AND activity.engagement_table IN ('contract','contract_period')
LEFT JOIN account_invoice
ON activity.accelo_deployment = account_invoice.accelo_deployment
AND activity.engagement_id = account_invoice.id
AND activity.engagement_table = 'account_invoice'
LEFT JOIN account_purchase
ON activity.accelo_deployment = account_purchase.accelo_deployment
AND activity.engagement_id = account_purchase.id
AND activity.engagement_table = 'account_purchase'
LEFT JOIN job_type
ON activity.accelo_deployment = job_type.accelo_deployment
AND job.type_id = job_type.id
LEFT JOIN contract_type
ON activity.accelo_deployment = contract_type.accelo_deployment
AND contract.type_id = contract_type.id
LEFT JOIN prospect_type
ON activity.accelo_deployment = prospect_type.accelo_deployment
AND prospect.type_id = prospect_type.id
LEFT JOIN issue_type
ON activity.accelo_deployment = issue_type.accelo_deployment
AND issue.type_id = issue_type.id
WHERE (time_allocation_link.billable > 0 OR time_allocation_link.nonbillable > 0)
AND activity.record_date >= MAKEDATE(year(now()-interval 3 year),1)
Appreciate the assistance!