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

    Join Date
    Jul 2010
    Posts
    7
    Rep Power
    0

    Bad Optmizer Plan (firebird 2.1)


    I have been going nuts trying to figure this out...Ill just write the queries and results Im getting. All tables on JOINS are referencing their PKs and FKS (properly created/indexed) :

    tables overview :

    HD_OCORRENCIAS - 500.000 rows (calls/incidents table)
    HD_STATUS - 10 rows (calls status table)
    ORDEM_SERVICO_DIVISAO - 20 rows (calls support areas table)
    HD_PROBLEMAS - 250 rows (types of calls table)
    CLIENTES - 200.000 rows (clients table)


    very slow query (the one I need to run on the system) :

    SELECT
    HD_OCORRENCIAS.NUMERO
    FROM HD_OCORRENCIAS
    INNER JOIN HD_STATUS ON HD_OCORRENCIAS.STATUS = HD_STATUS.STAT_ID
    INNER JOIN ORDEM_SERVICO_DIVISAO ON HD_OCORRENCIAS.SISTEMA = ORDEM_SERVICO_DIVISAO.IDDIVISAO
    INNER JOIN HD_PROBLEMAS ON HD_OCORRENCIAS.PROBLEMA = HD_PROBLEMAS.PROB_ID
    INNER JOIN CLIENTES ON HD_OCORRENCIAS.IDCLIENTE = CLIENTES.IDCLIENTE

    WHERE (HD_STATUS.STAT_PAINEL = 2)

    plan used :

    PLAN JOIN (CLIENTES NATURAL, HD_OCORRENCIAS INDEX (FK_HD_OCORRENCIAS_CLIENTE), HD_STATUS INDEX (PK_HD_STATUS), ORDEM_SERVICO_DIVISAO INDEX (PK_ORDEM_SERVICO_DIVISAO), HD_PROBLEMAS INDEX (PK_HD_PROBLEMAS))

    executed 7.000ms (full table scan on clientes)


    now, if I remove the clientes join... :

    SELECT
    HD_OCORRENCIAS.NUMERO
    FROM HD_OCORRENCIAS
    INNER JOIN HD_STATUS ON HD_OCORRENCIAS.STATUS = HD_STATUS.STAT_ID
    INNER JOIN ORDEM_SERVICO_DIVISAO ON HD_OCORRENCIAS.SISTEMA = ORDEM_SERVICO_DIVISAO.IDDIVISAO
    INNER JOIN HD_PROBLEMAS ON HD_OCORRENCIAS.PROBLEMA = HD_PROBLEMAS.PROB_ID
    --INNER JOIN CLIENTES ON HD_OCORRENCIAS.IDCLIENTE = CLIENTES.IDCLIENTE

    WHERE (HD_STATUS.STAT_PAINEL = 2)

    plan used :

    PLAN JOIN (HD_STATUS INDEX (HD_STATUS_IDX1), HD_OCORRENCIAS INDEX (FK_HD_OCORRENCIAS_STAT), ORDEM_SERVICO_DIVISAO INDEX (PK_ORDEM_SERVICO_DIVISAO), HD_PROBLEMAS INDEX (PK_HD_PROBLEMAS))

    very fast, almost instantly.


    now...ill keep the clientes table on the query. and I will remove ORDEM_SERVICO_DIVISAO and HD_PROBLEMAS :

    query :

    SELECT
    HD_OCORRENCIAS.NUMERO
    FROM HD_OCORRENCIAS
    INNER JOIN HD_STATUS ON HD_OCORRENCIAS.STATUS = HD_STATUS.STAT_ID
    --INNER JOIN ORDEM_SERVICO_DIVISAO ON HD_OCORRENCIAS.SISTEMA = ORDEM_SERVICO_DIVISAO.IDDIVISAO
    --INNER JOIN HD_PROBLEMAS ON HD_OCORRENCIAS.PROBLEMA = HD_PROBLEMAS.PROB_ID
    INNER JOIN CLIENTES ON HD_OCORRENCIAS.IDCLIENTE = CLIENTES.IDCLIENTE

    WHERE (HD_STATUS.STAT_PAINEL = 2)

    plan used :

    PLAN JOIN (HD_STATUS INDEX (HD_STATUS_IDX1), HD_OCORRENCIAS INDEX (FK_HD_OCORRENCIAS_STAT), CLIENTES INDEX (PK_CLIENTES))


    very fast again.



    I havent got a clue to whats happening here. Why the optmizer is going for CLIENTES NATURAL, and why if I remove some of the joins is starts using the regular FK index??
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Posts
    7
    Rep Power
    0
    PS, of course I have to include fields in the select area to fetch data from those joined tables, I just excluded them to shorten the queries
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    Try to do
    SET STATISTICS INDEX index_name
    for all indexes for the tables in the query and run the original query again.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Posts
    7
    Rep Power
    0
    Originally Posted by mIRCata
    Try to do
    SET STATISTICS INDEX index_name
    for all indexes for the tables in the query and run the original query again.
    Already done, no improvement.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    Originally Posted by Rudi BR
    Already done, no improvement.
    If you use this
    SELECT
    HD_OCORRENCIAS.NUMERO
    FROM HD_OCORRENCIAS
    INNER JOIN HD_STATUS ON HD_OCORRENCIAS.STATUS = HD_STATUS.STAT_ID
    INNER JOIN ORDEM_SERVICO_DIVISAO ON HD_OCORRENCIAS.SISTEMA = ORDEM_SERVICO_DIVISAO.IDDIVISAO
    INNER JOIN HD_PROBLEMAS ON HD_OCORRENCIAS.PROBLEMA = HD_PROBLEMAS.PROB_ID
    INNER JOIN CLIENTES ON HD_OCORRENCIAS.IDCLIENTE = CLIENTES.IDCLIENTE

    WHERE (HD_STATUS.STAT_PAINEL = 2)

    PLAN JOIN (HD_STATUS INDEX (HD_STATUS_IDX1), HD_OCORRENCIAS INDEX (FK_HD_OCORRENCIAS_STAT), ORDEM_SERVICO_DIVISAO INDEX (PK_ORDEM_SERVICO_DIVISAO), HD_PROBLEMAS INDEX (PK_HD_PROBLEMAS), CLIENTS INDEX(PK_STATUS)/*if the primary key is PK_STATUS, or you can use the index that is for the foreign key in HD_OCORRENCIAS */)

    is it faster?
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Posts
    7
    Rep Power
    0
    Originally Posted by mIRCata
    If you use this...
    SELECT
    HD_OCORRENCIAS.NUMERO
    FROM HD_OCORRENCIAS
    INNER JOIN HD_STATUS ON HD_OCORRENCIAS.STATUS = HD_STATUS.STAT_ID
    INNER JOIN ORDEM_SERVICO_DIVISAO ON HD_OCORRENCIAS.SISTEMA = ORDEM_SERVICO_DIVISAO.IDDIVISAO
    INNER JOIN HD_PROBLEMAS ON HD_OCORRENCIAS.PROBLEMA = HD_PROBLEMAS.PROB_ID
    INNER JOIN CLIENTES ON HD_OCORRENCIAS.IDCLIENTE = CLIENTES.IDCLIENTE

    WHERE (HD_STATUS.STAT_PAINEL = 2)

    PLAN JOIN (
    HD_STATUS INDEX (HD_STATUS_IDX1),
    HD_OCORRENCIAS INDEX (FK_HD_OCORRENCIAS_STAT),
    ORDEM_SERVICO_DIVISAO INDEX (PK_ORDEM_SERVICO_DIVISAO),
    HD_PROBLEMAS INDEX (PK_HD_PROBLEMAS),
    CLIENTES INDEX(PK_CLIENTES))


    That did the trick mIRCata, it ran as fast as expected.

    The problem is I cannot use custom plans in the system. Any idea if there is any workaround to get the optimizer to use this proper plan?

IMN logo majestic logo threadwatch logo seochat tools logo