Re: SQL report - Mailing list pgsql-sql

Hi Steve,<br /><br />Thanks for you suggestions. In my senario, what is current depends on users. Because if user wants
astatus report at 00:00 1st Jan 2009, then 00:00 1st Jan 2009 is current. So it is not possible to flag any records as
currentunless the user tells us what is current. <br /><br />cheers<br />John<br /><br />On Jul 31, 2009 2:41am, Steve
Crawford<scrawford@pinpointresearch.com> wrote:<br />> wkipjohn@gmail.com wrote:<br />> <br />> <br
/>>I have the following senario.<br />> <br />> <br />> <br />> I have a tracking system. The system
willrecord the status of an object regularly, all the status records are stored in one table. And it will keep a
historyof maximum 1000 status record for each object it tracks. The maximum objects the system will track is 100,000.
Whichmeans I will potentially have a table size of 100 million records.<br />> <br />> <br />> <br />> I
haveto generate a report on the latest status of all objects being tracked at a particular point in time, and also I
haveto allow user to sort and filter on different columes in the status record displayed in the report.<br />> <br
/>><br />> ...<br />> <br />> <br />> <br />> <br />> Just wanna to know if anyone have a
differentapproach to my senario. Thanks alot.<br />> <br />> <br />> <br />> <br />> Not knowing all the
detailsof your system, here are some things you could experiment with:<br />> <br />> <br />> <br />> 1.
Adda "latest record id" field in your object table (automatically updated with a trigger) that would allow you to do a
simplejoin with the tracking table. I suspect that such a join will be far faster than calculating "max" 100,000 times
atthe expense of a slightly larger main table.<br />> <br />> <br />> <br />> 2. Add a "current record
flag"in the status table that simply flags the most recent record for each object (again, use triggers to keep the flag
appropriatelyupdated). This would also eliminate the need for the "max" subquery. You could even create a partial index
filteringon the "current record flag" which could speed things up if the reporting query is written correctly.<br
/>><br />> <br />> <br />> 3. Partition the table into a "current status table" and "historical status
table"(each inheriting from the main table). Use a trigger so that anytime a new status record in added, the old
"current"record is moved from the "current" to the "historical" table and the new one added to the "current" table. The
lateststatus report will only need a simple join on the "current" table with a max size of 100,000 rather than a more
complexquery over a 100,000,000 record table.<br />> <br />> <br />> <br />> Cheers,<br />> <br />>
Steve<br/>> <br />> <br />>