Re: SQL report

wkipjohn(at)gmail(dot)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