Explicit Update Statistics in Unload

forum.wishlist (1998-2005) & forum.gupta.wishlist (2005-2010)
Clifford W. Bass

Explicit Update Statistics in Unload

Post by Clifford W. Bass » 05 Oct 2006, 19:39

 Posted by:  Clifford W. Bass 

Hello,

My wish for today: Please place an explicit "UPDATE STATISTICS ON
DATABASE;" line in the database unload file prior to the storing of any
commands and the creation of any views.

The reason: The past few times that that I have done an unload and
reload in version 9.x I have noticed a distinct slowing down of the
execution of stored commands after an unload. If I do a load using the
unload file as created by the unload it takes 1.92 seconds to execute the
following stored command specifying a table with 236 columns. If I add the
update statistics command into the unload file prior to the view creation,
which is prior to the storing of the commands, it takes 0.01 seconds. The
difference is noticible with other (all?) stored commands.

store SysAdm.GetColumns
select distinct LowName = @lower(T2.Name)
from SysAdm.SysSynonyms T1, SysAdm.SysColumns T2, SysSQL.SysTabAuth T3
where T2.TbCreator = T1.TbCreator and T2.TbName = T1.TbName and TCreator =
T1.TbCreator and TtName = T1.TbName and ((T1.Creator =
USER and exists (select * from SysAdm.SysSynonyms T4 where T4.Creator =
USER and T4.Name = @upper(:1))) or (T1.Creator = 'PUBLIC' and
not exists (select * from SysAdm.SysSynonyms T5 where T5.Creator = USER
and T5.Name = @upper(:1)))) and T1.TbName not like 'SYS%' and
SelectAuth = 'Y' and T1.Name = @upper(:1)
order by 1;

Thank You,

Clifford Bass

Rainer Ebert

Explicit Update Statistics in Unload

Post by Rainer Ebert » 06 Oct 2006, 10:11

 Posted by:  Rainer Ebert 

I disagree with this wish. For us everything is ok without the update
statistics command. And for a >= 10GB database, it would take about 15
minutes longer to load the database if the update statistics command would
be added. So Gupta, please do not change anything.

Rainer

Clifford W. Bass

Explicit Update Statistics in Unload

Post by Clifford W. Bass » 06 Oct 2006, 17:26

 Posted by:  Clifford W. Bass 

Hi All,

How about making it optional? That is, something that can be specified
in either the unload or the load command? Editting huge unload files to add
that command is a pain, but worth it, and if I can avoid it, it would make
my life easier.

By the way, Ranier, do you use stored commands? This seems only to
impact stored commands, although I am not entirely sure that such is the
case. If you do use them, have you actually tested to see if it makes a
difference?

Thanks,

Clifford Bass

Rainer Ebert

Explicit Update Statistics in Unload

Post by Rainer Ebert » 06 Oct 2006, 17:35

 Posted by:  Rainer Ebert 

No we do not use stored commands. With SQLBase 5 we did use them and had a
lot of problems. So we do not use them any more.

Rainer

Return to “gupta.wishlist”

Who is online

Users browsing this forum: [Ccbot] and 0 guests