| Subject | RE: [firebird-support] More FB 2.0 / FB1.5 optimizer differences |
|---|---|
| Author | Rick Debay |
| Post date | 2006-12-04T21:50:44Z |
What plans are generated by each databases optimizer?
-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Hans
Sent: Monday, December 04, 2006 4:19 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] More FB 2.0 / FB1.5 optimizer differences
What I'm I doing wong ?
Any ideas ?
Tried doubling buffer sizes in FB2.0 .. but
Best Regard
Hans
------------------------------------------------------------------------
Same Server, Same data
FB1.5: runtime 29 Secs ODS 10.1
FB2.0: runtime 240 Secs ODS 11.0
------------------------------------------------------------------------
select distinct o.mud_name oil_co,
s.lsd,
s.date_started,
m.mud_name
from sites s
join tickets t on (t.soldtosite_uniquenum = s.site_uniquenum or
t.shiptosite_uniquenum = s.site_uniquenum ) and t.ref_ticket_uniquenum
is null
join mudcompanies o on o.mud_uniquenum = s.cust_uniquenum
join warehouses w on w.house_uniquenum = s.house_uniquenum and
w.training <> 'T'
join mudcompanies m on m.mud_uniquenum = t.mud_uniquenum
where s.wellcompleted <> 'T' and current_date - s.date_started <= 120
order by o.mud_name, s.lsd, m.mud_name
------------------------------------------------------------------------
knowing that there aslway is a value preset for two of the joins,
changing them to left outer joins
FB1.5: runtime 3 Secs
FB2.0: runtime 29 Secs
------------------------------------------------------------------------
select distinct o.mud_name oil_co,
s.lsd,
s.date_started,
m.mud_name
from sites s
join tickets t on (t.soldtosite_uniquenum = s.site_uniquenum or
t.shiptosite_uniquenum = s.site_uniquenum ) and t.ref_ticket_uniquenum
is null
left outer
join mudcompanies o on o.mud_uniquenum = s.cust_uniquenum
join warehouses w on w.house_uniquenum = s.house_uniquenum and
w.training <> 'T'
left outer
join mudcompanies m on m.mud_uniquenum = t.mud_uniquenum
where s.wellcompleted <> 'T' and current_date - s.date_started <= 120
order by o.mud_name, s.lsd, m.mud_name
------------------------------------------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Resources item on the
main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links
Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.
-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Hans
Sent: Monday, December 04, 2006 4:19 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] More FB 2.0 / FB1.5 optimizer differences
What I'm I doing wong ?
Any ideas ?
Tried doubling buffer sizes in FB2.0 .. but
Best Regard
Hans
------------------------------------------------------------------------
Same Server, Same data
FB1.5: runtime 29 Secs ODS 10.1
FB2.0: runtime 240 Secs ODS 11.0
------------------------------------------------------------------------
select distinct o.mud_name oil_co,
s.lsd,
s.date_started,
m.mud_name
from sites s
join tickets t on (t.soldtosite_uniquenum = s.site_uniquenum or
t.shiptosite_uniquenum = s.site_uniquenum ) and t.ref_ticket_uniquenum
is null
join mudcompanies o on o.mud_uniquenum = s.cust_uniquenum
join warehouses w on w.house_uniquenum = s.house_uniquenum and
w.training <> 'T'
join mudcompanies m on m.mud_uniquenum = t.mud_uniquenum
where s.wellcompleted <> 'T' and current_date - s.date_started <= 120
order by o.mud_name, s.lsd, m.mud_name
------------------------------------------------------------------------
knowing that there aslway is a value preset for two of the joins,
changing them to left outer joins
FB1.5: runtime 3 Secs
FB2.0: runtime 29 Secs
------------------------------------------------------------------------
select distinct o.mud_name oil_co,
s.lsd,
s.date_started,
m.mud_name
from sites s
join tickets t on (t.soldtosite_uniquenum = s.site_uniquenum or
t.shiptosite_uniquenum = s.site_uniquenum ) and t.ref_ticket_uniquenum
is null
left outer
join mudcompanies o on o.mud_uniquenum = s.cust_uniquenum
join warehouses w on w.house_uniquenum = s.house_uniquenum and
w.training <> 'T'
left outer
join mudcompanies m on m.mud_uniquenum = t.mud_uniquenum
where s.wellcompleted <> 'T' and current_date - s.date_started <= 120
order by o.mud_name, s.lsd, m.mud_name
------------------------------------------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Resources item on the
main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links
Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.