May 3, 2011 at 12:23 pm
I do not think that is the issue, these are queries that are run by our ERP.
But thank you for that.
May 3, 2011 at 2:45 pm
You should be able to capture what is getting run via profiler, and run it on both servers and dig into what it's actually doing. I'd be sure to verify NOT IN isn't being used.
May 6, 2011 at 12:33 pm
Do you see any thring wrong with this query? This is the one I have been testing, runs 10 times faster on x32.
SELECT COALESCE(customer_credit_history.company_id, v_current.company_id) company_id
,COALESCE(customer_credit_history.customer_id, v_current.customer_id) customer_id
,COALESCE(customer_credit_history.year_invoiced, v_current.current_year) year_invoiced
,COALESCE(customer_credit_history.month_invoiced, v_current.current_month) month_invoiced
,COALESCE(customer_credit_history.high_credit_used, 0) high_credit_used
,COALESCE(customer_credit_history.amount_paid, 0) amount_paid
,COALESCE(customer_credit_history.avg_fast_slow_days, 0) avg_fast_slow_days
,COALESCE(customer_credit_history.sum_days_x_payment, 0) sum_days_x_payment
,COALESCE(customer_credit_history.invoiced_sales, 0) + COALESCE(customer_credit_history.invoiced_other_charges, 0) invoiced_sales
,COALESCE(company.company_name, v_current.company_name) company_name
,COALESCE(customer.customer_name, v_current.customer_name) customer_name
FROM customer_credit_history
INNER JOIN customer ON customer.customer_id = 10516
AND customer.company_id = '1'
INNER JOIN company ON company.company_id = '1'
FULL JOIN ( SELECT DATEPART(month, current_timestamp) current_month
,DATEPART(year, current_timestamp) current_year
,10516 customer_id
,'1' company_id
,company.company_name
,customer.customer_name
FROM company
LEFT OUTER JOIN customer ON customer.company_id = company.company_id
AND customer.customer_id = 10516
WHERE company.company_id = '1'
) v_current ON v_current.current_month = customer_credit_history.month_invoiced
AND v_current.current_year = customer_credit_history.year_invoiced AND v_current.customer_id = customer_credit_history.customer_id
AND v_current.company_id = customer_credit_history.company_id
WHERE COALESCE(customer_credit_history.customer_id, v_current.customer_id) = 10516 AND COALESCE(customer_credit_history.company_id, v_current.company_id) = '1'
ORDER BY customer_credit_history.year_invoiced DESC, customer_credit_history.month_invoiced DESC
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply