2020-1

Backend database might require periodic
maintenance
Databases maintain a variety of statistics in order to optimize performance. When high levels of
inserts and/or deletions occur, the statistical data keeping can struggle to keep up. Over a
period of prolonged and intensive processing this can result in a degradation in performance,
with the whole database slowing down as it struggles to clean itself up.
In Connect terms the effect can be felt as the Data Mapper and/or Job Creation progressively
slowing down.
To cure this issue, it is recommended that you periodically run manual maintenance on the
backend database.
If using MySQL, the following script should be run in a query window:
set @a=null,@c=null,@b=concat("show tables where",ifnull(concat("
`Tables_in_",database(),"` like '",@c,"' and"),'')," (@a:=concat_ws
(',',@a,`Tables_in_",database(),"`))");
Prepare `bd` from @b;
EXECUTE `bd`;
DEALLOCATE PREPARE `bd`;
set @a:=concat('optimize table ',@a);
PREPARE `sql` FROM @a;
EXECUTE `sql`;
DEALLOCATE PREPARE `sql`;
set @a=null,@b=null,@c=null;
If using Microsoft SQL Server run the following command in a query window:
sp_updatestats
Page 68