Group by, Order by, sub queries - Performance Problem for getting the previous row value.

Question

I am using MS ACCESS 2003

TABLE NAME –> tmp_cardevent

PERSONID   CARDEVENTDATE  CARDEVENTTIME

5008       20090805       080000
5008       20090805       140000
5008       20090809       180000
5008       20090809       220000
3405       20090805       080000
3405       20090805       180000
3405       20090809       070000
3405       20090809       230000
3010       20080806       090000
3010       20080806       230000
3010       20080810       100000
3010       20080810       160000

I want to display Today time and previous day time for the person id Previous day means not yesterday, previous cardeventtime for the particular person id.

I make the following query for getting previous row value

select t1.Personid, 
    t1.cardeventdate, 
    t1.cardeventtime, 
    t2.Personid,  
    t2.cardeventdate,
    t2.cardeventtime 
from  tmp_cardevent  t1 inner join tmp_cardevent t2 on t1.cardno = t2.cardno 
where t2.cardeventdate =  (
    select max(cardeventdate)  
    from tmp_cardevent ds 
    where ds.cardeventdate < t1.cardeventdate
        and ds.cardno = t1.cardno 
    )

From the above query previous row is displaying perfectly

Expected Output

PERSONID   CARDEVENTDATE      LastCARDEVENTDATE  

5008       20090809           20090805       
3405       20090809           20090805       
3010       20080810           20080806

But if am using group by, order by, sub queries in the above query, it is taking to much time nothing displaying, because it's so bad in performance in huge amount of data

So can anyone help me to get the best solution for such a problem?

or any other query help?

Answer 1

You need to have indexes on your grouped and ordered columns.

e.g. of creating index on column Last_Name in the Customer table. CREATE INDEX IDX_CUSTOMER_LAST_NAME on CUSTOMER (Last_Name)

Similarly you can create indices on the columns used in your group by and order clauses.

Answer 2

SELECT
   Seq = identity(int, 1, 1),
   CardNo,
   CardEventDate
INTO #CardSeq
FROM tmp_cardevent
ORDER BY CardNo, CardEventDate

SELECT
   t1.Personid,
   t1.cardeventdate,
   t1.cardeventtime,
   t2.Personid,
   t2.cardeventdate,
   t2.cardeventtime
from
   tmp_cardevent t1
   inner join #CardSeq S1 ON t1.CardNo = S.CardNo
   left join #CardSeq S2 ON t1.CardNo = t2.CardNo and t1.Seq - 1 = t2.Seq
   left join tmp_cardevent t2 on t1.cardno = t2.cardno

DROP TABLE #CardSeq

Putting an index on the temp table on CardNo and/or Seq should help. Creating the temp table with the indexes before filling it is probably better than adding the indexes after using SELECT INTO. Experiment with a clustered index only on each column, then clustered on one column + nonclustered on the other and vice versa to see what gives the best performance.

If you have conditions on the query to limit the card numbers, do this on the insert to the temp table so it only works on what's necessary.

The left joins are needed otherwise the first event will never show up.