|
|
Posted by T.H.N. on 03/29/06 16:24
I'm trying to work out a database design to make it quicker for my client
program to read and display updates to the data set. Currently it reads in
the entire data set again after each change, which was acceptable when the
data set was small but now it's large enough to start causing noticable
delays. I've come up with a possible solution but am looking for others'
input on its suitability to the problem.
Here is the DDL for one of the tables:
create table epl_packages
(
customer varchar(8) not null, -- \
package_type char not null, -- primary key
package_no int not null, -- /
dimensions varchar(50) not null default(0),
weight_kg int not null,
despatch_id int, -- filled in on despatch
loaded bit not null default(0),
item_count int not null default(0)
)
alter table epl_packages
add constraint pk_epl_packages
primary key (customer, package_type, package_no)
My first thought was to add a datetime column to each table to record the
time of the last change, but that would only work for inserts and updates.
So I figured that a separate table for deletions would make this complete.
DDL would be something like:
create table epl_packages
(
customer varchar(8) not null,
package_type char not null,
package_no int not null,
dimensions varchar(50) not null default(0),
weight_kg int not null,
despatch_id int,
loaded bit not null default(0),
item_count int not null default(0),
last_update_time datetime default(getdate()) -- new column
)
alter table epl_packages
add constraint pk_epl_packages
primary key (customer, package_type, package_no)
create table epl_packages_deletions
(
delete_time datetime,
customer varchar(8) not null,
package_type char not null,
package_no int not null
)
And then these triggers on update and delete (insert is handled automatically
by the default constraint on last_update_time):
create trigger tr_upd_epl_packages
on epl_packages
for update
as
-- check for primary key change
if (columns_updated() & 1792) > 0 -- first three columns: 256+512+1024
insert epl_packages_deletions
select
getdate(),
customer,
package_type,
package_no
from deleted
update A
set last_update_time = getdate()
from epl_packages A
join inserted B
on A.customer = B.customer and
A.package_type = B.package_type and
A.package_no = B.package_no
go
create trigger tr_del_epl_packages
on epl_packages
for delete
as
insert epl_packages_deletions
select
getdate(),
customer,
package_type,
package_no
from deleted
go
The client program would then do the initial read as follows:
select getdate()
select
customer,
package_type,
package_no,
dimensions,
weight_kg,
despatch_id,
loaded,
item_count
from epl_packages
where
customer = {current customer}
order by
customer,
package_type,
package_no
It would store the output of getdate() to be used in subsequent updates,
which would be read from the server as follows:
select getdate()
select
customer,
package_type,
package_no,
dimensions,
weight_kg,
despatch_id,
loaded,
item_count
from epl_packages
where
customer = {current customer} and
last_update_time > {output of getdate() from previous read}
order by
customer,
package_type,
package_no
select
customer,
package_type,
package_no
from epl_packages_deletions
where
customer = {current customer} and
delete_time > {output of getdate() from previous read}
The client program will then apply the deletions and the updated/inserted
rows, in that order. This would be done for each table displayed in the
client.
Any critical comments on this approach and any improvements that could
be made would be much appreciated!
Navigation:
[Reply to this message]
|