Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    3

    Unanswered: Can we speed this up?

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.
    Last edited by andrewst; 07-27-04 at 08:49.

  3. #3
    Join Date
    Jul 2004
    Posts
    3

    Thumbs up

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •