数据量太大，查询很慢，查询语句优化问题

qq_24661363 2017-11-20 03:43:03

我们公司是做加工生产的，数据大概是几十万，数据库里加了索引，也写了存储过程，存储过程里还有视图，以前速度还可以的，后来就不行了。
跟踪发现视图的一段语句，执行完要7秒。。。代码如下：

SELECT A.STOCK_CLASS, A.FACTORY_CD, A.STOCK_FACTORY, A.WC_CD, A.JOB_INST_NO, A.MATE_CD, A.PALLET_NO, A.LOC_CD
FROM DBO.TB_MM_MATE_STOCK A WITH(NOLOCK)
INNER JOIN (
SELECT ID = MAX(A.ID), A.PALLET_NO
FROM DBO.TB_MM_MATE_STOCK A WITH(NOLOCK)
INNER JOIN (
SELECT C_V, KOR
FROM DBO.TB_SM_CODE_DETAIL WITH(NOLOCK)
WHERE CD_CAT = 'IN_CLASS'
AND C_V NOT IN ('IP', 'MI', 'MC', 'MM', 'MZ')
) B ON A.STOCK_CLASS = B.C_V
GROUP BY A.PALLET_NO
) B ON A.ID = B.ID

实在不知道该怎么改，肯定有人说不要用not in 这个没关系的，查询速度直接秒的。请教个我大神~~大牛~~

...全文

972 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

xiaoxiangqing 2017-11-21

打赏
举报

这个要看下执行计划

RINK_1 2017-11-20

打赏
举报

这样试试呢。


select A.STOCK_CLASS, 
       A.FACTORY_CD, 
       A.STOCK_FACTORY, 
       A.WC_CD, 
       A.JOB_INST_NO, 
       A.MATE_CD, 
       A.PALLET_NO, 
       A.LOC_CD
FROM DBO.TB_MM_MATE_STOCK A WITH(NOLOCK)
INNER JOIN 
          (
           SELECT C_V, 
                  KOR,
           FROM DBO.TB_SM_CODE_DETAIL WITH(NOLOCK)
           WHERE CD_CAT = 'IN_CLASS'
           AND C_V NOT IN ('IP', 'MI', 'MC', 'MM', 'MZ')
           ) AS B ON A.STOCK_CLASS = B.C_V
 WHERE NOT EXISTS (SELECT 1 FROM TB_MM_MATE_STOCK WHERE A.PALLET_NO=PALLET_NO AND ID>A.ID)

吉普赛的歌 2017-11-20

打赏
举报

引用 2 楼 qq_24661363 的回复:

报错的，group by 那里。

忘记了 max , 下面这个呢？

SELECT A.STOCK_CLASS,
       A.FACTORY_CD,
       A.STOCK_FACTORY,
       A.WC_CD,
       A.JOB_INST_NO,
       A.MATE_CD,
       A.PALLET_NO,
       A.LOC_CD
FROM   DBO.TB_MM_MATE_STOCK A WITH (NOLOCK) WHERE A.ID IN
(
    SELECT MAX(A.ID)
    FROM   DBO.TB_MM_MATE_STOCK AS A WITH (NOLOCK) 
           INNER JOIN DBO.TB_SM_CODE_DETAIL AS B WITH (NOLOCK) 
           ON  A.STOCK_CLASS = B.C_V 
                AND B.CD_CAT = 'IN_CLASS'
                AND B.C_V NOT IN ('IP', 'MI', 'MC', 'MM', 'MZ')
    GROUP BY A.PALLET_NO
)

听雨停了 2017-11-20

打赏
举报

引用 4 楼 qq_24661363 的回复:

[quote=引用 3 楼 qq_37170555 的回复:]


SELECT A.STOCK_CLASS,
       A.FACTORY_CD,
       A.STOCK_FACTORY,
       A.WC_CD,
       A.JOB_INST_NO,
       A.MATE_CD,
       A.PALLET_NO,
       A.LOC_CD
FROM   DBO.TB_MM_MATE_STOCK A WITH (NOLOCK)
INNER JOIN ( 
	--1.单独执行下面这句sql看需要多久           
	SELECT MAX(A.ID) AS id,A.PALLET_NO
	FROM   DBO.TB_MM_MATE_STOCK A WITH (NOLOCK)
	INNER JOIN DBO.TB_SM_CODE_DETAIL b WITH (NOLOCK) ON A.STOCK_CLASS = B.C_V
	WHERE  a.CD_CAT = 'IN_CLASS' AND a.C_V NOT IN ('IP', 'MI', 'MC', 'MM', 'MZ')
	GROUP BY A.PALLET_NO
	--1.结束
) b ON A.ID = B.ID 
--是否还需要下面这个and条件，根据你的逻辑推断出来应该要加上这个条件吧
AND A.PALLET_NO=b.PALLET_NO

单独执行你标识的部分需要三秒[/quote] 或者把标识的那部分改成下面这样试试看


SELECT MAX(A.ID) AS id,A.PALLET_NO
	FROM   DBO.TB_MM_MATE_STOCK A WITH (NOLOCK)
	INNER JOIN DBO.TB_SM_CODE_DETAIL b WITH (NOLOCK) ON A.STOCK_CLASS = B.C_V
	INNER JOIN (
		SELECT DISTINCT cv from dbo.TB_SM_CODE_DETAIL
		WHERE cv NOT IN ('IP', 'MI', 'MC', 'MM', 'MZ')
	) c ON b.c_v<>c.C_V
	WHERE  a.CD_CAT = 'IN_CLASS'
	GROUP BY A.PALLET_NO

听雨停了 2017-11-20

打赏
举报

引用 4 楼 qq_24661363 的回复:

[quote=引用 3 楼 qq_37170555 的回复:]


SELECT A.STOCK_CLASS,
       A.FACTORY_CD,
       A.STOCK_FACTORY,
       A.WC_CD,
       A.JOB_INST_NO,
       A.MATE_CD,
       A.PALLET_NO,
       A.LOC_CD
FROM   DBO.TB_MM_MATE_STOCK A WITH (NOLOCK)
INNER JOIN ( 
	--1.单独执行下面这句sql看需要多久           
	SELECT MAX(A.ID) AS id,A.PALLET_NO
	FROM   DBO.TB_MM_MATE_STOCK A WITH (NOLOCK)
	INNER JOIN DBO.TB_SM_CODE_DETAIL b WITH (NOLOCK) ON A.STOCK_CLASS = B.C_V
	WHERE  a.CD_CAT = 'IN_CLASS' AND a.C_V NOT IN ('IP', 'MI', 'MC', 'MM', 'MZ')
	GROUP BY A.PALLET_NO
	--1.结束
) b ON A.ID = B.ID 
--是否还需要下面这个and条件，根据你的逻辑推断出来应该要加上这个条件吧
AND A.PALLET_NO=b.PALLET_NO

单独执行你标识的部分需要三秒[/quote]


SELECT A.STOCK_CLASS,
       A.FACTORY_CD,
       A.STOCK_FACTORY,
       A.WC_CD,
       A.JOB_INST_NO,
       A.MATE_CD,
       A.PALLET_NO,
       A.LOC_CD
FROM   DBO.TB_MM_MATE_STOCK A WITH (NOLOCK)
INNER JOIN ( 
	--1.单独执行下面这句sql看需要多久           
	SELECT MAX(A.ID) AS id,A.PALLET_NO
	FROM   DBO.TB_MM_MATE_STOCK A WITH (NOLOCK)
	INNER JOIN DBO.TB_SM_CODE_DETAIL b WITH (NOLOCK) ON A.STOCK_CLASS = B.C_V
	INNER JOIN (
		SELECT 'IP' AS C_V UNION ALL
		SELECT 'MI' AS C_V UNION ALL
		SELECT 'MC' AS C_V UNION ALL
		SELECT 'MM' AS C_V UNION ALL
		SELECT 'MZ' AS C_V	
	) c ON b.c_v<>c.C_V
	WHERE  a.CD_CAT = 'IN_CLASS'
	GROUP BY A.PALLET_NO
	--1.结束
) b ON A.ID = B.ID 
--是否还需要下面这个and条件，根据你的逻辑推断出来应该要加上这个条件吧
AND A.PALLET_NO=b.PALLET_NO

再单独执行下里面的那个sql看看需要多久，然后整个执行下看看

qq_24661363 2017-11-20

打赏
举报

引用 3 楼 qq_37170555 的回复:


SELECT A.STOCK_CLASS,
       A.FACTORY_CD,
       A.STOCK_FACTORY,
       A.WC_CD,
       A.JOB_INST_NO,
       A.MATE_CD,
       A.PALLET_NO,
       A.LOC_CD
FROM   DBO.TB_MM_MATE_STOCK A WITH (NOLOCK)
INNER JOIN ( 
	--1.单独执行下面这句sql看需要多久           
	SELECT MAX(A.ID) AS id,A.PALLET_NO
	FROM   DBO.TB_MM_MATE_STOCK A WITH (NOLOCK)
	INNER JOIN DBO.TB_SM_CODE_DETAIL b WITH (NOLOCK) ON A.STOCK_CLASS = B.C_V
	WHERE  a.CD_CAT = 'IN_CLASS' AND a.C_V NOT IN ('IP', 'MI', 'MC', 'MM', 'MZ')
	GROUP BY A.PALLET_NO
	--1.结束
) b ON A.ID = B.ID 
--是否还需要下面这个and条件，根据你的逻辑推断出来应该要加上这个条件吧
AND A.PALLET_NO=b.PALLET_NO

单独执行你标识的部分需要三秒

听雨停了 2017-11-20

打赏
举报


SELECT A.STOCK_CLASS,
       A.FACTORY_CD,
       A.STOCK_FACTORY,
       A.WC_CD,
       A.JOB_INST_NO,
       A.MATE_CD,
       A.PALLET_NO,
       A.LOC_CD
FROM   DBO.TB_MM_MATE_STOCK A WITH (NOLOCK)
INNER JOIN ( 
	--1.单独执行下面这句sql看需要多久           
	SELECT MAX(A.ID) AS id,A.PALLET_NO
	FROM   DBO.TB_MM_MATE_STOCK A WITH (NOLOCK)
	INNER JOIN DBO.TB_SM_CODE_DETAIL b WITH (NOLOCK) ON A.STOCK_CLASS = B.C_V
	WHERE  a.CD_CAT = 'IN_CLASS' AND a.C_V NOT IN ('IP', 'MI', 'MC', 'MM', 'MZ')
	GROUP BY A.PALLET_NO
	--1.结束
) b ON A.ID = B.ID 
--是否还需要下面这个and条件，根据你的逻辑推断出来应该要加上这个条件吧
AND A.PALLET_NO=b.PALLET_NO

qq_24661363 2017-11-20

打赏
举报

引用 1 楼 yenange 的回复:

SELECT A.STOCK_CLASS,
       A.FACTORY_CD,
       A.STOCK_FACTORY,
       A.WC_CD,
       A.JOB_INST_NO,
       A.MATE_CD,
       A.PALLET_NO,
       A.LOC_CD
FROM   DBO.TB_MM_MATE_STOCK A WITH (NOLOCK) WHERE A.ID IN
(
	SELECT ID
	FROM   DBO.TB_MM_MATE_STOCK AS A WITH (NOLOCK) 
		   INNER JOIN DBO.TB_SM_CODE_DETAIL AS B WITH (NOLOCK) 
		   ON  A.STOCK_CLASS = B.C_V 
				AND B.CD_CAT = 'IN_CLASS'
				AND B.C_V NOT IN ('IP', 'MI', 'MC', 'MM', 'MZ')
	GROUP BY A.PALLET_NO
)

这样多少秒？

报错的，group by 那里。

吉普赛的歌 2017-11-20

打赏
举报

SELECT A.STOCK_CLASS,
       A.FACTORY_CD,
       A.STOCK_FACTORY,
       A.WC_CD,
       A.JOB_INST_NO,
       A.MATE_CD,
       A.PALLET_NO,
       A.LOC_CD
FROM   DBO.TB_MM_MATE_STOCK A WITH (NOLOCK) WHERE A.ID IN
(
	SELECT ID
	FROM   DBO.TB_MM_MATE_STOCK AS A WITH (NOLOCK) 
		   INNER JOIN DBO.TB_SM_CODE_DETAIL AS B WITH (NOLOCK) 
		   ON  A.STOCK_CLASS = B.C_V 
				AND B.CD_CAT = 'IN_CLASS'
				AND B.C_V NOT IN ('IP', 'MI', 'MC', 'MM', 'MZ')
	GROUP BY A.PALLET_NO
)

这样多少秒？