Loading ...
Sorry, an error occurred while loading the content.

Re: Left Outer Join - Very Slow Query

Expand Messages
  • Svein Erling Tysv√¶r
    Hi Douglas! Assuming that VENDO.ID_PRODUTO is far more selective than VENDO.DATA when spanning half a year, the speed may increase if you change your query to
    Message 1 of 2 , Sep 1, 2004
    • 0 Attachment
      Hi Douglas!

      Assuming that VENDO.ID_PRODUTO is far more selective than VENDO.DATA
      when spanning half a year, the speed may increase if you change your
      query to

      SELECT
      P.ID_PRODUTO,
      P.DESCRICAO,
      COALESCE(SUM(V.QTDE),0) AS TOTAL
      FROM PRODUTO P LEFT OUTER JOIN VENDA V
      ON P.ID_PRODUTO=V.ID_PRODUTO
      AND (V.DATA BETWEEN '01/01/2003' AND '06/30/2003' OR 2=0)
      GROUP BY 1,2

      HTH,
      Set

      --- In firebird-support@yahoogroups.com, "Douglas Dallacqua" wrote:
      > SELECT
      > P.ID_PRODUTO,
      > P.DESCRICAO,
      > COALESCE(SUM(V.QTDE),0) AS TOTAL
      > FROM PRODUTO P LEFT OUTER JOIN VENDA V
      > ON P.ID_PRODUTO=V.ID_PRODUTO
      > AND V.DATA BETWEEN '01/01/2003' AND '06/30/2003'
      > GROUP BY 1,2
      >
      > PLAN SORT (JOIN (P NATURAL,V INDEX (VENDA_IDX2,VENDA_IDX1)))
      >
      > tables
      >
      > produto(id_produto (pk),descricao)
      > 7.500 rows
      > venda(data,id_produto,qtde,prcvenda) ==> venda_idx1- index on field
      data / venda_idx2 - index on field id_produto
      > 280.000 rows
      >
      > if i use inner join the query is vary fast.
      > but the same query using left join / postgreSQL run in 3s
      > while in Firebird 1.5.1 run in 15s
      >
      > help...
      >
      > Douglas Dallacqua
      > Assis,SP,Brazil
    Your message has been successfully submitted and would be delivered to recipients shortly.