Friday, February 24, 2012

can somebody help my optimise my code please

Hi

I have the following SQL, which I am using to query our mainframe (some kind of IBM DB2 type thing)
Basically there are two tables joined by account number. One contains info about the customer, and the other contains details about transactions that have happened for each month. I am trying to get the total spend for last month and the month before. (I know I am not using anything from the CUS_ACC table at the mo, but I will need to)

SELECT PROD.CUST_ACC_NUM, Sum(SPEND) AS SUMSPEND, STMT_MONTH, STMT_YEAR
FROM PROD INNER JOIN CUST_ACC ON PROD.CUST_ACC_NUM=CUST_ACC.CUST_ACC_NUM

WHERE PROD.CUST_ACC_NUM=630822
And
(
(STMT_MONTH=Month(DateAdd("m",-1,Date())) And STMT_YEAR=Year(DateAdd("m",-1,Date())))
Or
(STMT_MONTH=Month(DateAdd("m",-2,Date())) And STMT_YEAR=Year(DateAdd("m",-2,Date())))
)
GROUP BY PROD.CUST_ACC_NUM, STMT_MONTH, STMT_YEAR;

now, as the query is at the moment, it takes tens of minutes to run for a single account (I will have to do it for about 3,000 accounts). However, if remove the line that starts with 'OR' and the line below it (i.e. only get last months sales) it runs in seconds.

Any ideas why?
Thanks
Kevinforget it, its working at full speed. hmmmmmm, very very strange

No comments:

Post a Comment