Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2013
    Posts
    1

    Unanswered: Need help with a query

    Hi, I am new here.

    I need help with a query which takes me > 15 minutes to load and I never get an result as I frustrated and kill the process.

    The attachment is the "EXPLAIN" result of the query, and the following is the query itself.

    Code:
    EXPLAIN SELECT 
      *
    FROM
      stock stk
      LEFT JOIN 
        (SELECT 
          * 
        FROM
          (SELECT 
            t_stkcode,
            t_store,
            m_date,
            t_totqty inqty,
            0 outqty 
          FROM
            grdt,
            grmt 
          WHERE m_docno = t_docno 
            AND m_cancel = 'N' 
          UNION
          ALL 
          SELECT 
            t_stkcode,
            t_store,
            m_date,
            t_totqty inqty,
            0 outqty 
          FROM
            srdt,
            srmt 
          WHERE m_docno = t_docno 
            AND m_cancel = 'N' 
          UNION
          ALL 
          SELECT 
            t_stkcode,
            t_store,
            m_date,
            t_totqty inqty,
            0 outqty 
          FROM
            adjindt,
            adjinmt 
          WHERE m_docno = t_docno 
            AND m_cancel = 'N' 
          UNION
          ALL 
          SELECT 
            t_stkcode,
            t_store,
            m_date,
            t_totqty inqty,
            0 outqty 
          FROM
            stkindt,
            stkinmt 
          WHERE m_docno = t_docno 
            AND m_cancel = 'N' 
          UNION
          ALL 
          SELECT 
            t_stkcode,
            m_storeto,
            m_date,
            t_totqty inqty,
            0 outqty 
          FROM
            strfdt,
            strfmt 
          WHERE m_docno = t_docno 
            AND m_cancel = 'N' 
          UNION
          ALL 
          SELECT 
            t_stkcode,
            t_store,
            m_date,
            0 inqty,
            t_totqty outqty 
          FROM
            cbdt,
            cbmt 
          WHERE m_docno = t_docno 
            AND m_cancel = 'N' 
          UNION
          ALL 
          SELECT 
            t_stkcode,
            t_store,
            m_date,
            0 inqty,
            t_totqty outqty 
          FROM
            invdt,
            invmt 
          WHERE m_docno = t_docno 
            AND m_cancel = 'N' 
          UNION
          ALL 
          SELECT 
            t_stkcode,
            t_store,
            m_date,
            0 inqty,
            t_totqty outqty 
          FROM
            adjoutdt,
            adjoutmt 
          WHERE m_docno = t_docno 
            AND m_cancel = 'N' 
          UNION
          ALL 
          SELECT 
            t_stkcode,
            t_store,
            m_date,
            0 inqty,
            t_totqty outqty 
          FROM
            stkoutdt,
            stkoutmt 
          WHERE m_docno = t_docno 
            AND m_cancel = 'N' 
          UNION
          ALL 
          SELECT 
            t_stkcode,
            t_store,
            m_date,
            0 inqty,
            t_totqty outqty 
          FROM
            prdt,
            prmt 
          WHERE m_docno = t_docno 
            AND m_cancel = 'N' 
          UNION
          ALL 
          SELECT 
            t_stkcode,
            t_store,
            m_date,
            0 inqty,
            t_totqty outqty 
          FROM
            dodt,
            domt 
          WHERE m_docno = t_docno 
            AND m_cancel = 'N' 
            AND m_billno = '' 
          UNION
          ALL 
          SELECT 
            t_stkcode,
            m_storefr,
            m_date,
            0 inqty,
            t_totqty outqty 
          FROM
            strfdt,
            strfmt 
          WHERE m_docno = t_docno 
            AND m_cancel = 'N') table1 
        WHERE m_date <= '2013-09-09') table1 
        ON stk.stk_code = table1.t_stkcode 
    WHERE 1 = 1 ;


    Thanks,
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2007
    Location
    Bangalore India
    Posts
    28
    Hi,

    first you need to follow QEP

    1) EXPLAN your query, which will tell you optimizer the data.
    2) Check your query you are addressing valid index, if not index it.
    3) "Use index" will select which index you want to run the query.
    4) use where clause and dont use where 1=1 because its always true.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •