June 8, 2015 at 10:40 am
I am designing a system that reconciles transactions by month and account. When a month is done it gets 'closed.' I have not yet decided how to handle 'closed' records and would like some advice.
My two options are to either simply flag records as 'closed' or to move closed records to another datastore. I have been pondering the pros and cons of each approach but have not yet come up with a conclusive answer.
We have two main tables. In a normal month we average 11,000 to 13,00 records in one table, 7,000 to 8,000 in the other.
If you have concrete experience of such a scenario, your advice and suggestions would be appreciated!
Here are my two options:
Option 1. Flag records as 'closed.'
PROS:
- Fewer code changes in the fetch procedure.
- Lock 'closed' records via an UPDATE trigger that prevents updates on closed records.
CONS:
- Will require structural changes to tables and all related views and queries
- Over time, old record backlog may slow overall system performance
Option 2. Move closed records to another data store (either other tables or another database)
PROS:
- Performance savings realized by keeping active tables smaller
- Easier to keep closed records 'read only' via permissions on the other tables
CONS:
- More code changes will be needed to let user switch between 'archive view' mode and regular mode
- Extra database(s) to back up, administer, etc
Any thoughts or suggestions?
Kind thanks,
Barry
June 8, 2015 at 11:19 am
Option 3???
Flag records as closed and move them to archive after xx months?
There will probably be a need to view, and possibly change these records for a period of time after the month is closed.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 8, 2015 at 11:22 am
Couple thought, first 13000 records a month is a relatively small data set. Second why are you trying to lock the records at the data base level, wouldn't you be better off doing that at the application layer? For example a trigger to lock records sounds cool until you need to unlock them.
June 8, 2015 at 11:26 am
Michael: Once the records for a month are marked as 'reconciled,' they'll be DONE. No further changes. My only question is the best way to implement such a change. I want them viewable, but I don't want them slowing down the system when working with current, unreconciled records.
ZZArtin: My thought on a 'trigger' was just that, a thought. I'm trying to come up with ways to implement this with a minimum of code changes. Application layer is fine too... I'm just looking for the best combination of efficient code and efficient server storage/retrieval.
June 8, 2015 at 11:55 am
First, I'd suggest a column called ClosedDate. But until you scale up to several millions of rows, then partitioning your table(s) for the sake of performance is not worthwhile. However, given your requirement to enforce a read-only constraint on accounting periods that have been "closed" while keeping the application queries simple (one table), a case may be made for table partitioning and ReadOnly partitions where ClosedDate is leveraged for the partitioning function.
How To Decide if You Should Use Table Partitioning
http://www.brentozar.com/archive/2012/03/how-decide-if-should-use-table-partitioning/
CREATE PARTITION FUNCTION
https://msdn.microsoft.com/en-us/library/ms187802.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 8, 2015 at 12:00 pm
Eric: Thanks very much for the helpful information regarding the number of records needed before partitioning becomes required. It may be that I'm worrying over a problem that won't occur in my lifetime. 😀
Add to that the fact that my SQL Server is running in a virtual server anyway. I'm thinking table partitioning would make no effective difference --- the whole thing would live on one disk in one file, regardless of how many partitions I make.
June 8, 2015 at 12:10 pm
bimplebean (6/8/2015)
Eric: Thanks very much for the helpful information regarding the number of records needed before partitioning becomes required. It may be that I'm worrying over a problem that won't occur in my lifetime. 😀Add to that the fact that my SQL Server is running in a virtual server anyway. I'm thinking table partitioning would make no effective difference --- the whole thing would live on one disk in one file, regardless of how many partitions I make.
Insuring that rowsets belonging to closed accounting periods are not updated is a worthwhile requirement by itself. In many cases partitioning is leveraged for no other reason than logical segregation of rowsets, for example making rows with the ClosedDate column populated ReadOnly. Users can query the entire table but could not update, delete, or insert(?) rows for a closed period. However, I'm not sure about inserting rows for closed periods.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 8, 2015 at 12:29 pm
I think we'll just add a ClosedDate datetime column and prevent changes to those records if the field is not NULL. The UI (a WinForms app loading DataGridView controls) should load just fine. If we see performance problems after that, we'll revisit it.
Thanks!
June 8, 2015 at 12:44 pm
Or like you said originally, this could be done using INSTEAD OF triggers.
For example, you could create a ClosedPeriods table containing one row for each closed period. Using this method there is no need to add another ClosedDate column to the transactional table.
CREATE TABLE RentCollected
(
UnitID int not null,
YearMonth char(7) not null,
CollectedAmt money not null
);
GO
insert into RentCollected ( UnitID, YearMonth, CollectedAmt )
values ( 1, '2015/05', 500.00 ), ( 1, '2015/06', 0.00 );
GO
CREATE TABLE ClosedPeriods
(
YearMonth char(7) not null
);
GO
insert into ClosedPeriods ( YearMonth ) values ('2015/05');
GO
create trigger trg_RentCollected_DisallowUpdateOnClosedPeriod
on RentCollected
instead of update
AS
if exists(select 1 from inserted i
where I.YearMonth in (select YearMonth from ClosedPeriods))
begin;
raiserror('Error', 16, 1);
return;
end;
update RC
set RC.CollectedAmt = I.CollectedAmt
from RentCollected RC
join inserted I on I.UnitID = RC.UnitID
and I.YearMonth = RC.YearMonth;
GO
update RentCollected
set CollectedAmt = 600.00
where UnitID = 1 and YearMonth = '2015/05';
Msg 50000, Level 16, State 1,
Procedure trg_RentCollected_DisallowUpdateOnClosedPeriod,
Line 43
Error
update RentCollected
set CollectedAmt = 600.00
where UnitID = 1 and YearMonth = '2015/06';
(1 row(s) affected)
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 8, 2015 at 12:47 pm
Slick. I'd had a similar thought.
Thanks again!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply