SQL command query taking a long time

[nmair]

Hello there.

The below query is running very slow. Is there anyway I can change it to make it run quicker?

SELECT ({fn MONTHNAME(transactiondate)}), acctno, COUNT(DISTINCT mcc) AS DISTINCTMCC
FROM transactions
WHERE (response='50' OR response='01') AND fraud='F' AND channel='02' AND (trantype='P' OR trantype='PA') AND (transactiondate>={ts '2010-11-01 00:00:00'} AND transactiondate<{ts '2011-04-01 00:00:00'})
GROUP BY ({fn MONTHNAME(transactiondate)}), acctno

 

[nmair]

Sorry folks,

I forgot to mention that there is 120 million records returned before grouping begins.

 

[lbass]

I wonder whether using {fn month()} might be faster. The DISTINCT count is probably what is really slowing things down though.

-LB