Re: [firebird-support] Doubts regarding statistics of indexes and tables...
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]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:#ff;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,
Re: [firebird-support] Doubts regarding statistics of indexes and tables...
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
Re: [firebird-support] Doubts regarding statistics of indexes and tables...
Ok, thank you very much! On Saturday, May 12, 2018, 11:42:52 AM CDT, liviuslivius liviusliv...@poczta.onet.pl [firebird-support]wrote: Hi, i do not suppose that this is possible and especially that you should do this. Better then look at queries and addapt it to real situation. Regards,Karol Bieniaszewski #yiv2598329866 #yiv2598329866 -- #yiv2598329866ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv2598329866 #yiv2598329866ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv2598329866 #yiv2598329866ygrp-mkp #yiv2598329866hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv2598329866 #yiv2598329866ygrp-mkp #yiv2598329866ads {margin-bottom:10px;}#yiv2598329866 #yiv2598329866ygrp-mkp .yiv2598329866ad {padding:0 0;}#yiv2598329866 #yiv2598329866ygrp-mkp .yiv2598329866ad p {margin:0;}#yiv2598329866 #yiv2598329866ygrp-mkp .yiv2598329866ad a {color:#ff;text-decoration:none;}#yiv2598329866 #yiv2598329866ygrp-sponsor #yiv2598329866ygrp-lc {font-family:Arial;}#yiv2598329866 #yiv2598329866ygrp-sponsor #yiv2598329866ygrp-lc #yiv2598329866hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv2598329866 #yiv2598329866ygrp-sponsor #yiv2598329866ygrp-lc .yiv2598329866ad {margin-bottom:10px;padding:0 0;}#yiv2598329866 #yiv2598329866actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv2598329866 #yiv2598329866activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv2598329866 #yiv2598329866activity span {font-weight:700;}#yiv2598329866 #yiv2598329866activity span:first-child {text-transform:uppercase;}#yiv2598329866 #yiv2598329866activity span a {color:#5085b6;text-decoration:none;}#yiv2598329866 #yiv2598329866activity span span {color:#ff7900;}#yiv2598329866 #yiv2598329866activity span .yiv2598329866underline {text-decoration:underline;}#yiv2598329866 .yiv2598329866attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv2598329866 .yiv2598329866attach div a {text-decoration:none;}#yiv2598329866 .yiv2598329866attach img {border:none;padding-right:5px;}#yiv2598329866 .yiv2598329866attach label {display:block;margin-bottom:5px;}#yiv2598329866 .yiv2598329866attach label a {text-decoration:none;}#yiv2598329866 blockquote {margin:0 0 0 4px;}#yiv2598329866 .yiv2598329866bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv2598329866 .yiv2598329866bold a {text-decoration:none;}#yiv2598329866 dd.yiv2598329866last p a {font-family:Verdana;font-weight:700;}#yiv2598329866 dd.yiv2598329866last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv2598329866 dd.yiv2598329866last p span.yiv2598329866yshortcuts {margin-right:0;}#yiv2598329866 div.yiv2598329866attach-table div div a {text-decoration:none;}#yiv2598329866 div.yiv2598329866attach-table {width:400px;}#yiv2598329866 div.yiv2598329866file-title a, #yiv2598329866 div.yiv2598329866file-title a:active, #yiv2598329866 div.yiv2598329866file-title a:hover, #yiv2598329866 div.yiv2598329866file-title a:visited {text-decoration:none;}#yiv2598329866 div.yiv2598329866photo-title a, #yiv2598329866 div.yiv2598329866photo-title a:active, #yiv2598329866 div.yiv2598329866photo-title a:hover, #yiv2598329866 div.yiv2598329866photo-title a:visited {text-decoration:none;}#yiv2598329866 div#yiv2598329866ygrp-mlmsg #yiv2598329866ygrp-msg p a span.yiv2598329866yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv2598329866 .yiv2598329866green {color:#628c2a;}#yiv2598329866 .yiv2598329866MsoNormal {margin:0 0 0 0;}#yiv2598329866 o {font-size:0;}#yiv2598329866 #yiv2598329866photos div {float:left;width:72px;}#yiv2598329866 #yiv2598329866photos div div {border:1px solid #66;min-height:62px;overflow:hidden;width:62px;}#yiv2598329866 #yiv2598329866photos div label {color:#66;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv2598329866 #yiv2598329866reco-category {font-size:77%;}#yiv2598329866 #yiv2598329866reco-desc {font-size:77%;}#yiv2598329866 .yiv2598329866replbq {margin:4px;}#yiv2598329866 #yiv2598329866ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv2598329866 #yiv2598329866ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv2598329866 #yiv2598329866ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv2598329866 #yiv2598329866ygrp-mlmsg select, #yiv2598329866 input, #yiv2598329866 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv2598329866 #yiv2598329866ygrp-mlmsg pre, #yiv2598329866 code {font:115% monospace;}#yiv2598329866 #yiv2598329866ygrp-mlmsg * {line-height:1.22em;}#yiv2598329866 #yiv2598329866ygrp-mlmsg #yiv2598329866logo {padding-bottom:10px;}#yiv2598329866 #yiv2598329866ygrp-msg p a {font-family:Verdana;}#yiv2598329866 #yiv2598329866ygrp-msg
Re: [firebird-support] Doubts regarding statistics of indexes and tables...
Hi, i do not suppose that this is possible and especially that you should do this. Better then look at queries and addapt it to real situation. Regards,Karol Bieniaszewski null
Re: [firebird-support] Doubts regarding statistics of indexes and tables...
Ok, but how do I restore the statistics? Using "update" statements? Thanks in advance for your help. On Saturday, May 12, 2018, 1:19:10 AM CDT, liviuslivius liviusliv...@poczta.onet.pl [firebird-support]wrote: Hi, to "backup" statistics you can run query on rdb$indices and rdb$index_segment tables. And save result to the file. About table statistics - here is really fine concept that you do not need to recalculate anything. Table pages count and record size is taken in calc. In fb3 also record compression level. Regards,Karol Bieniaszewski #yiv0641671250 #yiv0641671250 -- #yiv0641671250ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv0641671250 #yiv0641671250ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv0641671250 #yiv0641671250ygrp-mkp #yiv0641671250hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv0641671250 #yiv0641671250ygrp-mkp #yiv0641671250ads {margin-bottom:10px;}#yiv0641671250 #yiv0641671250ygrp-mkp .yiv0641671250ad {padding:0 0;}#yiv0641671250 #yiv0641671250ygrp-mkp .yiv0641671250ad p {margin:0;}#yiv0641671250 #yiv0641671250ygrp-mkp .yiv0641671250ad a {color:#ff;text-decoration:none;}#yiv0641671250 #yiv0641671250ygrp-sponsor #yiv0641671250ygrp-lc {font-family:Arial;}#yiv0641671250 #yiv0641671250ygrp-sponsor #yiv0641671250ygrp-lc #yiv0641671250hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv0641671250 #yiv0641671250ygrp-sponsor #yiv0641671250ygrp-lc .yiv0641671250ad {margin-bottom:10px;padding:0 0;}#yiv0641671250 #yiv0641671250actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv0641671250 #yiv0641671250activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv0641671250 #yiv0641671250activity span {font-weight:700;}#yiv0641671250 #yiv0641671250activity span:first-child {text-transform:uppercase;}#yiv0641671250 #yiv0641671250activity span a {color:#5085b6;text-decoration:none;}#yiv0641671250 #yiv0641671250activity span span {color:#ff7900;}#yiv0641671250 #yiv0641671250activity span .yiv0641671250underline {text-decoration:underline;}#yiv0641671250 .yiv0641671250attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv0641671250 .yiv0641671250attach div a {text-decoration:none;}#yiv0641671250 .yiv0641671250attach img {border:none;padding-right:5px;}#yiv0641671250 .yiv0641671250attach label {display:block;margin-bottom:5px;}#yiv0641671250 .yiv0641671250attach label a {text-decoration:none;}#yiv0641671250 blockquote {margin:0 0 0 4px;}#yiv0641671250 .yiv0641671250bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv0641671250 .yiv0641671250bold a {text-decoration:none;}#yiv0641671250 dd.yiv0641671250last p a {font-family:Verdana;font-weight:700;}#yiv0641671250 dd.yiv0641671250last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv0641671250 dd.yiv0641671250last p span.yiv0641671250yshortcuts {margin-right:0;}#yiv0641671250 div.yiv0641671250attach-table div div a {text-decoration:none;}#yiv0641671250 div.yiv0641671250attach-table {width:400px;}#yiv0641671250 div.yiv0641671250file-title a, #yiv0641671250 div.yiv0641671250file-title a:active, #yiv0641671250 div.yiv0641671250file-title a:hover, #yiv0641671250 div.yiv0641671250file-title a:visited {text-decoration:none;}#yiv0641671250 div.yiv0641671250photo-title a, #yiv0641671250 div.yiv0641671250photo-title a:active, #yiv0641671250 div.yiv0641671250photo-title a:hover, #yiv0641671250 div.yiv0641671250photo-title a:visited {text-decoration:none;}#yiv0641671250 div#yiv0641671250ygrp-mlmsg #yiv0641671250ygrp-msg p a span.yiv0641671250yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv0641671250 .yiv0641671250green {color:#628c2a;}#yiv0641671250 .yiv0641671250MsoNormal {margin:0 0 0 0;}#yiv0641671250 o {font-size:0;}#yiv0641671250 #yiv0641671250photos div {float:left;width:72px;}#yiv0641671250 #yiv0641671250photos div div {border:1px solid #66;min-height:62px;overflow:hidden;width:62px;}#yiv0641671250 #yiv0641671250photos div label {color:#66;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv0641671250 #yiv0641671250reco-category {font-size:77%;}#yiv0641671250 #yiv0641671250reco-desc {font-size:77%;}#yiv0641671250 .yiv0641671250replbq {margin:4px;}#yiv0641671250 #yiv0641671250ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv0641671250 #yiv0641671250ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv0641671250 #yiv0641671250ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv0641671250 #yiv0641671250ygrp-mlmsg select, #yiv0641671250 input, #yiv0641671250 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv0641671250 #yiv0641671250ygrp-mlmsg pre, #yiv0641671250 code {font:115% monospace;}#yiv0641671250
Re: [firebird-support] Doubts regarding statistics of indexes and tables...
Hi, to "backup" statistics you can run query on rdb$indices and rdb$index_segment tables. And save result to the file. About table statistics - here is really fine concept that you do not need to recalculate anything. Table pages count and record size is taken in calc. In fb3 also record compression level. Regards,Karol Bieniaszewski null
[firebird-support] Doubts regarding statistics of indexes and tables...
Firebird ver 2.5.7.27050 64 bitsSuperserverWindows Server 2012R2 In firebird, how do you calculate statistics to feed the query optimizer? Note: I know that the command: 'set statistics index ;' calculate statistics for indexes, but what about tables for example? And my second question: is there any way to backup the statistics before recalculate them? Note: This backup can be very useful if the new statistics create a performance problem Thanks in advance for your help