I'm trying to fix a bug in an app (not my own) that runs a large query on the database. This causes a 504 Gateway Time-out when deployed. When I run the query in MSSMS it just executes forever without returning any results:
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
I have simplified the query without using ROW_NUMBERS() OVER and the query executes fairly quickly without issues. This is the modified query:
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) )
In the app, before it times-out it appears to be retrieving 1.6 million records whereas the simplified query only 72k, not sure why that is.
Any idea how to fix this?
First, phrase the query as a 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'
)
Then, several things could be affecting performance:
To eliminate the third possibility, just check how fast this runs:
select count(*)
from cs;
You can check the first by simply doing:
select *
from cs;
Then you can add in the row_number():
SELECT cs.*,
ROW_NUMBER OVER ( ORDER BY [ITEM_UID] ) AS RowNum
FROM cs;