Slow Subquery to make a calculation with a previous record

[Guest]

Hi,
I hav a table with 3 columns

IndexId Dt Value
1 1/1/06 100
1 1/5/06 110
2 1/2/06 120
2 2/1/06 140

I have a query that brings a previous index value to the current value as..
IndexId Dt Value PrevVal
1 1/5/06 110 100
2 2/1/06 140 120

This query is to make return calculations

My query is as follows

SELECT Index.INDEXid, Index.DT, Index.VALUE, (Select Value From Index X where
Index.Indexid=X.Indexid and X.Dt=(Select max(Dt) from Index y
where y.Indexid=Index.Indexid and y.Dt<Index.Dt)) AS Prevval INTO RATES
FROM [Index];

but it is too slow.. only when i reduce the data size it works... I have
over 40.000 records and 8 different indexid's ..

Would anyone recommend an easier and faster way...

 

[Gary Walter]

I hav a table with 3 columns

IndexId Dt Value
1 1/1/06 100
1 1/5/06 110
2 1/2/06 120
2 2/1/06 140

I have a query that brings a previous index value to the current value
as..
IndexId Dt Value PrevVal
1 1/5/06 110 100
2 2/1/06 140 120

This query is to make return calculations

My query is as follows

SELECT Index.INDEXid, Index.DT, Index.VALUE, (Select Value From Index X
where
Index.Indexid=X.Indexid and X.Dt=(Select max(Dt) from Index y
where y.Indexid=Index.Indexid and y.Dt<Index.Dt)) AS Prevval INTO RATES
FROM [Index];

but it is too slow.. only when i reduce the data size it works... I have
over 40.000 records and 8 different indexid's ..

SELECT
Index.INDEXid,
Index.DT,
Index.VALUE,
(Select
Value
From Index X
where
Index.Indexid=X.Indexid
and
X.Dt=
(Select
max(Dt)
from Index y
where
y.Indexid=Index.Indexid <--why not X.Indexid?
and
y.Dt<Index.Dt) <--why not X.Dt?
) AS Prevval
INTO RATES
FROM [Index];

to me, 40,000 records just doesn't sound
like the problem...

would you mind trying something?

make a copy of your table (structure and date)

name the table "RateIndex"

rename "Value" field to "IndexValue"

{both "Index" and "Value" are reserved
words in Access, but that's not the problem}

****************************
make sure that IndexId and Dt are indexed (dups okay)
****************************

now (if you don't mind), would you mind testing
the speed of this query

SELECT
Cur.IndexID,
Cur.Dt,
Cur.IndexValue,
Max(Prev.Dt) AS PrevDt
FROM
RateIndex AS Cur
LEFT JOIN
RateIndex AS Prev
ON
Cur.IndexID = Prev.IndexID
AND
Cur.Dt > Prev.Dt
GROUP BY
Cur.IndexID,
Cur.Dt,
Cur.IndexValue
ORDER BY
Cur.IndexID,
Cur.Dt;

if the result is suitable, then all you need
is the "previous IndexValue"

save this query (say as "qryPrvDt")

then join it to original table
on IndexID's
and
q.PrevDt = RateIndex.Dt

to get RateIndex.IndexValue As PrevVal

{I am making an assumption here that
there is only one record in the table
for a specific IndexID and Dt?}

SELECT
q.IndexID,
q.Dt,
q.IndexValue,
q.PrevDt,
t.IndexValue AS PrevVal
FROM
qryPrevDt AS q
LEFT JOIN
RateIndex AS t
ON
(q.PrevDt = t.Dt)
AND
(q.IndexID = t.IndexID)
ORDER BY
q.IndexID,
q.Dt;

I imagine the LEFT JOIN's are not needed...
I just threw them in there so you could end
up with a result that made it easy to verify where
all previous values "came from"....