Can we speed this up?

Napivo · #1 (**permalink**) 07-27-04, 04:32

I wonder if there is any way to accelerate a query like this. This one is taking about 5 secs on my access database

Code:

(SELECT '1', Count(tblBase_Logs.Date) AS Cnt From tblBase_Logs 
WHERE (((tblBase_Logs.Date) between #07/04/2004# 
And #07/05/2004#)And ([Spend]> 15)And ([Web] = 'Web2')))
Union (SELECT '2', Count(tblBase_Logs.Date) AS Cnt From tblBase_Logs 
WHERE (((tblBase_Logs.Date) between #07/04/2004# 
And #07/05/2004#)And ([Spend] between 10 and 15)And ([Web] = 'Web2')))
Union (SELECT '3', Count(tblBase_Logs.Date) AS Cnt From tblBase_Logs 
WHERE (((tblBase_Logs.Date) between #07/04/2004# 
And #07/05/2004#)And ([Spend] between 5 and 10)And ([Web] = 'Web2')))
Union (SELECT '4', Count(tblBase_Logs.Date) AS Cnt From tblBase_Logs 
WHERE (((tblBase_Logs.Date) between #07/04/2004# 
And #07/05/2004#)And ([Spend] <= 5)And ([Web] = 'Web2')))

Thanks in advance

andrewst · #2 (**permalink**) 07-27-04, 07:46

Does Access suport CASE?

Code:

SELECT CASE WHEN Spend > 15 THEN '1'
            WHEN Spend between 10 and 15 THEN '2'
            WHEN Spend between 5 and 10 THEN '3'
            WHEN Spend <= 5 THEN '4'
       END,
       Count(tblBase_Logs.Date) AS Cnt 
From   tblBase_Logs 
WHERE  tblBase_Logs.Date between #07/04/2004# And #07/05/2004#
And    Web = 'Web2'
GROUP BY CASE WHEN Spend > 15 THEN '1'
            WHEN Spend between 10 and 15 THEN '2'
            WHEN Spend between 5 and 10 THEN '3'
            WHEN Spend <= 5 THEN '4'
       END

BTW, note that you have a Spend value of 10 falling into both '2' and '3' groups.

Napivo · #3 (**permalink**) 07-27-04, 07:59

I just tried and it doesn’t

Sorry for your nice work.

I have been messing around with my indexes and managed to get the query below one sec of execution. That’s more acceptable than the 5 secs I had before.

I loose a bit of speed while entering data in the database (2 min over 200 000 records) but that’s ok.

Thank you for your time.

Internet Services

Web Development

Digital Marketing

Consumer Tech