A simple question

[David_Skov]

I have a table with 100k+ rows. In one coloumn I have an indication of the period of the year (i.e. 200309 = sept. 2003) each period are represented several times.

I need to retrieve the quarters of each year. So for that I use the coloumn with periods in a query. I then sum by totals and through a calculation I get the quarters. Here is the SQL view:

INSERT INTO Kvartal ( Periode, Kvartal )
SELECT Data.Periode, IIf(Right([Periode],2)<4,1,IIf(Right([Periode],2)>3 And Right([Periode],2)<7,2,IIf(Right([Periode],2)>6 And Right([Periode],2)<10,3,4))) AS Kvartal
FROM Data
GROUP BY Data.Periode, IIf(Right([Periode],2)<4,1,IIf(Right([Periode],2)>3 And Right([Periode],2)<7,2,IIf(Right([Periode],2)>6 And Right([Periode],2)<10,3,4)))
HAVING (((IIf(Right([Periode],2)<4,1,IIf(Right([Periode],2)>3 And Right([Periode],2)<7,2,IIf(Right([Periode],2)>6 And Right([Periode],2)<10,3,4))))<=[Choose quarter (ie 3):]));

Problem is that it's so **** slow since it has to retrieve all that data, create totals and then do the calculations. So it's not a wonder it's slow.

But is there an easier way to do this? I hope there is

In advance tx

 

[Andrew Fergus]

Hi David, I presume that [Periode] is indexed but I don't think that will be the problem. I suspect is will be the Right(... bits of the formula that are slowing it down. I can see why you are doing and what the effect is but I wonder if you should have a new field in your table for the quarter and use that field instead of calculating it in the query, e.g.

Period Quarter

200309 3
200310 4
200311 4
200312 4
200401 1
etc.

BUT before you do this, what about creating a new table that looks like this instead?

Period Quarter Year

200309 3 2003
200310 4 2003
200311 4 2003
200312 4 2003
200401 1 2004
etc.

Then you could link this table of periods and quarters to your main data table (through the period field) and then use the periods / quarters from this table in your query. This would be a more efficient way of doing it. It will however require some modification to your query.

There are a couple of options there but I'm pretty sure it will be the calculations within the query that is slowing it down.

HTH, Andrew.

 

[David_Skov]

Tx Andrew, I found a better way:

INSERT INTO Kvartal ( Periode, Kvartal )
SELECT Data.Periode, Int(Right(([Periode]-1),2)/3)+1 AS Kvartal
FROM Data
GROUP BY Data.Periode, Int(Right(([Periode]-1),2)/3)+1
HAVING (((Int(Right(([Periode]-1),2)/3)+1)<=[Choose quarter (ie 3):]));

works like a charm