Thank you
Sent from Yahoo Mail. Get the app
On Saturday, September 10, 2016 10:04 PM, "'Djordje Radovanovic'
[email protected] [firebird-support]" <[email protected]> wrote:
CREATE TABLE PARTNERS ( PARTID INTEGER NOT NULL, COUNTRY
CHAR(20));ALTER TABLE PARTNERS ADD CONSTRAINT PK_PARTNERS PRIMARY KEY
(PARTID);CREATE INDEX PARTNERS_IDX1 ON PARTNERS (COUNTRY); CREATE TABLE ORDERS
( ORDERNUM INTEGER NOT NULL, ORDERDATE DATE, AMOUNT
DECIMAL(18,2), PARTID INTEGER); ALTER TABLE ORDERS ADD CONSTRAINT
PK_ORDERS PRIMARY KEY (ORDERNUM);ALTER TABLE ORDERS ADD CONSTRAINT
FK_ORDERS_PARTNERS FOREIGN KEY (PARTID) REFERENCES PARTNERS (PARTID) ON DELETE
NO ACTION ON UPDATE NO ACTION; This is tables definitions. So, index exist and
after all perfomance analyzer gives me same result.Perfomance analyzer was
taken from IBExpert. Whatever I do (as others suggest) perfomance are the same.
I still vote for changes in optimizer. Right side from WHERE clause needs to be
evaluated before main query and it is simple and much better solution same as
Oracle optimize query. Firebird is very kind to programmer and has easy to
understand, efficient P/SQL and some improvements will give us significant
efficiency..Djordje From: mailto:[email protected] Sent: Friday,
September 09, 2016 10:51 PMTo: [email protected] Subject: Re:
[firebird-support] Optimizer request On Fri, Sep 9, 2016 at 8:30 AM, 'Djordje
Radovanovic' [email protected] [firebird-support]
<[email protected]> wrote:
I tried query with subquery in where clause and found big issue for this
type of subquery. select * from orders where orders.partid in (select
partners.partid from partners where partners.country = ‘Spain’) Perfomance
Analysis returns me this partners 687660 non index reads orders
28657 index reads If you analyze this result you’ll find that there is 687659
unnecessary non index reads. If developer of optimizer accept that all queries
on the left side of where clause has priority (and there is no way to be
opposite) than we have big improvement in optimization.
Is there an index on partners.country? What plans are generated for each
query? If I were writing this query, I'd write select o.* from orders o
inner join partners p where p.partid = o.opartid and
p.country = 'Spain'; All that silliness about "select from (select from )"
probably doesn't save anything inthis case - it does when the inner select is
complicated, but not here. Firebird won'tcarry around unneeded fields from the
partners table. What is the distribution of partners.country? Good luck, Ann
__,_._,_
#yiv1810376193 #yiv1810376193 -- #yiv1810376193ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1810376193
#yiv1810376193ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1810376193
#yiv1810376193ygrp-mkp #yiv1810376193hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv1810376193 #yiv1810376193ygrp-mkp #yiv1810376193ads
{margin-bottom:10px;}#yiv1810376193 #yiv1810376193ygrp-mkp .yiv1810376193ad
{padding:0 0;}#yiv1810376193 #yiv1810376193ygrp-mkp .yiv1810376193ad p
{margin:0;}#yiv1810376193 #yiv1810376193ygrp-mkp .yiv1810376193ad a
{color:#0000ff;text-decoration:none;}#yiv1810376193 #yiv1810376193ygrp-sponsor
#yiv1810376193ygrp-lc {font-family:Arial;}#yiv1810376193
#yiv1810376193ygrp-sponsor #yiv1810376193ygrp-lc #yiv1810376193hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1810376193
#yiv1810376193ygrp-sponsor #yiv1810376193ygrp-lc .yiv1810376193ad
{margin-bottom:10px;padding:0 0;}#yiv1810376193 #yiv1810376193actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1810376193
#yiv1810376193activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1810376193
#yiv1810376193activity span {font-weight:700;}#yiv1810376193
#yiv1810376193activity span:first-child
{text-transform:uppercase;}#yiv1810376193 #yiv1810376193activity span a
{color:#5085b6;text-decoration:none;}#yiv1810376193 #yiv1810376193activity span
span {color:#ff7900;}#yiv1810376193 #yiv1810376193activity span
.yiv1810376193underline {text-decoration:underline;}#yiv1810376193
.yiv1810376193attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv1810376193 .yiv1810376193attach div a
{text-decoration:none;}#yiv1810376193 .yiv1810376193attach img
{border:none;padding-right:5px;}#yiv1810376193 .yiv1810376193attach label
{display:block;margin-bottom:5px;}#yiv1810376193 .yiv1810376193attach label a
{text-decoration:none;}#yiv1810376193 blockquote {margin:0 0 0
4px;}#yiv1810376193 .yiv1810376193bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv1810376193
.yiv1810376193bold a {text-decoration:none;}#yiv1810376193 dd.yiv1810376193last
p a {font-family:Verdana;font-weight:700;}#yiv1810376193 dd.yiv1810376193last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1810376193
dd.yiv1810376193last p span.yiv1810376193yshortcuts
{margin-right:0;}#yiv1810376193 div.yiv1810376193attach-table div div a
{text-decoration:none;}#yiv1810376193 div.yiv1810376193attach-table
{width:400px;}#yiv1810376193 div.yiv1810376193file-title a, #yiv1810376193
div.yiv1810376193file-title a:active, #yiv1810376193
div.yiv1810376193file-title a:hover, #yiv1810376193 div.yiv1810376193file-title
a:visited {text-decoration:none;}#yiv1810376193 div.yiv1810376193photo-title a,
#yiv1810376193 div.yiv1810376193photo-title a:active, #yiv1810376193
div.yiv1810376193photo-title a:hover, #yiv1810376193
div.yiv1810376193photo-title a:visited {text-decoration:none;}#yiv1810376193
div#yiv1810376193ygrp-mlmsg #yiv1810376193ygrp-msg p a
span.yiv1810376193yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1810376193
.yiv1810376193green {color:#628c2a;}#yiv1810376193 .yiv1810376193MsoNormal
{margin:0 0 0 0;}#yiv1810376193 o {font-size:0;}#yiv1810376193
#yiv1810376193photos div {float:left;width:72px;}#yiv1810376193
#yiv1810376193photos div div {border:1px solid
#666666;min-height:62px;overflow:hidden;width:62px;}#yiv1810376193
#yiv1810376193photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1810376193
#yiv1810376193reco-category {font-size:77%;}#yiv1810376193
#yiv1810376193reco-desc {font-size:77%;}#yiv1810376193 .yiv1810376193replbq
{margin:4px;}#yiv1810376193 #yiv1810376193ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv1810376193 #yiv1810376193ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1810376193
#yiv1810376193ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1810376193
#yiv1810376193ygrp-mlmsg select, #yiv1810376193 input, #yiv1810376193 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv1810376193
#yiv1810376193ygrp-mlmsg pre, #yiv1810376193 code {font:115%
monospace;}#yiv1810376193 #yiv1810376193ygrp-mlmsg *
{line-height:1.22em;}#yiv1810376193 #yiv1810376193ygrp-mlmsg #yiv1810376193logo
{padding-bottom:10px;}#yiv1810376193 #yiv1810376193ygrp-msg p a
{font-family:Verdana;}#yiv1810376193 #yiv1810376193ygrp-msg
p#yiv1810376193attach-count span {color:#1E66AE;font-weight:700;}#yiv1810376193
#yiv1810376193ygrp-reco #yiv1810376193reco-head
{color:#ff7900;font-weight:700;}#yiv1810376193 #yiv1810376193ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv1810376193 #yiv1810376193ygrp-sponsor
#yiv1810376193ov li a {font-size:130%;text-decoration:none;}#yiv1810376193
#yiv1810376193ygrp-sponsor #yiv1810376193ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv1810376193
#yiv1810376193ygrp-sponsor #yiv1810376193ov ul {margin:0;padding:0 0 0
8px;}#yiv1810376193 #yiv1810376193ygrp-text
{font-family:Georgia;}#yiv1810376193 #yiv1810376193ygrp-text p {margin:0 0 1em
0;}#yiv1810376193 #yiv1810376193ygrp-text tt {font-size:120%;}#yiv1810376193
#yiv1810376193ygrp-vital ul li:last-child {border-right:none
!important;}#yiv1810376193
