Hi Steve,
Thanks for you suggestions. In my senario, what is current depends on
users. Because if user wants a status 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
current unless the user tells us what is current.
cheers
John
On Jul 31, 2009 2:41am, Steve Crawford <scrawf...@pinpointresearch.com>
wrote:
wkipj...@gmail.com wrote:
I have the following senario.
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 each object it tracks.
The maximum objects the system will track is 100,000. Which means I will
potentially have a table size of 100 million records.
I have to generate a report on the latest status of all objects being
tracked at a particular point in time, and also I have to allow user to
sort and filter on different columes in the status record displayed in
the report.
...
Just wanna to know if anyone have a different approach to my senario.
Thanks alot.
Not knowing all the details of your system, here are some things you
could experiment with:
1. Add a "latest record id" field in your object table (automatically
updated with a trigger) that would allow you to do a simple join with the
tracking table. I suspect that such a join will be far faster than
calculating "max" 100,000 times at the expense of a slightly larger main
table.
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
appropriately updated). This would also eliminate the need for the "max"
subquery. You could even create a partial index filtering on the "current
record flag" which could speed things up if the reporting query is
written correctly.
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 latest status report will only need a simple
join on the "current" table with a max size of 100,000 rather than a more
complex query over a 100,000,000 record table.
Cheers,
Steve
