'Auto-extending' partitioned views
From: LCaffrey (lxc_at_eosys.ie)
Date: 06/30/04
- Previous message: PeterWR: "Re: Connect problem MSDE ADO ASP"
- Messages sorted by: [ date ] [ thread ]
Date: 30 Jun 2004 01:26:05 -0700
I have a user table (my_tbl) which receives an insert/update/delete
(DML) operation every 3 to 5 seconds. It has a trigger (TRG1) which
creates an audit trail record in a separate table (my_tbl_trail).
How do I design the partitioned view so that:
1. the creation of the horizontally partitioned tables, and the view
definition happen automatically as the data in my_tbl grows.
2. the granularity is correct w.r.t. 256 table limit in view and
performance;
This is my understanding...
For Q1, I have the idea that the existing trigger, TRG1, is modified
to apply the audit trail records to the audit trail paritioned view
rather than to the audit trail table (SQLServer2000). SQLServer
automatically handles the application of each audit trail record into
the respective table underlying the view. This is fair enough.
This bit I am not so sure of... the partitioned view has an INSTEAD OF
trigger with the following pseudo-code...
...let's say that the current max underlying table is my_tbl_2004_05
(for May/2004)
...and assume that a new record is added to the view with a trans_date
of '1 Jun 2004 00:00:05'
if @trans_date occurs on 1st day of month
-- assuming that all DML are not in the past or future.
(a) dynamically create a new table called my_tbl_2004_06
(b) dynamically update the view definition to include the all my_tbl_YYYY_MM
tables
(c) finally inserts the triggering data into the partitioned view
This could get quite complicated if the DML trans_dates are all over
the place. To get around this, the trigger could check against a
reference list (pinned in memory) to see if a base table exists for
that trans_date in question and if not do (a), (b) and (c) as
described above else just do (c).
For Q2, for monthly tables, I would get about 21 years worth of base
data growth (should be safe enough!). My data file layout is very poor
(inherited) and once the trail table hits 3 months data, performance
drops. All data and indexes are in 1 primary file. Logs, tempdb and
data are all on the 1 RAID 5 array!! I would go for granularity of 1
month and < 2million rows per my_tbl_YYYY_MM. To cap it all, I have
FDA Validation restrictions which means major data surgery is not a
runner.
Does all this sound correct, particularly the automatic handling of
the creation of the underlying table and re-definition of the view.
Any comments would be much appreciated.
Regards
Liam Caffrey
lxc@eosys.ie
- Previous message: PeterWR: "Re: Connect problem MSDE ADO ASP"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
