#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    2
    Rep 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))))
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    2
    Rep 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?

IMN logo majestic logo threadwatch logo seochat tools logo