Re: Performance problem when table gets bigger
Posted by:
ICI MASA
Date: July 12, 2005 12:29AM
Hi Jay,
thanks for the reply, I tried to remove the straight_join so the query looks something like this:
SELECT tbPoint.Nname, tbPoint.Nidx, tbPoint.NID, tbPoint.Nunit, tmpTb.Value, tmpTb.VtimeStamp
FROM tbScanList JOIN tbPoint ON tbScanList.TID = tbPoint.TID JOIN
(SELECT v1.DID, v1.NID, v1.Value, v1.VID, v1.VtimeStamp FROM tbValue v1 JOIN
(SELECT NID, DID, max(VID) AS LastUpdate FROM tbValue GROUP BY NID, DID) v2
ON (v1.NID = v2.NID AND 42 = v2.DID AND v1.VID = v2.LastUpdate))
AS tmpTb ON tbPoint.NID = tmpTb.NID
WHERE tbScanList.DID = 42 ORDER BY tbPoint.Nname
But id did not make any difference it still took 20 s to run the query where the tbValue table has 3.000.000 records.
what else could i try?
regards
Martin