JWorld@TW the best professional Java site in Taiwan
      註冊 | 登入 | 全文檢索 | 排行榜  

» JWorld@TW » JDBC/SQL討論區  

按列印兼容模式列印這個話題 列印話題    把這個話題寄給朋友 寄給朋友    訂閱主題
reply to topicthreaded modego to previous topicgo to next topic
本主題所含的標籤
作者 SQL增加一段語法就跑很久的問題
王進平





發文: 49
積分: 0
於 2017-07-02 08:56 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
SELECT
    COUNT(COUNT(*) ) count
FROM
    (        SELECT DISTINCT
            a1.area_no,
            a1.area_type,
            Nvl(l1.bank_no,' ') bank_no,
            l1.u_location_no u_location_no
        FROM
            dmarea a1,
            dmlocate l1
        WHERE
                a1.area_no = l1.area_no (+)
            AND
                a1.stock_manage_flag = '1'
            AND a1.area_type not in('1','8')
    UNION
       SELECT DISTINCT
            a1.area_no,
            a1.area_type,
            nvl(s1.bank_no,' ') bank_no,
            s1.u_location_no u_location_no
        FROM
            dmarea a1,
            dvwn7shelf s1
        WHERE
                a1.area_no = s1.area_no (+)
            AND
                a1.stock_manage_flag = '1'
            AND a1.area_type not in('1','8')
    ) l,
    (
        SELECT DISTINCT
            w1.area_no,
            nvl(
                w1.bank_no,
                ' '
            ) bank_no
        FROM
            dninventoryworkinfo w1
        WHERE
            consignor_code = '27355925A2'
    ) w,
    (
        SELECT
            MAX(w2.inventory_day) inventory_day,
            w2.area_no,
            w2.bank_no
        FROM
            dninventorydayhistory w2
        WHERE
            w2.consignor_code = '27355925A2'
        GROUP BY
            w2.area_no,
            w2.bank_no
    ) ww
WHERE
        w.area_no IS NULL
    AND
        l.area_no = w.area_no (+)
    AND
        l.bank_no = w.bank_no (+)
    AND
        l.area_no = ww.area_no (+)
    AND
        l.bank_no = ww.bank_no (+)
    AND
        EXISTS (
      select 1
      From
      (
        SELECT
          to_u_location_no,from_u_location_no
        FROM
          dnstockhistory shto
                where
                    TO_CHAR(
                        shto.process_date,
                        'yyyymmdd'
                    ) BETWEEN '20170601' AND '20170629'
        UNION
        SELECT
          to_u_location_no,from_u_location_no
        FROM
          dnoldstockhistory shto
                where
                    TO_CHAR(
                        shto.process_date,
                        'yyyymmdd'
                    ) BETWEEN '20170601' AND '20170629'
      )shto      
            WHERE
                    l.u_location_no = shto.from_u_location_no
                OR
                    l.u_location_no = shto.to_u_location_no
        )
GROUP BY
    l.area_no,
    l.area_type,
    l.bank_no
ORDER BY
    l.area_no,
    l.bank_no


以上是跑很久的語法
當中下面這段是我增加的語法 這裡資料大約22K筆左右
1
2
3
4
5
6
7
8
9
10
11
12
13
14
    UNION
       SELECT DISTINCT
            a1.area_no,
            a1.area_type,
            nvl(s1.bank_no,' ') bank_no,
            s1.u_location_no u_location_no
        FROM
            dmarea a1,
            dvwn7shelf s1
        WHERE
                a1.area_no = s1.area_no (+)
            AND
                a1.stock_manage_flag = '1'
            AND a1.area_type not in('1','8')


原本的這段 資料大約15K筆
1
2
3
4
5
6
7
8
9
10
11
12
13
 SELECT DISTINCT
            a1.area_no,
            a1.area_type,
            Nvl(l1.bank_no,' ') bank_no,
            l1.u_location_no u_location_no
        FROM
            dmarea a1,
            dmlocate l1
        WHERE
                a1.area_no = l1.area_no (+)
            AND
                a1.stock_manage_flag = '1'
            AND a1.area_type not in('1','8')


兩個查詢組合起來之後跑了20分鐘左右我實在受不了關掉

但原始語法卻執行5秒鐘而已...這麼大段落差需要往哪方向去查呢?


reply to postreply to post
作者 Re:SQL增加一段語法就跑很久的問題 [Re:王進平]
王進平





發文: 49
積分: 0
於 2017-07-02 14:33 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
補充
剛剛把下列語法刪掉
1
2
                OR
                    l.u_location_no = shto.to_u_location_no


就可以五秒內完成
但上面刪掉那行的位置我改成
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
            UNION
            SELECT
                1
            FROM
                (
                    SELECT
                        to_u_location_no,
                        from_u_location_no
                    FROM
                        dnstockhistory shto
                    WHERE
                        TO_CHAR(
                            shto.process_date,
                            'yyyymmdd'
                        ) BETWEEN '20170501' AND '20170529'
                    UNION
                    SELECT
                        to_u_location_no,
                        from_u_location_no
                    FROM
                        dnoldstockhistory shto
                    WHERE
                        TO_CHAR(
                            shto.process_date,
                            'yyyymmdd'
                        ) BETWEEN '20170501' AND '20170529'
                ) shto1
            WHERE
                    l.u_location_no = shto1.to_u_location_no


一樣跑到死掉...是我理解有問題嗎...


reply to postreply to post
» JWorld@TW »  JDBC/SQL討論區

reply to topicthreaded modego to previous topicgo to next topic
  已讀文章
  新的文章
  被刪除的文章
Jump to the top of page

JWorld@TW 本站商標資訊

Powered by Powerful JuteForum® Version Jute 1.5.8