SQL report
От | wkipjohn@gmail.com |
---|---|
Тема | SQL report |
Дата | |
Msg-id | 0016e64f68205aab0d046fe35882@google.com обсуждение исходный текст |
Ответы |
Re: SQL report
(Rob Sargent <robjsargent@gmail.com>)
Re: SQL report (Steve Crawford <scrawford@pinpointresearch.com>) |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: