Procedure for storing in backup tables?

Question

I have two tables, sales and salesProducts because of the volume of information in these tables reading/writing operations are slow. I need to create a database that stores this data (from the sales and sales tables), for a certain period, in backup tables (bkpVenda and bkpVendaProduct). The period will be informed by the user. How should I make this process?

create table venda (
ven_ID   int not null identity (1,1) constraint PKVenda primary key,
cli_ID   int not null,
fun_ID   int not null, 
ven_Data date not null,
constraint fkVenCli foreign key (cli_ID) references cliente(cli_ID),
constraint fkVenFun foreign key (fun_ID) references funcionario(fun_ID))

;

create table vendaProduto (
vpr_ID          int identity (1,1) constraint PKVendaProduto primary key,
ven_ID          int not null,
liv_ID          int not null, 
vpr_Quantidade  int,
vpr_ValorUnit   decimal(15,2),
constraint fkItvVen foreign key (ven_ID) references venda(ven_ID),
constraint fkItvliv foreign key (liv_ID) references livro(liv_ID))

;

Answer 1

You can do it this way:

if object_id('realizar_backup', 'P') is null
begin
  exec('create procedure realizar_backup as');
end;
go

alter procedure realizar_backup @data_inicio datetime,
                                @data_fim    datetime
as
begin
  set nocount on;

  insert into bkpVenda(ven_ID,
                       cli_ID,
                       fun_ID, 
                       ven_Data)
  select v.ven_ID,
         v.cli_ID,
         v.fun_ID, 
         v.ven_Data
    from venda with(readpast) v
   where v.ven_Data between data_inicio and data_fim;

  insert into bkpVendaProduto(vpr_ID,
                              ven_ID,
                              liv_ID 
                              vpr_Quantidade,
                              vpr_ValorUnit)
  select vp.vpr_ID,
         vp.ven_ID,
         vp.liv_ID 
         vp.vpr_Quantidade,
         vp.vpr_ValorUnit
    from venda with(readpast) v
         inner join vendaProduto with(readpast) vp on vp.ven_ID = v.ven_ID
   where v.ven_Data between data_inicio and data_fim;
end;
go

However I advise you to record in tables log at the time the transactions are made (insert, update, delete) and then just check for dates. But if it’s just to save this procedure solves your problem.

Answer 2

Before modifying the database structure, and its impact on applications, I suggest an analysis of what is causing the slowness. They may be outdated statistics, some query that needs to be optimized, fragmented or missing indexes etc. In short, typical database administration tasks.

After the analysis, it may be indicated the creation of additional indices. For example, if there is a high number of queries in the "sale" table per period, then it is likely that the creation of index by the ven_Data column will optimize these queries. Of course, in addition to the ven_Data column, it is necessary to evaluate cover columns that should be in that index.

In the sales tableProduct seems to me that should be reevaluated what will compose the index clustered. The vpr_ID column as the index key clustered, seems useless to me. It could even be kept as a primary key, but on an index nonclustered. Or better yet, just declare that column as Unic.

Among the several candidate keys of the "salesProduct" table, perhaps the ven_ID column is the ideal to be chosen as index key clustered. It can be a simple key, accepting duplicities, or a composite key (ven_ID + vpr_ID), exclusive (no repetitions).

The suggestion is that, before thinking about modifying the database structure, find the current bottlenecks.

Regarding the physical space occupied, analyze how much each column occupies. For example only, the column Table quantity "salesProduct" is declared as int. That is, it occupies 4 bytes and accepts values of up to 2 million and little. Did any book have more than 32,000 copies sold in the same transaction? If the quantity of each item sold is less than 32 thousand units, you may declare the column as smallint, that then the column will occupy half of the space (2 bytes).

The same reasoning for the fun_ID column in the "sale" table, which is also stated as int. Are there more than 32,000 employees in this bookstore?! Of course not! This column could be declared as smallint (up to about 32 thousand employees) or even tinyint (up to 255 employees).

The "sale" table contains the date of sale but does not include the time. If it is necessary to also record the time of sale, evaluate the use of the type smalldatetime. The guy date occupies 3 bytes and smalldatetime occupies 4 bytes.

These are details that, in the end, make the difference in performance.