ROW_NUMBER()超出了大型查询的超时时间.最佳解决方案

Question

我正试图修复一个在数据库上运行大型查询的应用程序(而不是我自己的(中的错误。这会在部署时导致504网关超时。当我在MSSMS中运行查询时，它只会永远执行，不会返回任何结果：

SELECT * 
FROM   (SELECT Row_number() 
OVER ( 
ORDER BY [TABLE_ONE].[MET_uid] ) 
AS RowNum, 
(SELECT Count(1) 
FROM   TABLE_ONE 
LEFT OUTER JOIN TABLE_TWO 
ON TABLE_ONE.SCC_uid = 
TABLE_TWO.SCC_uid 
WHERE  ( [TABLE_ONE].[ANOTHER_DATE] <= 
'30 November 2020' ) 
AND ( [TABLE_ONE].[SOME_DATE] >= 
'30 November 2020' ) 
AND ( [TABLE_TWO].[ANOTHER_DATE] <= 
'30 November 2020' ) 
AND ( [TABLE_TWO].[SOME_DATE] >= 
'30 November 2020' )) 
AS 
TotalRows, 
[TABLE_ONE].[MET_uid] 
AS TABLE_ONE_MET_UID, 
[TABLE_ONE].[MET_type_uid] 
AS TABLE_ONE_MET_TYPE_UID, 
[TABLE_ONE].[IIC] 
AS 
TABLE_ONE_IIC, 
[TABLE_ONE].[LCD] 
AS 
TABLE_ONE_LCD, 
[TABLE_ONE].[ANOTHER_DATE] 
AS TABLE_ONE_ANOTHER_DATE, 
[TABLE_ONE].[SOME_DATE] 
AS TABLE_ONE_SOME_DATE, 
[TABLE_ONE].[SOME_DATEd] 
AS TABLE_ONE_SOME_DATED, 
[TABLE_ONE].[LED] 
AS TABLE_ONE_LED, 
[TABLE_ONE].[LED_U_UID] 
AS 
TABLE_ONE_LED_U_UID, 
[TABLE_ONE].[P_UID] 
AS TABLE_ONE_P_UID, 
[TABLE_ONE].[FM_UID] 
AS TABLE_ONE_FM_UID, 
[TABLE_ONE].[D_LEN] 
AS TABLE_ONE_D_LEN, 
[TABLE_ONE].[D_AR] 
AS TABLE_ONE_D_AR, 
[TABLE_ONE].[ETI] 
AS TABLE_ONE_ETI, 
[TABLE_ONE].[NT] 
AS TABLE_ONE_NT, 
[TABLE_ONE].[BLT] 
AS TABLE_ONE_BLT, 
[TABLE_ONE].[BLK] 
AS TABLE_ONE_BLK, 
[TABLE_ONE].[BLU] 
AS TABLE_ONE_BLU, 
[TABLE_ONE].[trcn] 
AS TABLE_ONE_TRCN, 
[TABLE_ONE].[SCC_uid] 
AS TABLE_ONE_SCC_UID, 
[TABLE_TWO].[F_uid] 
AS TABLE_TWO_F_UID, 
[TABLE_TWO].[RDT_UID] 
AS TABLE_TWO_RDT_UID, 
[TABLE_TWO].[SCC_code] 
AS TABLE_TWO_SCC_CODE, 
[TABLE_TWO].[SCC_name] 
AS TABLE_TWO_SCC_NAME, 
[TABLE_TWO].[SCC_type_uid] 
AS TABLE_TWO_SCC_TYPE_UID, 
[TABLE_TWO].[SCC_length] 
AS TABLE_TWO_SCC_LENGTH, 
[TABLE_TWO].[SPDT] 
AS TABLE_TWO_SPDT, 
[TABLE_TWO].[SNC] 
AS TABLE_TWO_SNC, 
[TABLE_TWO].[SNJT_uid] 
AS 
TABLE_TWO_SNJT_UID, 
[TABLE_TWO].[SN_name] 
AS TABLE_TWO_SN_NAME, 
[TABLE_TWO].[ENC] 
AS TABLE_TWO_ENC, 
[TABLE_TWO].[ENCT_JT_uid] 
AS 
TABLE_TWO_ENCT_JT_UID, 
[TABLE_TWO].[ENCT_name] 
AS TABLE_TWO_ENCT_NAME, 
[TABLE_TWO].[NTT_uid] 
AS TABLE_TWO_NTT_UID, 
[TABLE_TWO].[RG_uid] 
AS TABLE_TWO_RG_UID, 
[TABLE_TWO].[AR_uid] 
AS TABLE_TWO_AR_UID, 
[TABLE_TWO].[division_uid] 
AS TABLE_TWO_DIVISION_UID, 
[TABLE_TWO].[DST_uid] 
AS TABLE_TWO_DST_UID, 
[TABLE_TWO].[SVV_AR_uid] 
AS TABLE_TWO_SVV_AR_UID, 
[TABLE_TWO].[RTF] 
AS TABLE_TWO_RTF, 
[TABLE_TWO].[CL_UID] 
AS TABLE_TWO_CL_UID, 
[TABLE_TWO].[MH_uid] 
AS 
TABLE_TWO_MH_UID, 
[TABLE_TWO].[ENV_uid] 
AS TABLE_TWO_ENV_UID, 
[TABLE_TWO].[ONR_uid] 
AS TABLE_TWO_ONR_UID, 
[TABLE_TWO].[AG_uid] 
AS TABLE_TWO_AG_UID, 
[TABLE_TWO].[ANOTHER_DATE] 
AS TABLE_TWO_ANOTHER_DATE, 
[TABLE_TWO].[SOME_DATE] 
AS TABLE_TWO_SOME_DATE, 
[TABLE_TWO].[DPT_code] 
AS TABLE_TWO_DPT_CODE, 
[TABLE_TWO].[NMT_code] 
AS TABLE_TWO_NMT_CODE, 
[TABLE_TWO].[RW_code] 
AS 
TABLE_TWO_RW_CODE, 
[TABLE_TWO].[SLVL_uid] 
AS TABLE_TWO_SLVL_UID, 
[TABLE_TWO].[FTG_code] 
AS TABLE_TWO_FTG_CODE, 
[TABLE_TWO].[FT_uid] 
AS 
TABLE_TWO_FT_UID, 
[TABLE_TWO].[FO_uid] 
AS TABLE_TWO_FO_UID, 
[TABLE_TWO].[DC] 
AS TABLE_TWO_DC, 
[TABLE_TWO].[SVV_uid] 
AS TABLE_TWO_SVV_UID, 
[TABLE_TWO].[SCC_length_status] 
AS 
TABLE_TWO_SCC_LENGTH_STATUS, 
[TABLE_TWO].[SVV] 
AS TABLE_TWO_SVV, 
[TABLE_TWO].[MAX] 
AS TABLE_TWO_MAX, 
[TABLE_TWO].[SQN] 
AS TABLE_TWO_SQN, 
[TABLE_TWO].[DRT] 
AS TABLE_TWO_DRT, 
[TABLE_TWO].[cwxsp] 
AS TABLE_TWO_CWXSP, 
[TABLE_TWO].[offcwxsp] 
AS TABLE_TWO_OFFCWXSP, 
[TABLE_TWO].[ukpms_MHH_uid] 
AS 
TABLE_TWO_UKPMS_MHH_UID, 
[TABLE_TWO].[fway_MHH_uid] 
AS 
TABLE_TWO_FWAY_MHH_UID, 
[TABLE_TWO].[SMH_uid] 
AS 
TABLE_TWO_SMH_UID, 
[TABLE_TWO].[SMP_ROU_uid] 
AS 
TABLE_TWO_SMP_ROU_UID, 
[TABLE_TWO].[SMP_dot_CLK_uid] 
AS 
TABLE_TWO_SMP_DOT_CLK_UID, 
[TABLE_TWO].[LCT_uid] 
AS TABLE_TWO_LCT_UID, 
[TABLE_TWO].[LH] 
AS TABLE_TWO_LH, 
[TABLE_TWO].[programmed_date] 
AS TABLE_TWO_PROGRAMMED_DATE, 
[TABLE_TWO].[LCD_uid] 
AS TABLE_TWO_LCD_UID, 
[TABLE_TWO].[CW] 
AS TABLE_TWO_CW, 
[TABLE_TWO].[lca_occ_LCT_uid] 
AS 
TABLE_TWO_LCA_OCC_LCT_UID, 
[TABLE_TWO].[SG] 
AS TABLE_TWO_SG, 
[TABLE_TWO].[SGG] 
AS TABLE_TWO_SGG 
FROM   TABLE_ONE 
LEFT OUTER JOIN TABLE_TWO 
ON TABLE_ONE.SCC_uid = 
TABLE_TWO.SCC_uid 
WHERE  ( [TABLE_ONE].[ANOTHER_DATE] <= '30 November 2020' ) 
AND ( [TABLE_ONE].[SOME_DATE] >= '30 November 2020' ) 
AND ( [TABLE_TWO].[ANOTHER_DATE] <= '30 November 2020' ) 
AND ( [TABLE_TWO].[SOME_DATE] >= '30 November 2020' )) AS 
RowConstrainedResult 
WHERE  rownum >= 0 
AND rownum < 24 
ORDER  BY rownum 

我在不使用ROW_NUMBERS((OVER的情况下简化了查询，并且查询执行得相当快，没有问题。这是修改后的查询：

SELECT dbo.TABLE_ONE.*, 
dbo.TABLE_TWO.* 
FROM   dbo.TABLE_ONE 
INNER JOIN dbo.TABLE_TWO 
ON dbo.TABLE_ONE.SCC_uid = 
dbo.TABLE_TWO.SCC_uid 
WHERE  ( dbo.TABLE_ONE.ANOTHER_DATE <= 
CONVERT(DATETIME, '2020-11-30 00:00:00' 
, 
102) ) 
AND ( dbo.TABLE_ONE.SOME_DATE >= 
CONVERT(DATETIME, '2020-11-30 00:00:00', 
102) ) 
AND ( dbo.TABLE_TWO.ANOTHER_DATE <= 
CONVERT(DATETIME, '2020-11-30 00:00:00', 
102) ) 
AND ( dbo.TABLE_TWO.SOME_DATE >= 
CONVERT(DATETIME, '2020-11-30 00:00:00', 
102) ) 

在应用程序中，在超时之前，它似乎检索了160万条记录，而简化的查询只有72k条，不确定为什么。

知道怎么解决这个问题吗？

Answer 1

首先，将查询表述为CTE:

WITH cs as (
SELECT ca.*, sa.*. -- you may need to adjust the columns so there are no duplicates
FROM  dbo.RM_COMMON_ATTRIBUTES ca INNER JOIN
dbo.SECTION_ATTRIBUTES sa
ON ca.SECTION_UID = sa.SECTION_UID
WHERE ca.START_DATE <= '2020-11-30' AND
ca.END_DATE >= '2020-11-30' AND 
sa.START_DATE <= '2020-11-30' AND
sa.END_DATE >= '2020-11-30'
)

然后，有几件事可能会影响性能：

• 结果集的大小
• 行号
• 返回所有行，而不仅仅是第一行

要消除第三种可能性，只需检查它的运行速度：

select count(*)
from cs;

你可以通过简单的操作来检查第一个：

select *
from cs;

然后您可以添加row_number():

SELECT cs.*, 
ROW_NUMBER OVER (  ORDER BY [ITEM_UID] ) AS RowNum
FROM cs;