socialmedian: Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code

Add News Flash

User-submitted headlines for this story

Clip

Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code

Source: Data Management

Aug 17, 2008

Summary

Take a look at this query.

tsqlLine number On/Off | Show/Hide | Select all

SELECT * FROM
(
SELECT customer_id, ‘MTD’ AS record_type, COUNT(*), SUM(…), AVG(…)
FROM payment_table
WHERE YEAR(payment_dt) = YEAR(GETDATE())
and MONTH(payment_dt) = MONTH(GETDATE())
GROUP BY customer_id) MTD_payments
UNION ALL
(
SELECT customer_id, ‘YTD’ AS record_type, COUNT(*), SUM(…), AVG(…)
FROM payment_table
WHERE
WHERE YEAR(payment_dt) = YEAR(GETDATE())
GROUP BY customer_id) YTD_payments
UNION ALL
(
SELECT customer_id, ‘LTD’ AS record_type, COUNT(*), SUM(…), AVG(…)
FROM payment_table) LTD_payments
) payments_report
ORDER BY customer_id, record_type

Code is hidden, SHOW

Can you see the problem?

A person had this query, it would run for over 24 hours. Wow, that is pretty bad, I don’t think I had ever written something that ran over an hour, and the ones I did were mostly defragmentation and update statistics jobs.

The problem is that the following piece of code

tsqlLine number On/Off | Show/Hide | Select all

WHERE YEAR(payment_dt) = YEAR(GETDATE())
and MONTH(payment_dt) = MONTH(GETDAT

...Read the full article

Comments (1)

Denis Gobo,

Aug 18, 2008

Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code. In this case the query run time went from 24 hours+ to 36 seconds!!!