How can I debug this sql query to avoid timeout?-CodePudding

I have an SQL query that actually doesn't do a lot despite giving me headaches. Honestly, I have no idea how to debug this query because whenever I run it, it's running for minutes until I finally forcely quit SQL developer. Even cancelling the query takes ages.

Any advice, help, highly appreciated!!

SELECT
    view2.some_id,
    SUM(view1.qty)
FROM
    someview view1,
    someview view2
WHERE
        view1.lot = view2.some_id
    AND view2.some_prefix = 'ABCD'
    AND view2.some_prefix = 'EFGH'
GROUP BY
    view2.some_id

I don't know why it's causing timeouts. This short statement (s2) is part of another statement:

CREATE OR REPLACE FORCE EDITIONABLE VIEW "VIEW" AS 
WITH s1 AS
        (SELECT something
           FROM sometable st
          WHERE st.side = 'TO'
          group by st.lot),
    s2 AS
        (SELECT
      view2.some_id,
      SUM(view1.qty)
  FROM
      someview view1,
      someview view2
  WHERE
          view1.lot = view2.some_id
      AND view2.some_prefix = 'ABCD'
      AND view2.some_prefix = 'EFGH'
  GROUP BY
      view2.some_id)
 SELECT 
        t2.some,
        t2.some,
        t2.some,
        s1.some,
        t2.some,
        t2.some,
        s2.some,
        s2.some / s1.some * t2.some
   FROM
        sometable t2, s1, s2
        WHERE t2.some = s1.some
        AND t2.some = s2.some
        AND t2.some = 'FROM' AND t2.some = 'VN1' AND t2.some_prefix = 'ABCD';

Just give me any hint please.

Update: I checked the amount of rows per table:

sometable st (s1): 2.805.809
view2 (s2): 21.877
view1 (s2): 6.144.386
t2: 121.043

Doesn't seem so much....

CodePudding user response：

i'd start with taking out the implicit join (which should not be used) in s2

CodePudding user response：

The syntax is outdated as all mentioned here. I knew but it's what I find in my company's code. However, following several advices here, I deconstructed the statement and rebuilt it using modern syntax, using explicit joins.

While doing this I figured out that the "group by" clause in block s2 query (first code block above) was missing the second argument.

So, when changing to (adding "view1.qty"):

GROUP BY
view2.some_id, view1.qty

The statement finally worked and did not even take that long. That's what I was expecting. And, I re-checked with my explicit version, got the same data, counter-checking it. Thanks for everybody posting, it helped me to find the answer!

And this is my version of it:

SELECT
  some,
  some,
  some,
  some,
  some,
  some,
  some,
  some / some * some
FROM
  (
    SELECT
      *
    FROM
      (
        SELECT
          a.somelot,
          b.someqty vq
        FROM
          (
            (
              SELECT
                view2.some_id somelot,
                view2.someother_id --added this
              FROM
                someview view2
              WHERE
                view2.some_prefix = 'ABCD'
                AND view2.some_prefix = 'EFGH'
            ) a
            JOIN (
              SELECT
                view1.lot,
                SUM(view1.qty) qty
              FROM
                someview view1
              GROUP BY
                lot,
                qty
            ) b ON a.someother_id = b.lot
          )
      ) s2
      JOIN (
        SELECT
          lot,
          SUM(trans_qty) pq
        FROM
          someview
        WHERE
          side = 'TO'
        GROUP BY
          lot,
          trans_qty
      ) s1 ON s1.lot = s2.lot
      JOIN (
        SELECT
          lot,
          some,
          some,
          trans_qty trans_qty,
          some
        FROM
          someview
        WHERE
          some = 'ZZZZ'
          AND some = 'XXX'
          AND some_prefix = 'WXYZ'
      ) t2 ON t2.lot = s1.lot
      AND t2.lot = s2.lot
  )