Query taking too long - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query taking too long

Author

Topic

Damian39
Starting Member

33 Posts

Posted - 2014-10-10 : 12:57:20

Hello all,
I've written a query and when I attempt to run it, it runs for over 40 minutes and continues to run until I manually cancel it. I was hoping someone could look the query over to tell me where I am going wrong. I'm sure it has something to do with one of the temp tables that I am using which is causing the query to endlessly loop, but I have been unable to find it, and was hoping a fresh set of eyes might be able to see what I cannot. Here is the query in it's entirety:


IF OBJECT_ID('TEMPDB..#TMP1C') IS NOT NULL DROP TABLE #TMP1C


IF OBJECT_ID('TEMPDB..#TMPCSGACCT1') IS NOT NULL DROP TABLE #TMPCSGACCT1
IF OBJECT_ID('TEMPDB..#TMPCSGACCT2') IS NOT NULL DROP TABLE #TMPCSGACCT2
IF OBJECT_ID('TEMPDB..#TMPCSGACCT3') IS NOT NULL DROP TABLE #TMPCSGACCT3
IF OBJECT_ID('TEMPDB..#TMPCSGACCT4') IS NOT NULL DROP TABLE #TMPCSGACCT4

--delete [AuditGroup].dbo.EquipRetDataTest

CREATE TABLE #TMPCSGACCT1 (
	ITEM# NUMERIC (7,0),
	SUB_ACCT_NO VARCHAR (MAX)
)
INSERT INTO #TMPCSGACCT1 (ITEM#,SUB_ACCT_NO)

SELECT	ITEM#,
		rtrim(ltrim([SUB_ACCT_NO])) AS SUB_ACCT_NO

FROM [AuditGroup].dbo.EquipRetDataTest (NOLOCK)

CREATE TABLE #TMPCSGACCT2 (
	ITEM# NUMERIC (7,0),
	SUB_ACCT_NO VARCHAR (MAX)
)

INSERT INTO #TMPCSGACCT2 (ITEM#,SUB_ACCT_NO)

SELECT	ITEM#,SUB_ACCT_NO
FROM	#TMPCSGACCT1 WITH(NOLOCK)
WHERE	LEFT([SUB_ACCT_NO],6) IN ('849560','849575')

CREATE TABLE #TMPCSGACCT3 (
	ITEM# NUMERIC (7,0),
	PRIN SMALLINT,
	SUB_ACCT_NO CHAR (16)
)
INSERT INTO #TMPCSGACCT3 (ITEM#,PRIN,SUB_ACCT_NO)

SELECT	ITEM#,
		CASE WHEN LEFT([SUB_ACCT_NO],6) = '849560' THEN '6000'
		--WHEN LEFT([SUB_ACCT_NO],6) = '849574' THEN '7400'
		WHEN LEFT([SUB_ACCT_NO],6) = '849575' THEN '7500'
		ELSE '' END AS PRIN,
		SUB_ACCT_NO

FROM	#TMPCSGACCT2 WITH(NOLOCK)

CREATE TABLE #TMPCSGACCT4 (
	ITEM# NUMERIC (7,0),
	PRIN SMALLINT,
	SUB_ACCT_NO CHAR (16),
	[CUST_ACCT_NO_SBB] CHAR(13)
)
INSERT INTO #TMPCSGACCT4 (ITEM#,PRIN,SUB_ACCT_NO,[CUST_ACCT_NO_SBB])

SELECT	A.ITEM#,A.PRIN,A.SUB_ACCT_NO,S.[CUST_ACCT_NO_SBB] 

FROM	#TMPCSGACCT3 A WITH(NOLOCK) INNER JOIN [Vantage].[dbo].[SBB_BASE] S WITH(NOLOCK)
ON		A.PRIN=S.PRIN_SBB AND
		A.SUB_ACCT_NO=S.SUB_ACCT_NO_SBB AND S.PRIN_SBB IN ('6000','7500')

select distinct T.PRIN_MON, 
T.AGNT_MON, 
T.SUB_ACCT_NO_MON, 
s.RES_NAME_SBB,
hs.ADDR1_HSE,
hs.RES_CITY_HSE,
hs.RES_STATE_HSE,
hs.POSTAL_CDE_HSE,
T.TRAN_DTE_MON, 
--T.TRAN_CDE_MON, 
T.TRAN_AMT_MON, 
s.CUR_BAL_SBB,
case when h.acct_stage_ohi = 'v' then 'VOL_DISCO'
	 when h.ACCT_STAGE_OHI = 'n' then 'NON_PAY_DISCO'
ELSE '' END AS 'CUSTOMER_STATUS',
h.connect_dte_ohi, 
h.disco_dte_ohi, 
h.item_status_ohi

INTO #TMP1C

from #TMPCSGACCT4 as T4, vantage.dbo.ohi_hist_item as h (nolock),Vantage.dbo.SBB_BASE as s (nolock),
Vantage.dbo.HSE_BASE as hs (nolock),vantage.dbo.MON_TRAN_BASE as T (nolock)

where T4.PRIN = H.PRIN_OHI
and	T4.SUB_ACCT_NO = h.SUB_ACCT_NO_OHI
and T4.CUST_ACCT_NO_SBB = h.CUST_ACCT_NO_OHI
and h.PRIN_OHI = s.PRIN_SBB
and h.SYS_OHI = s.SYS_SBB
and h.SUB_ACCT_NO_OHI = s.SUB_ACCT_NO_SBB
and s.HSE_KEY_SBB = hs.HSE_KEY_HSE
and t.PRIN_MON = h.PRIN_OHI
and t.sub_acct_no_mon = h.sub_acct_no_ohi

and H.DISCO_DTE_OHI = (select max(disco_dte_ohi) as disco_dte_ohi 
FROM Vantage.dbo.OHI_HIST_ITEM AS B WHERE B.CUST_ACCT_NO_OHI = H.CUST_ACCT_NO_OHI)
and h.DISCO_DTE_OHI > h.CONNECT_DTE_OHI
and T.TRAN_CDE_MON = '287'
and h.acct_stage_ohi in ('V','N')
and h.ITEM_STATUS_OHI NOT IN ('B','X')


group by T.PRIN_MON, 
T.AGNT_MON, 
T.SUB_ACCT_NO_MON,
s.RES_NAME_SBB,
hs.ADDR1_HSE,
hs.RES_CITY_HSE,
hs.RES_STATE_HSE,
hs.POSTAL_CDE_HSE, 
T.TRAN_DTE_MON, 
--T.TRAN_CDE_MON, 
T.TRAN_AMT_MON, 
s.CUR_BAL_SBB,
h.acct_stage_ohi,
h.connect_dte_ohi, 
h.disco_dte_ohi, 
h.item_status_ohi

SELECT A.*, EL.END_DTE_EQL, EL.eqp_stat_eql, EL.EQP_SERIAL_EQl 


FROM #TMP1C AS A (NOLOCK) left outer JOIN Vantage.dbo.EQL_HIST AS EL (NOLOCK)
ON		A.SUB_ACCT_NO_MON = EL.EQP_LOCAL_EQL

WHERE EL.EQP_STAT_EQL = 'D'
--AND EQ.OWNER_CDE_EQP = 'R'
AND EL.END_DTE_EQL = (SELECT MAX(END_DTE_EQL) AS END_DTE_EQL FROM Vantage.dbo.EQL_HIST AS EL2
					  WHERE  EL.EQP_LOCAL_EQL = EL2.EQP_LOCAL_EQL)

group by A.PRIN_MON, 
A.AGNT_MON, 
A.SUB_ACCT_NO_MON,
A.RES_NAME_SBB,
A.ADDR1_HSE,
A.RES_CITY_HSE,
A.RES_STATE_HSE,
A.POSTAL_CDE_HSE, 
A.TRAN_DTE_MON, 
--T.TRAN_CDE_MON, 
A.TRAN_AMT_MON, 
A.CUR_BAL_SBB,
El.eqp_stat_eql,
el.EQP_SERIAL_EQl, 
A.CUSTOMER_STATUS,
A.connect_dte_ohi, 
A.disco_dte_ohi, 
A.item_status_ohi,
EL.END_DTE_EQL

Thank you for any and all help!

Damian

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-10 : 13:14:12

There is no looping logic in your code, so it's not in an indefinite loop. You'll need to break this down to figure out where the bottleneck is. You are likely missing an index.

To break it down, comment out everything below the first insert/select and then run it. Does that complete quickly? If yes, then uncomment the next section and include the next insert/select. Keep doing that until you find where the issue is. Then let us know which part of your code is the problem.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Damian39
Starting Member

33 Posts

Posted - 2014-10-14 : 13:28:01

Hi tkizer, I've tried running each select individually and the first 4 complete within seconds, but once I call the fifth select statement, it runs and runs with no end. I believe it may have something to do with my hitting the same table twice. The reason I have the sub-query within the fifth select statement is to get the most recent disconnect date within that table. If I shouldn't do it this way, then should I perhaps hit the table to pull my records, and then list all of my different parameters within my last select statement? Essentially what I am trying to do is shorten the amount of time it takes my query to run, and I was informed the more where statements one has in their query, the longer the query will take. I've highlighted in red where I am hitting the same table twice.
[code]
CREATE TABLE #TMPCSGACCT1 (
ITEM# NUMERIC (7,0),
SUB_ACCT_NO VARCHAR (MAX)
)
INSERT INTO #TMPCSGACCT1 (ITEM#,SUB_ACCT_NO)

SELECT ITEM#,
rtrim(ltrim([SUB_ACCT_NO])) AS SUB_ACCT_NO

FROM [AuditGroup].dbo.EquipRetDataTest (NOLOCK)

CREATE TABLE #TMPCSGACCT2 (
ITEM# NUMERIC (7,0),
SUB_ACCT_NO VARCHAR (MAX)
)

INSERT INTO #TMPCSGACCT2 (ITEM#,SUB_ACCT_NO)

SELECT ITEM#,SUB_ACCT_NO
FROM #TMPCSGACCT1 WITH(NOLOCK)
WHERE LEFT([SUB_ACCT_NO],6) IN ('849560','849575')

CREATE TABLE #TMPCSGACCT3 (
ITEM# NUMERIC (7,0),
PRIN SMALLINT,
SUB_ACCT_NO CHAR (16)
)
INSERT INTO #TMPCSGACCT3 (ITEM#,PRIN,SUB_ACCT_NO)

SELECT ITEM#,
CASE WHEN LEFT([SUB_ACCT_NO],6) = '849560' THEN '6000'
--WHEN LEFT([SUB_ACCT_NO],6) = '849574' THEN '7400'
WHEN LEFT([SUB_ACCT_NO],6) = '849575' THEN '7500'
ELSE '' END AS PRIN,
SUB_ACCT_NO

FROM #TMPCSGACCT2 WITH(NOLOCK)

CREATE TABLE #TMPCSGACCT4 (
ITEM# NUMERIC (7,0),
PRIN SMALLINT,
SUB_ACCT_NO CHAR (16),
[CUST_ACCT_NO_SBB] CHAR(13)
)

INSERT INTO #TMPCSGACCT4 (ITEM#,PRIN,SUB_ACCT_NO,[CUST_ACCT_NO_SBB])

SELECT A.ITEM#,A.PRIN,A.SUB_ACCT_NO,S.[CUST_ACCT_NO_SBB]

FROM #TMPCSGACCT3 A WITH(NOLOCK) INNER JOIN [Vantage].[dbo].[SBB_BASE] S WITH(NOLOCK)
ON A.PRIN=S.PRIN_SBB AND
A.SUB_ACCT_NO=S.SUB_ACCT_NO_SBB AND S.PRIN_SBB IN ('6000','7500')

select
h.acct_stage_ohi,
h.connect_dte_ohi,
h.disco_dte_ohi,
h.item_status_ohi

INTO #TMP1C

from #TMPCSGACCT4 as T4 (nolock) inner join vantage.dbo.ohi_hist_item as h (nolock)

ON T4.PRIN = H.PRIN_OHI
and T4.SUB_ACCT_NO = h.SUB_ACCT_NO_OHI
and T4.CUST_ACCT_NO_SBB = h.CUST_ACCT_NO_OHI

where h.DISCO_DTE_OHI > h.CONNECT_DTE_OHI
and H.DISCO_DTE_OHI = (select max(disco_dte_ohi) as disco_dte_ohi
FROM Vantage.dbo.OHI_HIST_ITEM AS B WHERE B.CUST_ACCT_NO_OHI = H.CUST_ACCT_NO_OHI)

GROUP BY
h.acct_stage_ohi,
h.connect_dte_ohi,
h.disco_dte_ohi,
h.item_status_ohi

Damian

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-14 : 13:43:32

what does the query plan show for the whole query starting from the select h.acct_stage_ohi down to the end of the group by? What type of join? I suspect that the subquery in red may be generating a nested loops operator causing that table to be read once for each row in the outer query. That may not be optimal. you could try something like:


SELECT h.acct_stage_ohi
	,h.connect_dte_ohi
	,h.disco_dte_ohi
	,h.item_status_ohi
INTO #TMP1C
FROM #TMPCSGACCT4 AS T4(NOLOCK)
INNER JOIN vantage.dbo.ohi_hist_item AS h(NOLOCK) ON T4.PRIN = H.PRIN_OHI
	AND T4.SUB_ACCT_NO = h.SUB_ACCT_NO_OHI
	AND T4.CUST_ACCT_NO_SBB = h.CUST_ACCT_NO_OHI
INNER JOIN (
	SELECT CUST_ACCT_NO_OHI
		,max(disco_dte_ohi) AS disco_dte_ohi
	FROM Vantage.dbo.OHI_HIST_ITEM
	) B ON B.CUST_ACCT_NO_OHI = H.CUST_ACCT_NO_OHI
WHERE h.DISCO_DTE_OHI > h.CONNECT_DTE_OHI
	AND H.DISCO_DTE_OHI = b.disco_dte_ohi
GROUP BY h.acct_stage_ohi
	,h.connect_dte_ohi
	,h.disco_dte_ohi
	,h.item_status_ohi

Damian39
Starting Member

33 Posts

Posted - 2014-10-14 : 15:05:19

I adjusted my query according to your suggestion, and now I am receiving an error stating the cust_acct_no_ohi is invalid in the select list because it is not contained in an aggregate function or group by clause. Would I need to add a group by within the parenthesis of that new select statement?


select
b.CUST_ACCT_NO_OHI,  
h.acct_stage_ohi,
h.connect_dte_ohi, 
h.disco_dte_ohi, 
h.item_status_ohi


INTO #TMP1C

from #TMPCSGACCT4 as T4 (nolock) inner join vantage.dbo.ohi_hist_item as h (nolock)

ON T4.PRIN = H.PRIN_OHI
and	T4.SUB_ACCT_NO = h.SUB_ACCT_NO_OHI
and T4.CUST_ACCT_NO_SBB = h.CUST_ACCT_NO_OHI

inner join (select cust_acct_no_ohi, max(disco_dte_ohi) as disco_dte_ohi 
FROM Vantage.dbo.OHI_HIST_ITEM) B ON B.CUST_ACCT_NO_OHI = H.CUST_ACCT_NO_OHI

where h.DISCO_DTE_OHI > h.CONNECT_DTE_OHI
and H.DISCO_DTE_OHI = b.disco_dte_ohi

GROUP BY
b.CUST_ACCT_NO_OHI,
h.acct_stage_ohi,
h.connect_dte_ohi, 
h.disco_dte_ohi, 
h.item_status_ohi

Damian

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-14 : 15:08:20

Yes add a group by:

inner join (select cust_acct_no_ohi, max(disco_dte_ohi) as disco_dte_ohi
FROM Vantage.dbo.OHI_HIST_ITEM GROUP BY cust_acct_no_ohi) B ON B.CUST_ACCT_NO_OHI = H.CUST_ACCT_NO_OHI

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources