Thank you for your help. It was very helpful!
On Sunday, May 13, 2018, 12:50:26 AM CDT, Helen Borrie hele...@iinet.net.au
[firebird-support] <firebird-support@yahoogroups.com> wrote:
Javier wrote:
>
> Ok, but how do I restore the statistics? Using "update" statements?
You do not "restore the statistics". The statistic that gets updated
by a SET STATISTICS call is the selectivity of the index. Low value =
high selectivity = Good; high value = low selectivity = bad. The
value immediately after the call reflects the state of the index at
that point. So, at that point, the optimizer has the best chance to
prepare the most efficient plan by deciding which indexes will be the
most helpful.
After that, the actual condition of an index selected for the plan may
degrade gradually as rows are inserted and deleted. When you start to
notice a decline in the performance of queries over a table with a
high level of inserts and deletes, it is probably time to run SET
STATISTICS again. Selectivity is not static - it changes
infinitessimally with each insertion and deletion, or dramatically
with a major bulk insert or delete. Eventually, the statistic may
tend to become too outdated to obtain the same level of performance as
when the numbers were fresh.
The optimizer always calculates a plan based on the most recently
calculated statistics. What you can do is run the queries in isql with
SET PLANONLY while the statistics are fresh. That will tell you the
optimizer's choices based on the current selectivity and record count.
You can save that plan in a text file. If you decide from your test
results that you like it, you can copy it into your application
queries using the optional PLAN clause. If you supply a plan in your
select query, the optimizer will not try to create one. That might or
might not work positively over time, of course.
On the whole, the optimizer is going to produce the most effective
plan, since it is aware of the current size if the table and may be
able to make dynamic adjustments that your static plan cannot take
into account.
Hth.
Helen
#yiv1957919616 #yiv1957919616 -- #yiv1957919616ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1957919616
#yiv1957919616ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1957919616
#yiv1957919616ygrp-mkp #yiv1957919616hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv1957919616 #yiv1957919616ygrp-mkp #yiv1957919616ads
{margin-bottom:10px;}#yiv1957919616 #yiv1957919616ygrp-mkp .yiv1957919616ad
{padding:0 0;}#yiv1957919616 #yiv1957919616ygrp-mkp .yiv1957919616ad p
{margin:0;}#yiv1957919616 #yiv1957919616ygrp-mkp .yiv1957919616ad a
{color:#0000ff;text-decoration:none;}#yiv1957919616 #yiv1957919616ygrp-sponsor
#yiv1957919616ygrp-lc {font-family:Arial;}#yiv1957919616
#yiv1957919616ygrp-sponsor #yiv1957919616ygrp-lc #yiv1957919616hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1957919616
#yiv1957919616ygrp-sponsor #yiv1957919616ygrp-lc .yiv1957919616ad
{margin-bottom:10px;padding:0 0;}#yiv1957919616 #yiv1957919616actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1957919616
#yiv1957919616activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1957919616
#yiv1957919616activity span {font-weight:700;}#yiv1957919616
#yiv1957919616activity span:first-child
{text-transform:uppercase;}#yiv1957919616 #yiv1957919616activity span a
{color:#5085b6;text-decoration:none;}#yiv1957919616 #yiv1957919616activity span
span {color:#ff7900;}#yiv1957919616 #yiv1957919616activity span
.yiv1957919616underline {text-decoration:underline;}#yiv1957919616
.yiv1957919616attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv1957919616 .yiv1957919616attach div a
{text-decoration:none;}#yiv1957919616 .yiv1957919616attach img
{border:none;padding-right:5px;}#yiv1957919616 .yiv1957919616attach label
{display:block;margin-bottom:5px;}#yiv1957919616 .yiv1957919616attach label a
{text-decoration:none;}#yiv1957919616 blockquote {margin:0 0 0
4px;}#yiv1957919616 .yiv1957919616bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv1957919616
.yiv1957919616bold a {text-decoration:none;}#yiv1957919616 dd.yiv1957919616last
p a {font-family:Verdana;font-weight:700;}#yiv1957919616 dd.yiv1957919616last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1957919616
dd.yiv1957919616last p span.yiv1957919616yshortcuts
{margin-right:0;}#yiv1957919616 div.yiv1957919616attach-table div div a
{text-decoration:none;}#yiv1957919616 div.yiv1957919616attach-table
{width:400px;}#yiv1957919616 div.yiv1957919616file-title a, #yiv1957919616
div.yiv1957919616file-title a:active, #yiv1957919616
div.yiv1957919616file-title a:hover, #yiv1957919616 div.yiv1957919616file-title
a:visited {text-decoration:none;}#yiv1957919616 div.yiv1957919616photo-title a,
#yiv1957919616 div.yiv1957919616photo-title a:active, #yiv1957919616
div.yiv1957919616photo-title a:hover, #yiv1957919616
div.yiv1957919616photo-title a:visited {text-decoration:none;}#yiv1957919616
div#yiv1957919616ygrp-mlmsg #yiv1957919616ygrp-msg p a
span.yiv1957919616yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1957919616
.yiv1957919616green {color:#628c2a;}#yiv1957919616 .yiv1957919616MsoNormal
{margin:0 0 0 0;}#yiv1957919616 o {font-size:0;}#yiv1957919616
#yiv1957919616photos div {float:left;width:72px;}#yiv1957919616
#yiv1957919616photos div div {border:1px solid
#666666;min-height:62px;overflow:hidden;width:62px;}#yiv1957919616
#yiv1957919616photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1957919616
#yiv1957919616reco-category {font-size:77%;}#yiv1957919616
#yiv1957919616reco-desc {font-size:77%;}#yiv1957919616 .yiv1957919616replbq
{margin:4px;}#yiv1957919616 #yiv1957919616ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv1957919616 #yiv1957919616ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1957919616
#yiv1957919616ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1957919616
#yiv1957919616ygrp-mlmsg select, #yiv1957919616 input, #yiv1957919616 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv1957919616
#yiv1957919616ygrp-mlmsg pre, #yiv1957919616 code {font:115%
monospace;}#yiv1957919616 #yiv1957919616ygrp-mlmsg *
{line-height:1.22em;}#yiv1957919616 #yiv1957919616ygrp-mlmsg #yiv1957919616logo
{padding-bottom:10px;}#yiv1957919616 #yiv1957919616ygrp-msg p a
{font-family:Verdana;}#yiv1957919616 #yiv1957919616ygrp-msg
p#yiv1957919616attach-count span {color:#1E66AE;font-weight:700;}#yiv1957919616
#yiv1957919616ygrp-reco #yiv1957919616reco-head
{color:#ff7900;font-weight:700;}#yiv1957919616 #yiv1957919616ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv1957919616 #yiv1957919616ygrp-sponsor
#yiv1957919616ov li a {font-size:130%;text-decoration:none;}#yiv1957919616
#yiv1957919616ygrp-sponsor #yiv1957919616ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv1957919616
#yiv1957919616ygrp-sponsor #yiv1957919616ov ul {margin:0;padding:0 0 0
8px;}#yiv1957919616 #yiv1957919616ygrp-text
{font-family:Georgia;}#yiv1957919616 #yiv1957919616ygrp-text p {margin:0 0 1em
0;}#yiv1957919616 #yiv1957919616ygrp-text tt {font-size:120%;}#yiv1957919616
#yiv1957919616ygrp-vital ul li:last-child {border-right:none
!important;}#yiv1957919616
