Wrong plan is used ?!?

Dev Shed Forums Sponsor:

January 14th, 2011, 08:40 AM

astons2

Registered User

Join Date: Jan 2011

Posts: 2

Time spent in forums: 1 h 5 m 56 sec
Reputation Power: 0

Wrong plan is used ?!?

Recently I converted my database from interbase 2009 to firebird 2.5. Both databases are 100% at this moment identical. I noticed that few of my queries which I am using runing extremely slow on firebird. One of those queries on interbase is executed in less then 100ms and on firebird it needs more then 14sec, so I guess that firebird is calculating wrong plan.

Any idea? Thanks for help!

Code:

select mpvp_cene.*

from  mpvp_cene

left join new_view_get_zalihe on new_view_get_zalihe.artid = mpvp_cene.artid and
new_view_get_zalihe.magid = mpvp_cene.magid

where mpvp_cene.magid = 1

fast interbase plan

Code:

PLAN MERGE (SORT (MPVP_CENE INDEX (RDB$FOREIGN131)),SORT (SORT (JOIN (NEW_VIEW_GET_ZALIHE NEW_VIEW_SUM_ZALIHE PRIJEMNICE_H NATURAL,NEW_VIEW_GET_ZALIHE NEW_VIEW_SUM_ZALIHE PRIJEMNICE_D INDEX (RDB$FOREIGN77)))
PLAN SORT (JOIN (NEW_VIEW_GET_ZALIHE NEW_VIEW_SUM_ZALIHE PRIJEMNICE_H NATURAL,NEW_VIEW_GET_ZALIHE NEW_VIEW_SUM_ZALIHE INTERNI_PRENOS INDEX (RDB$PRIMARY10),NEW_VIEW_GET_ZALIHE NEW_VIEW_SUM_ZALIHE PRIJEMNICE_D INDEX (RDB$FOREIGN77)))
PLAN SORT (JOIN (NEW_VIEW_GET_ZALIHE NEW_VIEW_SUM_ZALIHE RACUNI_H NATURAL,NEW_VIEW_GET_ZALIHE NEW_VIEW_SUM_ZALIHE RACUNI_D INDEX (RDB$FOREIGN91)))
PLAN SORT (JOIN (NEW_VIEW_GET_ZALIHE NEW_VIEW_SUM_ZALIHE OTPIS_H NATURAL,NEW_VIEW_GET_ZALIHE NEW_VIEW_SUM_ZALIHE OTPIS_D INDEX (RDB$FOREIGN71)))
PLAN SORT (JOIN (NEW_VIEW_GET_ZALIHE NEW_VIEW_SUM_ZALIHE TREBOVANJE_H NATURAL,NEW_VIEW_GET_ZALIHE NEW_VIEW_SUM_ZALIHE TREBOVANJE_D INDEX (RDB$FOREIGN105)))))

slow firebird plan

Code:

PLAN JOIN (MPVP_CENE INDEX (FK_MPVP_CENE_MAGID)SORT (JOIN (NEW_VIEW_GET_ZALIHE NEW_VIEW_SUM_ZALIHE PRIJEMNICE_D INDEX (INTEG_394), NEW_VIEW_GET_ZALIHE NEW_VIEW_SUM_ZALIHE PRIJEMNICE_H INDEX (XPKPRIJEMNICE_H)))
PLAN SORT (JOIN (JOIN (NEW_VIEW_GET_ZALIHE NEW_VIEW_SUM_ZALIHE PRIJEMNICE_D INDEX (RDB$INTEG_394EW_VIEW_GET_ZALIHE NEW_VIEW_SUM_ZALIHE INTERNI_PRENOS INDEX (FK_INTERNI_PRENOS_MAGID)), NEW_VIEW_GET_ZALIHE NEW_VIEW_SUM_ZALIHE PRIJEMNICE_H INDEX (XPKPRIJEMNICE_H)))
PLAN SORT (JOIN (NEW_VIEW_GET_ZALIHE NEW_VIEW_SUM_ZALIHE RACUNI_D INDEX (RDB$FOREINTEG_425IEW_GET_ZALIHE NEW_VIEW_SUM_ZALIHE RACUNI_H INDEX (XPKRACUNI_H)))
PLAN SORT (JOIN (NEW_VIEW_GET_ZALIHE NEW_VIEW_SUM_ZALIHE OTPIS_H INDEX (RDB$FOREIGN4INTEG_392GET_ZALIHE NEW_VIEW_SUM_ZALIHE OTPIS_D INDEX (OTPIS_D_IDX1)))
PLAN SORT (JOIN (NEW_VIEW_GET_ZALIHE NEW_VIEW_SUM_ZALIHE TREBOVANJE_D INDEX (RDB$FOREIGN35), INTEG_387ZALIHE NEW_VIEW_SUM_ZALIHE TREBOVANJE_H INDEX (XPKTREBOVANJE_H))))

January 14th, 2011, 03:07 PM

clivew

Contributing User

Join Date: Jan 2006

Location: Carlsbad, CA

Posts: 2,045

Time spent in forums: 1 Week 6 Days 2 h 37 m
Reputation Power: 382

Are the statistics on your tables up to date?

Are you sure all the indexes are identical?

Is the data essentially identical also?

There may be some settings in the Firebird configuration that need tweaking;
but I am more of a SQL expert (I hope?) than a DBA.

January 17th, 2011, 06:47 AM

astons2

Registered User

Join Date: Jan 2011

Posts: 2

Time spent in forums: 1 h 5 m 56 sec
Reputation Power: 0

Thanks for help!

I extracted complete database with data into sql script, then I used that script to make database in firebird. So all database structure with data and indexes are 100% same, I never used statistic ....

Funny thing is when I change sql to this, query is fast:

Code:

select mpvp_cene.*

from  mpvp_cene

inner join new_view_get_zalihe on new_view_get_zalihe.artid = mpvp_cene.artid and
new_view_get_zalihe.magid = mpvp_cene.magid

where mpvp_cene.magid = 1

Please note that with sql with left join I get ~800rows and sql with inner join gives me ~500rows, so there is not much data in tables. Left join also should be executed in split second like inner join.

Any idea?

Wrong plan is used ?!?

Developer Shed Advertisers and Affiliates