Re: SQL report

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 <scrawford(at)pinpointresearch(dot)com>
wrote:
> 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