[firebird-support] Suboptimal plan
Hello, In a select statement there are two tables joined by a single matching field, with search conditions in both of them. One of them has roughly 13 records, while the other has 600. Inspecting the plan generated by the optimizer, I realized that it uses the shorter one as the controlling stream instead of the longer one (i.e. the shorter table is at the left side). Using a PLAN expression with the corresponding indexes to bypass the plan deviced by the optimizer in order to switch the order of the streams, the performance is boosted (nearly three times faster). What can cause the optimizer to pick a shorter table as the controlling stream ? Is there a workaround to induce the optimizer to select the streams in a fixed way or should I have to resort to a manually imposed PLAN ? Thanks, Aldo ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
RE: [firebird-support] Suboptimal plan
In a select statement there are two tables joined by a single matching field, with search conditions in both of them. One of them has roughly 13 records, while the other has 600. Inspecting the plan generated by the optimizer, I realized that it uses the shorter one as the controlling stream instead of the longer one (i.e. the shorter table is at the left side). Using a PLAN expression with the corresponding indexes to bypass the plan deviced by the optimizer in order to switch the order of the streams, the performance is boosted (nearly three times faster). What can cause the optimizer to pick a shorter table as the controlling stream ? Old/invalid index statistics. Is there a workaround to induce the optimizer to select the streams in a fixed way or should I have to resort to a manually imposed PLAN ? If you can provide the PLAN and SELECT (old and optimized) we can offer some suggestions. Sean
Re: [firebird-support] Suboptimal plan
Are the indexes statistics up to date? Also, post the query, query plan, and indexes stats. Carlos Firebird Performance in Detail - http://videos.firebirddevelopersday.com www.firebirdnews.org - www.FireBase.com.br ACacacfs Hello, ACacacfs In a select statement there are two tables joined by a single ACacacfs matching field, with search conditions in both of them. One of them has ACacacfs roughly 13 records, while the other has 600. ACacacfs Inspecting the plan generated by the optimizer, I realized that it ACacacfs uses the shorter one as the controlling stream instead of the longer one ACacacfs (i.e. the shorter table is at the left side). ACacacfs Using a PLAN expression with the corresponding indexes to bypass ACacacfs the plan deviced by the optimizer in order to switch the order of the ACacacfs streams, the performance is boosted (nearly three times faster). ACacacfs What can cause the optimizer to pick a shorter table as the ACacacfs controlling stream ? ACacacfs Is there a workaround to induce the optimizer to select the streams ACacacfs in a fixed way or should I have to resort to a manually imposed PLAN ? ACacacfs Thanks, ACacacfs Aldo
Re: [firebird-support] Suboptimal plan
Sean and Carlos, thanks for your answer. Although the statistics are up to date the optimizer still chooses the shorter stream as the controller. Nevertheless, I found a workaround using a subquery on the shorter table instead of a join. This forced the optimizer to traverse the longer table first and boosts the performance even more than using an optimized query with a fixed plan. Thank you very much for your help. Aldo El 01/08/14 a las 16:01, 'Carlos H. Cantu' lis...@warmboot.com.br [firebird-support] escibió: Are the indexes statistics up to date? Also, post the query, query plan, and indexes stats. Carlos Firebird Performance in Detail - http://videos.firebirddevelopersday.com www.firebirdnews.org - www.FireBase.com.br ACacacfs Hello, ACacacfs In a select statement there are two tables joined by a single ACacacfs matching field, with search conditions in both of them. One of them has ACacacfs roughly 13 records, while the other has 600. ACacacfs Inspecting the plan generated by the optimizer, I realized that it ACacacfs uses the shorter one as the controlling stream instead of the longer one ACacacfs (i.e. the shorter table is at the left side). ACacacfs Using a PLAN expression with the corresponding indexes to bypass ACacacfs the plan deviced by the optimizer in order to switch the order of the ACacacfs streams, the performance is boosted (nearly three times faster). ACacacfs What can cause the optimizer to pick a shorter table as the ACacacfs controlling stream ? ACacacfs Is there a workaround to induce the optimizer to select the streams ACacacfs in a fixed way or should I have to resort to a manually imposed PLAN ? ACacacfs Thanks, ACacacfs Aldo
Re: [firebird-support] Suboptimal plan
Hi ! There is some tricks to change the way optimizer will choose the order of tables: example: select * from LongTable L left join ShortTable S on (S.FieldA = L.FieldA) The outer join would force the table on the left to be scaned first. Another option is select * from LongTable L join ShortTable S on (S.FieldA = L.FieldA+0) the +0 (or || '' if the field is a char/varchar) would prevent the optimizer to use the index on LongTable.FieldA and could lead to change the join order see you ! Em 1/8/2014 18:09, Aldo Caruso aldo.car...@argencasas.com [firebird-support] escreveu: Sean and Carlos, thanks for your answer. Although the statistics are up to date the optimizer still chooses the shorter stream as the controller. Nevertheless, I found a workaround using a subquery on the shorter table instead of a join. This forced the optimizer to traverse the longer table first and boosts the performance even more than using an optimized query with a fixed plan. Thank you very much for your help. Aldo El 01/08/14 a las 16:01, 'Carlos H. Cantu' lis...@warmboot.com.br [firebird-support] escibió: Are the indexes statistics up to date? Also, post the query, query plan, and indexes stats. Carlos Firebird Performance in Detail - http://videos.firebirddevelopersday.com www.firebirdnews.org - www.FireBase.com.br ACacacfs Hello, ACacacfs In a select statement there are two tables joined by a single ACacacfs matching field, with search conditions in both of them. One of them has ACacacfs roughly 13 records, while the other has 600. ACacacfs Inspecting the plan generated by the optimizer, I realized that it ACacacfs uses the shorter one as the controlling stream instead of the longer one ACacacfs (i.e. the shorter table is at the left side). ACacacfs Using a PLAN expression with the corresponding indexes to bypass ACacacfs the plan deviced by the optimizer in order to switch the order of the ACacacfs streams, the performance is boosted (nearly three times faster). ACacacfs What can cause the optimizer to pick a shorter table as the ACacacfs controlling stream ? ACacacfs Is there a workaround to induce the optimizer to select the streams ACacacfs in a fixed way or should I have to resort to a manually imposed PLAN ? ACacacfs Thanks, ACacacfs Aldo
Re: [firebird-support] Suboptimal plan
Alexandre, thanks for your clues ! I certain circumstances the first one could change the result set, as it changes an inner join to an outer join. On the other hand, the second one is really good an prevents the optimizer to use the index on the long table, as you said. Aldo El 01/08/14 a las 18:29, Alexandre Benson Smith ibl...@thorsoftware.com.br [firebird-support] escibió: Hi ! There is some tricks to change the way optimizer will choose the order of tables: example: select * from LongTable L left join ShortTable S on (S.FieldA = L.FieldA) The outer join would force the table on the left to be scaned first. Another option is select * from LongTable L join ShortTable S on (S.FieldA = L.FieldA+0) the +0 (or || '' if the field is a char/varchar) would prevent the optimizer to use the index on LongTable.FieldA and could lead to change the join order see you ! Em 1/8/2014 18:09, Aldo Caruso aldo.car...@argencasas.com [firebird-support] escreveu: Sean and Carlos, thanks for your answer. Although the statistics are up to date the optimizer still chooses the shorter stream as the controller. Nevertheless, I found a workaround using a subquery on the shorter table instead of a join. This forced the optimizer to traverse the longer table first and boosts the performance even more than using an optimized query with a fixed plan. Thank you very much for your help. Aldo El 01/08/14 a las 16:01, 'Carlos H. Cantu' lis...@warmboot.com.br [firebird-support] escibió: Are the indexes statistics up to date? Also, post the query, query plan, and indexes stats. Carlos Firebird Performance in Detail - http://videos.firebirddevelopersday.com www.firebirdnews.org - www.FireBase.com.br ACacacfs Hello, ACacacfs In a select statement there are two tables joined by a single ACacacfs matching field, with search conditions in both of them. One of them has ACacacfs roughly 13 records, while the other has 600. ACacacfs Inspecting the plan generated by the optimizer, I realized that it ACacacfs uses the shorter one as the controlling stream instead of the longer one ACacacfs (i.e. the shorter table is at the left side). ACacacfs Using a PLAN expression with the corresponding indexes to bypass ACacacfs the plan deviced by the optimizer in order to switch the order of the ACacacfs streams, the performance is boosted (nearly three times faster). ACacacfs What can cause the optimizer to pick a shorter table as the ACacacfs controlling stream ? ACacacfs Is there a workaround to induce the optimizer to select the streams ACacacfs in a fixed way or should I have to resort to a manually imposed PLAN ? ACacacfs Thanks, ACacacfs Aldo
