34,590
社区成员
发帖
与我相关
我的任务
分享请问各位大神如何优化大数据库的查询语句
我有多张表的关联,并且每张表的数据量都在十几万条以上, 通过条件筛选后的数据量也在七到八万条. 已经使用临时表的方式写过,但是查询效率也很慢. 几乎在十多分钟才出得来数据..
我想问的是,除去临时表的方式,还有其它方法能再优化吗?
我想问的是,除去临时表的方式,还有其它方法能再优化吗?
...全文
请发表友善的回复…
发表回复
OwenZeng_DBA 2017-11-14
- 打赏
- 举报
这3个查询是哪个查询比较慢，你可以先对最慢的进行优化
qq_41053136 2017-11-14
- 打赏
- 举报
DECLARE @FROM_DATE DATETIME,@TO_DATE DATETIME,@PINPAI VARCHAR(32),@SX VARCHAR(32),@YR VARCHAR(32),@JJ VARCHAR(32),@XB VARCHAR(32),@MDBH VARCHAR(32),@PROD_CLS VARCHAR(32)
SET @FROM_DATE =:FROM_DATE
SET @TO_DATE =:TO_DATE
SET @MDBH=:MDBH SET @PINPAI=:PINPAI
SET @SX=:SX SET @YR=:YR SET @JJ=:JJ SET @XB=:XB SET @PROD_CLS=:PROD_CLS
--库存表
SELECT UG.UG_NAME , SYS_UNIT.UNIT_NAME , EX.PROD_CLS_NUM ,EX.PROD_NAME ,
SUM(STK.STK_ON_HAND)AS STK , SUM(STK.STK_ON_HAND * ISNULL(COST.UNIT_COST,0)) AS COST
INTO #STK
FROM WAREH_STK STK
LEFT JOIN SYS_UNIT ON STK.WAREH_ID = SYS_UNIT.UNIT_ID
INNER JOIN SYS_UG_DTL UD ON STK.WAREH_ID = UD.MBR_ID
INNER JOIN SYS_UG UG ON UD.UG_ID = UG.UG_ID AND UG_TYPE = 'MG' AND UNIT_TYPE = 'SH'
LEFT JOIN PROD_EX EX ON STK.PROD_ID = EX.PROD_ID
LEFT JOIN WAREH W ON W.WAREH_ID = STK.WAREH_ID
LEFT JOIN STK_COST COST ON COST.PROD_CLS_ID = EX.PROD_CLS_ID AND W.COST_GRP_ID =COST.COST_GRP_ID AND COST.UNIT_ID = 1
LEFT JOIN SYS_CODE_DTL BRAND ON BRAND.CODE = EX.BRAND AND BRAND.CODE_TYPE = 'BRAND'
LEFT JOIN SYS_CODE_DTL SCD2 ON SCD2.CODE=EX.PROD_PROP AND SCD2.CODE_TYPE='PROD_PROP'
LEFT JOIN SYS_CODE_DTL SCD3 ON SCD3.CODE=EX.SEASON AND SCD3.CODE_TYPE='SEASON'
LEFT JOIN SYS_CODE_DTL SCD6 ON SCD6.CODE=EX.GENDER AND SCD6.CODE_TYPE='GENDER'
WHERE SYS_UNIT.UNIT_NUM LIKE @MDBH AND EX.PROD_CLS_NUM LIKE @PROD_CLS AND STK.STK_ON_HAND <> 0
AND (EX.YEAR_VAL LIKE @YR OR EX.YEAR_VAL IS NULL) AND (BRAND.CODE LIKE @PINPAI OR BRAND.CODE IS NULL)
AND (SCD2.CODE LIKE @SX OR SCD2.CODE IS NULL )AND (SCD3.CODE LIKE @JJ OR SCD3.CODE IS NULL )
AND (SCD6.CODE LIKE @XB OR SCD6.CODE IS NULL)
GROUP BY UG.UG_NAME,SYS_UNIT.UNIT_NAME , EX.PROD_CLS_NUM ,EX.PROD_NAME
--===================================================================================================================
--零售表
SELECT UG.UG_NAME , SYS_UNIT.UNIT_NAME , EX.PROD_CLS_NUM ,EX.PROD_NAME ,
SUM(RLB_DTL.SELL_QTY) AS QTY, SUM(RLB_DTL.SELL_VAL) AS VAL,SUM(RLB_DTL.SELL_QTY * ISNULL(COST.UNIT_COST,0)) AS COST
INTO #SELL
FROM RLB
LEFT JOIN RLB_DTL ON RLB.SHOP_ID = RLB_DTL.SHOP_ID AND RLB.RLB_NUM = RLB_DTL.RLB_NUM AND RLB.UNIT_ID =1
LEFT JOIN SYS_UNIT ON RLB.SHOP_ID = SYS_UNIT.UNIT_ID
INNER JOIN SYS_UG_DTL UD ON RLB.SHOP_ID = UD.MBR_ID
INNER JOIN SYS_UG UG ON UD.UG_ID = UG.UG_ID AND UG_TYPE = 'MG' AND UNIT_TYPE = 'SH'
LEFT JOIN PROD_EX EX ON RLB_DTL.PROD_ID = EX.PROD_ID
LEFT JOIN WAREH W ON W.WAREH_ID = RLB.SHOP_ID
LEFT JOIN STK_COST COST ON COST.PROD_CLS_ID = EX.PROD_CLS_ID AND W.COST_GRP_ID =COST.COST_GRP_ID AND COST.UNIT_ID = 1
LEFT JOIN SYS_CODE_DTL BRAND ON BRAND.CODE = EX.BRAND AND BRAND.CODE_TYPE = 'BRAND'
LEFT JOIN SYS_CODE_DTL SCD2 ON SCD2.CODE=EX.PROD_PROP AND SCD2.CODE_TYPE='PROD_PROP'
LEFT JOIN SYS_CODE_DTL SCD3 ON SCD3.CODE=EX.SEASON AND SCD3.CODE_TYPE='SEASON'
LEFT JOIN SYS_CODE_DTL SCD6 ON SCD6.CODE=EX.GENDER AND SCD6.CODE_TYPE='GENDER'
WHERE SYS_UNIT.UNIT_NUM LIKE @MDBH AND EX.PROD_CLS_NUM LIKE @PROD_CLS AND
RLB.DOC_DATE BETWEEN @FROM_DATE AND @TO_DATE
AND (EX.YEAR_VAL LIKE @YR OR EX.YEAR_VAL IS NULL)
AND (BRAND.CODE LIKE @PINPAI OR BRAND.CODE IS NULL)
AND (SCD2.CODE LIKE @SX OR SCD2.CODE IS NULL )AND (SCD3.CODE LIKE @JJ OR SCD3.CODE IS NULL )
AND (SCD6.CODE LIKE @XB OR SCD6.CODE IS NULL)
GROUP BY UG.UG_NAME , SYS_UNIT.UNIT_NAME , EX.PROD_CLS_NUM ,EX.PROD_NAME
--======================================================================================================================
SELECT A.UG_NAME,A.UNIT_NAME,A.PROD_CLS_NUM,A.PROD_NAME,BRAND.DESCRIPTION AS BRAND,SCD1.DESCRIPTION AS D_STYLE,SCD2.DESCRIPTION AS PROD_PROP,
PS.DESCRIPTION AS PS,EX.YEAR_VAL AS YR,SCD3.DESCRIPTION AS SEASON,SCD4.DESCRIPTION AS M_STYLE,SCD5.DESCRIPTION AS SUB_STYLE,SCD6.DESCRIPTION AS GENDER,
EX.SALES_DATE ,U.UNIT_NAME AS U_NAME ,EX.STD_UNIT_PRICE,
ROUND((CASE WHEN (CASE WHEN F.UNIT_PRICE*F.DISC_RATE IS NULL THEN FF.UNIT_PRICE*FF.DISC_RATE ELSE F.UNIT_PRICE*F.DISC_RATE END)
IS NULL THEN EX.STD_UNIT_PRICE ELSE (CASE WHEN F.UNIT_PRICE*F.DISC_RATE IS NULL THEN FF.UNIT_PRICE*FF.DISC_RATE ELSE F.UNIT_PRICE*F.DISC_RATE END) END),2,0)AS RT_PRICE,
ROUND((CASE WHEN (CASE WHEN F.UNIT_PRICE*F.DISC_RATE IS NULL THEN FF.UNIT_PRICE*FF.DISC_RATE ELSE F.UNIT_PRICE*F.DISC_RATE END)
IS NULL THEN EX.STD_UNIT_PRICE ELSE (CASE WHEN F.UNIT_PRICE*F.DISC_RATE IS NULL THEN FF.UNIT_PRICE*FF.DISC_RATE ELSE F.UNIT_PRICE*F.DISC_RATE END) END)/EX.STD_UNIT_PRICE,2,0) AS ZL,
A.QTY,A.VAL,A.S_COST,A.STK,A.COST
FROM
(SELECT ISNULL(STK.UG_NAME ,SELL.UG_NAME )AS UG_NAME,ISNULL(STK.UNIT_NAME ,SELL.UNIT_NAME )AS UNIT_NAME,
ISNULL(STK.PROD_CLS_NUM ,SELL.PROD_CLS_NUM )AS PROD_CLS_NUM, ISNULL(STK.PROD_NAME ,SELL.PROD_NAME )AS PROD_NAME,
ISNULL(SELL.QTY,0)AS QTY, ISNULL(SELL.VAL,0)AS VAL,ISNULL(SELL.COST,0)AS S_COST,ISNULL(STK.STK,0)AS STK, ISNULL(STK.COST,0)AS COST
FROM #STK STK
FULL JOIN #SELL SELL ON SELL.UNIT_NAME=STK.UNIT_NAME AND SELL.PROD_CLS_NUM =STK.PROD_CLS_NUM)A
LEFT JOIN PROD_CLS EX ON EX.PROD_CLS_NUM = A.PROD_CLS_NUM
LEFT JOIN SYS_CODE_DTL BRAND ON BRAND.CODE = EX.BRAND AND BRAND.CODE_TYPE = 'BRAND'
LEFT JOIN SYS_CODE_DTL SCD1 ON SCD1.CODE=EX.DSN_STYLE AND SCD1.CODE_TYPE='DSN_STYLE'
LEFT JOIN PROD_SORT PS ON PS.PROD_SORT_ID=EX.PROD_SORT_ID
LEFT JOIN SYS_CODE_DTL SCD2 ON SCD2.CODE = PS.PROD_PROP AND SCD2.CODE_TYPE='PROD_PROP'
LEFT JOIN SYS_CODE_DTL SCD3 ON SCD3.CODE=EX.SEASON AND SCD3.CODE_TYPE='SEASON'
LEFT JOIN SYS_CODE_DTL SCD4 ON SCD4.CODE=EX.MAIN_STYLE AND SCD4.CODE_TYPE='MAIN_STYLE'
LEFT JOIN SYS_CODE_DTL SCD5 ON SCD5.CODE=EX.SUB_STYLE AND SCD5.CODE_TYPE='SUB_STYLE'
LEFT JOIN SYS_CODE_DTL SCD6 ON SCD6.CODE=EX.GENDER AND SCD6.CODE_TYPE='GENDER'
LEFT JOIN SYS_UNIT U ON U.UNIT_ID = EX.DFLT_VENDER_ID
LEFT JOIN SYS_UNIT C ON C.UNIT_NAME = A.UNIT_NAME
LEFT JOIN PRL_DTL F ON F.UNIT_ID = C.UNIT_ID
AND F.PROD_CLS_ID = EX.PROD_CLS_ID AND F.PRL_TYPE='RT' AND(F.EXP_DATE>GETDATE() OR F.EXP_DATE IS NULL)
LEFT JOIN PRL_DTL FF ON FF.UNIT_ID = 1 AND
FF.PROD_CLS_ID = EX.PROD_CLS_ID AND FF.PRL_TYPE='RT' AND(FF.EXP_DATE>GETDATE() OR FF.EXP_DATE IS NULL)
DROP TABLE #STK,#SELL
OwenZeng_DBA 2017-11-14
- 打赏
- 举报
吧语句发上来看看吧。使用临时表来拆分是个不错的办法
吉普赛的歌 版主 2017-11-14
- 打赏
- 举报
这种报表形式的， 怕麻烦就只能辞职不干算了。只能一点点地筛选对比， 看哪些条件不加会比较快， 从而作为重点来处理。
对于能快速缩小结果集的（比如说一个表加条件后由一百万行缩小到一万行）， 尽量最先查询出来放到一个临时表， 再来与其它表进行连接。
二月十六 版主 2017-11-14
- 打赏
- 举报
可以用二分法，不是很麻烦，应该是一个比较有乐趣的过程
OwenZeng_DBA 2017-11-14
- 打赏
- 举报
有时候看上去麻烦的办法反而简单，，不然你可能花大量时间去找这个简单的办法，，，结果半天没找到。
qq_41053136 2017-11-14
- 打赏
- 举报
这个方法好麻烦啊,对于怕麻烦的我.. 哎.... 二月十六 版主 2017-11-14
- 打赏
- 举报
太长了……
楼主可以把连接表和where条件先去掉，然后一点点再填回去，先确认好那个地方是最耗时的，然后一步步优化
OwenZeng_DBA 2017-11-14
- 打赏
- 举报
你总要一步一步的来，看看慢在哪里再说，总的来说你的语句join的表还是很多，而且查询条件里面有like，你发下带有值的语句，就是没有参数化的语句再看看
qq_41053136 2017-11-14
- 打赏
- 举报
没别的方法了?
