I have the following senario.<br /><br />I have a tracking system. The system will record the status of an object
regularly,all the status records are stored in one table. And it will keep a history of maximum 1000 status record for
eachobject it tracks. The maximum objects the system will track is 100,000. Which means I will potentially have a table
sizeof 100 million records.<br /><br />I have to generate a report on the latest status of all objects being tracked at
aparticular point in time, and also I have to allow user to sort and filter on different columes in the status record
displayedin the report.<br /><br />The following is a brief description in the status record (they are not actual
code)<br/><br />ObjectRecord(<br /> objectId bigint PrimaryKey<br /> desc varchar <br />)<br /><br />StatusRecord (<br
/>id bigint PrimaryKey<br /> objectId bigint indexed<br /> datetime bigint indexed<br /> capacity double <br />
reliabilitydouble<br /> efficiency double<br />)<br /><br />I have tried to do the following, it works very well with
around20,000 objects. (The query return in less than 10s) But when I have 100,000 objects it becomes very very slow. (I
don'teven have patience to wait for it to return.... I kill it after 30 mins) <br /><br />select * from statusrecord s1
INNERJOIN ( SELECT objectId , MAX(datetime) AS msdt FROM statusrecord WHERE startDatetime <= 1233897527657 GROUP BY
objectId) AS s2 ON ( s1.objectId = s2.objectId AND s1.datetime = s2.msdt ) where ( capacity < 10.0 ) order by
s1.datetimeDESC, s1.objectId DESC;<br /><br />I did try to write a store procedure like below, for 100,000 objects and
1000status records / object, it returns in around 30 mins. <br /><br />CREATE OR REPLACE FUNCTION getStatus(pitvalue
BIGINT)RETURNS SETOF statusrecord AS $BODY$<br />DECLARE<br /> id VARCHAR;<br /> status statusrecord%ROWTYPE;<br
/>BEGIN<br/> FOR object IN SELECT * FROM objectRecord <br /> LOOP<br /> EXECUTE 'SELECT * FROM statusrecord WHERE
objectId= ' || quote_literal(object.objectId) || <br /> ' AND datetime <= ' || quote_literal(pitvalue) || ' ORDER BY
datetimeDESC'<br /> INTO status;<br /> IF FOUND THEN <br /> RETURN NEXT status;<br /> END IF;<br /> END LOOP;<br />
RETURN;<br/>END<br />$BODY$ LANGUAGE plpgsql;<br /><br />Just wanna to know if anyone have a different approach to my
senario.Thanks alot. <br /><br />John
