SQL语句效率很慢，请朋友们帮忙下

[iceagebirdxq]

select * from (
 SELECT ROW_NUMBER() OVER(ORDER BY AGT_CODE DESC) AS REC_NUM,* FROM (
          SELECT INT_ORG, AGT_CODE, AGT_STATUS, '1' AS CLS 
          FROM AGENT WHERE 
          AGT_CODE NOT IN (SELECT  OPEN_AGENT FROM CUSTOMER) 
          AND AGT_CODE NOT IN (SELECT   USER_CODE FROM AGENT_FUNDBIZ) 
          AND AGT_STATUS = '0' 
         UNION ALL 
          SELECT  A.INT_ORG, A.AGT_CODE, A.AGT_STATUS, '2' AS CLS 
          FROM AGENT A, AGENT_RELATION C WHERE 
          A.AGT_CODE = C.USER_CODE 
          AND A.AGT_CODE NOT IN (SELECT  OPEN_AGENT FROM CUSTOMER) 
          AND A.AGT_STATUS = '9' ) D
          ) E WHERE E.REC_NUM BETWEEN 1 AND 900

我测试了下，主要是加了 WHERE E.REC_NUM BETWEEN 1 AND 900 后就变的很慢，如果不加的话是没有什么影响的。

[icebbb34]

查看执行计划 调整索引

[筱筱澄]

row_number 分页的问题，刚出这玩意的时候，真火啊
都说替代了top，用了会发现它比top也没好多少，特别是 desc的时候，有时候会很慢
还有大家通常都用错了

[Felixzhaowenzhong]

SELECT *
FROM   (SELECT Row_number()
                 OVER(
                   ORDER BY AGT_CODE DESC) AS REC_NUM,
               *
        FROM   (SELECT INT_ORG,
                       AGT_CODE,
                       AGT_STATUS,
                       '1' AS CLS
                FROM   AGENT B
                WHERE  NOT EXISTS (SELECT OPEN_AGENT
                                        FROM   CUSTOMER WHERE OPEN_AGENT=B.AGT_CODE)
                       AND  NOT EXISTS (SELECT USER_CODE
                                            FROM   AGENT_FUNDBIZ WHERE USER_CODE=B.AGT_CODE)
                       AND AGT_STATUS = '0'
                UNION ALL
                SELECT A.INT_ORG,
                       A.AGT_CODE,
                       A.AGT_STATUS,
                       '2' AS CLS
                FROM   AGENT A,
                       AGENT_RELATION C
                WHERE  A.AGT_CODE = C.USER_CODE
                       AND  NOT EXISTS (SELECT OPEN_AGENT
                                              FROM   CUSTOMER WHERE OPEN_AGENT=A.AGT_CODE)
                       AND A.AGT_STATUS = '9') D) E
WHERE  E.REC_NUM>= 1 AND E.REC_NUM<=900
--- WHERE E.REC_NUM BETWEEN 1 AND 900  查看执行计划其实优化器最后实际将其转换为了 >= and <= 这种方式。
--NOT IN  不会执行索引查找 而 NOT EXISTS 是会执行索引查找 操作的

[ORAClE SE]

试试这样如何？

WITH t AS (SELECT a.Int_Org, a.Agt_Code, a.Agt_Status, '1' AS Cls
FROM AGENT a
WHERE NOT EXISTS (SELECT 1
FROM Customer b
WHERE b.Open_Agent = a.Agt_Code)
AND NOT EXISTS (SELECT 1
FROM Agent_Fundbiz c
WHERE c.User_Code = a.Agt_Code)
AND a.Agt_Status = '0'
UNION ALL
SELECT a.Int_Org, a.Agt_Code, a.Agt_Status, '2' AS Cls
FROM AGENT a, Agent_Relation c
WHERE a.Agt_Code = c.User_Code
AND NOT EXISTS (SELECT 1
FROM Customer c
WHERE c.Open_Agent = a.Agt_Code)
AND a.Agt_Status = '9')
SELECT *
FROM (SELECT Row_Number() Over(ORDER BY Agt_Code DESC) AS Rec_Num,*
FROM t d) e
WHERE e.Rec_Num >= 1
AND e.Rec_Num < 901

[筱筱澄]

分页

[nzperfect]

看到这个UNION ALL，基本上很有难度了，单纯看SQL优化不了

[Mr_Nice]

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY AGT_CODE DESC ) AS REC_NUM ,
                    *
          FROM      ( SELECT    INT_ORG ,
                                AGT_CODE ,
                                AGT_STATUS ,
                                '1' AS CLS
                      FROM      AGENT
                      WHERE     NOT EXISTS ( SELECT 1
                                             FROM   CUSTOMER
                                             WHERE  agent.AGT_CODE = OPEN_AGENT )
                                AND NOT EXISTS ( SELECT 1
                                                 FROM   AGENT_FUNDBIZ
                                                 WHERE  agent.AGT_CODE = USER_CODE )
                                AND AGT_STATUS = '0'
                      UNION ALL
                      SELECT    A.INT_ORG ,
                                A.AGT_CODE ,
                                A.AGT_STATUS ,
                                '2' AS CLS
                      FROM      AGENT A ,
                                AGENT_RELATION C
                      WHERE     A.AGT_CODE = C.USER_CODE
                                AND NOT EXISTS ( SELECT 1
                                                 FROM   CUSTOMER
                                                 WHERE  agent.AGT_CODE = OPEN_AGENT )
                                AND A.AGT_STATUS = '9'
                    ) D
        ) E
WHERE   E.REC_NUM BETWEEN 1 AND 900

[iceagebirdxq]

非常感谢大家的热心，我仔细分析下各位的回帖

[icebbb34]

      select IDENTITY(int, 1,1) AS REC_NUM,
					   *
		        into   #temp
		from(
                SELECT INT_ORG,
                       AGT_CODE,
                       AGT_STATUS,
                       '1' AS CLS
                FROM   AGENT B
                WHERE  NOT EXISTS (SELECT OPEN_AGENT
                                        FROM   CUSTOMER WHERE OPEN_AGENT=B.AGT_CODE)
                       AND  NOT EXISTS (SELECT USER_CODE
                                            FROM   AGENT_FUNDBIZ WHERE USER_CODE=B.AGT_CODE)
                       AND AGT_STATUS = '0'
                UNION ALL
                SELECT A.INT_ORG,
                       A.AGT_CODE,
                       A.AGT_STATUS,
                       '2' AS CLS
                FROM   AGENT A,
                       AGENT_RELATION C
                WHERE  A.AGT_CODE = C.USER_CODE
                       AND  NOT EXISTS (SELECT OPEN_AGENT
                                              FROM   CUSTOMER WHERE OPEN_AGENT=A.AGT_CODE)
                       AND A.AGT_STATUS = '9') D
           ) as E 
           order by E.AGT_CODE
           
           
           select * from #temp where REC_NUM>= 1 AND REC_NUM<=900