SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how long derived table last?
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

CleoTao
Starting Member

11 Posts

Posted - 12/29/2009 :  20:04:55  Show Profile  Reply with Quote
i wrote a query like

select table1.*, table2.*
from
(select * from a where condition1)table1
left outer join
(select * from b where condition2)table2
on table1.XX = table2.XX
union all
select table1.*, table2.*
from
(select * from c where condition3)table1
left outer join
(select * from d where condition4)table2
on table1.DD = table2.DD

this query sometimes works ok around 5mins, sometimes take 15mins, sometimes, 1hour

i am trying to optimize the query, i have two questions here

1. apparently, i can reuse table1 and table2 as alias name, so my questin is how long the derived table last?

2. if at the same time, another transaction use derived table with the same name of mine, will they cause deadlock?

can someone please help this?

thanks

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 12/29/2009 :  21:52:35  Show Profile  Visit russell's Homepage  Reply with Quote
1. it only lasts for the duration (scope) of the query
2. no. we can all alias our derived tables or correlated subqueries with the same name and they would not conflict

you need to identify what is making it take so long. check exec plan, blocking, indexes...

Edited by - russell on 12/29/2009 21:53:27
Go to Top of Page

CleoTao
Starting Member

11 Posts

Posted - 12/30/2009 :  08:06:45  Show Profile  Reply with Quote
the problem is sometimes this select query will block other UPDATE statement, how is that possible?
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 12/30/2009 :  08:09:27  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
what isolation level are you using. by default the isolation level is read committed which means that reads can conflic with writes (updateS)

In 2005 and up ms introduced an isolation level called READ COMMITTED SNAPSHOT and under that isolation level the reads don't conflict.

I'd recommend testing with the new isolation level.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 12/30/2009 :  08:10:47  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Also -- you've abviously given us a stripped out version of your query. If you post the exact query then we'll be able to give you some better pointers. It's hard to recommend a particular index strategy when we don't know anything about your data and query.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

CleoTao
Starting Member

11 Posts

Posted - 12/30/2009 :  11:39:42  Show Profile  Reply with Quote
here is the part of query, the whole query will have two "union all" with other two parts which criteria are different, but most of them are similar

i found if only this query will work very good, take around 2mins, if union all other two, it will take more time

select 'XX' as LOB, HandleTimeHalf,table5.DateTime,'0' as CallTypeID, CallsOfferedHalf,
CallsHandledHalf, 'YY' as Enterprise, loggedOnTimeToHalf, NotReadyTimeToHalf, RouterQueueCallsToHalf, TalkTimeHalf,
RouterQueueWaitTimeToHalf, AnsInterval6, HoldTimeToHalf,
TotalCallsAbandToHalf, TotalAbandonMinusServiceAban, CallsHandleMinusSericeCallsHandle, AbandInterval6,
AbandInterval7, AbandInterval8, AbandInterval9, AbandInterval10,
AnsInterval7, AnsInterval8,AnsInterval9, AnsInterval10,
WorkNotReadyTimeToHalf, sHoldTimeToHalf, TalkInTimeToHalf,
TalkTimeToHalf, CallsAnsweredToHalf, AvailTimeToHalf, 1234 as AgentTeamID, TeamACW, '0' as ShortCallsHalf,
'0' as AnswerWaitTimeHalf
from
(select DateTime,sum(TeamACW) as TeamACW
from
(select table1.DateTime, case when TeamACW is null then '0' else TeamACW end as TeamACW, HandledTime
from
(select DateTime, SUM(WorkNotReadyTimeToHalf) as TeamACWOld, 1234 as CallTypeID from ABCD with (nolock)
where SkillID in (1,2,3,4,5) and DateTime>='09/01/09'
group by DateTime
union all
select DateTime, SUM(WorkNotReadyTimeToHalf) as TeamACWOld, 5678 as CallTypeID from ABCD with (nolock)
where SkillID in (6,7,8,9,10) and DateTime>='09/01/09'
group by DateTime
union all
select DateTime, SUM(WorkNotReadyTimeToHalf) as TeamACWOld, 1212 as CallTypeID from ABCD with (nolock)
where SkillID in (11,12) and DateTime>='09/01/09'
group by DateTime
union all
select DateTime, SUM(WorkNotReadyTimeToHalf) as TeamACWOld, 3434 as CallTypeID from ABCD with (nolock)
where SkillID in (13,14) and DateTime>='09/01/09'
group by DateTime
)table1
left outer join
(select DateTime, sum(HandleTimeHalf) as HandledTime,sum(HandleTimeHalf-TalkTimeHalf-HoldTimeToHalf) as TeamACW, CallTypeID
from EFGH with (nolock)
where CallID in (1234,5678,1212,3434) and DateTime>='09/01/09' group by DateTime, CallTypeID
)table2
on table1.DateTime=table2.DateTime and table1.CallTypeID = table2.CallTypeID
) ctemp3 group by DateTime
)table5
left outer join
(select 'XX' as LOB, '0' AS HandleTimeHalf, DateTime, '0' as CallTypeID, '0' AS CallsOfferedHalf,
'0' AS CallsHandledHalf, 'YY' as Enterprise, SUM(LoggedOnTimeToHalf) AS loggedOnTimeToHalf,
sum(NotReadyTimeToHalf) as NotReadyTimeToHalf, '0' as RouterQueueCallsToHalf, '0' as TalkTimeHalf,
'0' as RouterQueueWaitTimeToHalf, '0' as AnsInterval6, '0' as HoldTimeToHalf, '0' as TotalCallsAbandToHalf, '0' as TotalAbandonMinusServiceAban, '0' as CallsHandleMinusSericeCallsHandle, '0' as AbandInterval6,
'0' as AbandInterval7, '0' as AbandInterval8, '0' as AbandInterval9, '0' as AbandInterval10,
'0' as AnsInterval7, '0' as AnsInterval8, '0' as AnsInterval9, '0' as AnsInterval10, '0' as WorkNotReadyTimeToHalf, '0' as sHoldTimeToHalf, '0' as TalkInTimeToHalf, '0' as TalkTimeToHalf, '0' as CallsAnsweredToHalf, sum(AvailTimeToHalf) as AvailTimeToHalf
from ABAB with (nolock), CDCD with (nolock)
where ABAB.ID = CDCD.ID and CDCD.ID3 in (1000,10001,1002,1003)
and datepart(hh, DateTime) between 6 and 21 and datename(dw, DateTime) not in ('Sunday', 'Saturday') and DateTime>='09/01/09'
group by DateTime)table6 on table5.DateTime = table6.DateTime
Go to Top of Page

CleoTao
Starting Member

11 Posts

Posted - 12/30/2009 :  11:43:27  Show Profile  Reply with Quote
please note this query is pulled the data from View, i dont think there is index on the view and could you let me know how i can check what level we are using now?

Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 12/30/2009 :  11:50:24  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
SELECT
[name]
, [Is_read_committed_snapshot_on]
FROM
sys.databases


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 12/30/2009 :  11:58:31  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
you have a column called Datetime? And you have tables with meaningless names like ABCD?

your code is really horrible -- all the repeated sub queries (that are all variations on a theme UNION'ed together are really going to destroy performace)

I think it might be time just to go back to the start and not try to change this. Start again.

1) what environment are you on (2000, 2005, 2008?)
2) What are you actually trying to do?

If you state the aim, give some sample data with table structure if possible, and the required output from that sample data then I'm sure this can be redesigned in a nicer way.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

CleoTao
Starting Member

11 Posts

Posted - 12/30/2009 :  12:09:03  Show Profile  Reply with Quote
this query is revised couple of times per the requst from the client, so i normally not change the whole query, only change the part i need to change

basically, this query can finish in 5mins, sometimes in 15mins, sometimes worse

i need to provide every 30 mins data from the table EFGH and ABAB, however these two tables only will have the data starting from the work time, after work time, no interval data in it, another table ABCD hold all interval data even they are ZERO, so i use DateTime from ABCD to join other two

why too many UNION ALL will cause the issue?
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 12/30/2009 :  12:14:01  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
every time you add another union then you are performing another query on that table. Potentially another table scan each time. I don't think you need any of the unions in the first section -- I think using CASE you can get them all into one derived table.

I think this is your query with some formatting No actual changes yet:

SELECT
	'XX' AS LOB
	, HandleTimeHalf
	, table5.DateTime
	, '0' AS CallTypeID
	, CallsOfferedHalf
	, CallsHandledHalf
	, 'YY' AS Enterprise
	, loggedOnTimeToHalf
	, NotReadyTimeToHalf
	, RouterQueueCallsToHalf
	, TalkTimeHalf
	, RouterQueueWaitTimeToHalf
	, AnsInterval6
	, HoldTimeToHalf
	, TotalCallsAbandToHalf
	, TotalAbandonMinusServiceAban
	, CallsHandleMinusSericeCallsHandle
	, AbandInterval6
	, AbandInterval7
	, AbandInterval8
	, AbandInterval9
	, AbandInterval10
	, AnsInterval7
	, AnsInterval8
	, AnsInterval9
	, AnsInterval10
	, WorkNotReadyTimeToHalf
	, sHoldTimeToHalf
	, TalkInTimeToHalf
	, TalkTimeToHalf
	, CallsAnsweredToHalf
	, AvailTimeToHalf
	, 1234 AS AgentTeamID
	, TeamACW
	, '0' AS ShortCallsHalf, 
	'0' AS AnswerWaitTimeHalf
FROM 
	(
	SELECT
		DATETIME
		, SUM(TeamACW) AS TeamACW
	FROM 
		(
		SELECT
			table1.DateTime
			, CASE WHEN TeamACW IS NULL THEN '0' ELSE TeamACW END AS TeamACW
			, HandledTime
		FROM
			(
			SELECT
				DATETIME
				, SUM(WorkNotReadyTimeToHalf) AS TeamACWOld
				, 1234 AS CallTypeID
			FROM
				ABCD WITH (NOLOCK)
			WHERE
				SkillID IN (1,2,3,4,5)
				AND DATETIME >='09/01/09'
			GROUP BY
				DATETIME

			UNION ALL SELECT
				DATETIME
				, SUM(WorkNotReadyTimeToHalf) AS TeamACWOld
				, 5678 AS CallTypeID
			FROM
				ABCD WITH (NOLOCK)
			WHERE
				SkillID IN (6,7,8,9,10)
				AND DATETIME >='09/01/09'
			GROUP BY
				DATETIME

			UNION ALL SELECT
				DATETIME
				, SUM(WorkNotReadyTimeToHalf) AS TeamACWOld
				, 1212 AS CallTypeID
			FROM
				ABCD WITH (NOLOCK)
			WHERE
				SkillID IN (11,12)
				AND DATETIME>='09/01/09'
			GROUP BY
				DATETIME

			UNION ALL SELECT DATETIME
				, SUM(WorkNotReadyTimeToHalf) AS TeamACWOld
				, 3434 AS CallTypeID
			FROM
				ABCD WITH (NOLOCK)
			WHERE
				SkillID IN (13,14)
				AND DATETIME>='09/01/09'
			GROUP BY
				DATETIME
			)
			table1 

			LEFT OUTER JOIN (
				SELECT
					DATETIME
					, SUM(HandleTimeHalf) AS HandledTime
					, SUM(HandleTimeHalf-TalkTimeHalf-HoldTimeToHalf) AS TeamACW
					, CallTypeID
				FROM
					EFGH WITH (NOLOCK)
				WHERE
					CallID IN (1234,5678,1212,3434)
					AND DATETIME>='09/01/09'
				GROUP BY
					DATETIME
					, CallTypeID
			)
			table2 ON
				table1.DateTime = table2.DateTime
				AND table1.CallTypeID = table2.CallTypeID
		)
		ctemp3
	GROUP BY
		DATETIME
	)
	table5

	LEFT OUTER JOIN (
		SELECT
			'XX' AS LOB
			, '0' AS HandleTimeHalf
			, DATETIME
			, '0' AS CallTypeID
			, '0' AS CallsOfferedHalf
			, '0' AS CallsHandledHalf
			, 'YY' AS Enterprise
			, SUM(LoggedOnTimeToHalf) AS loggedOnTimeToHalf
			, SUM(NotReadyTimeToHalf) AS NotReadyTimeToHalf
			, '0' AS RouterQueueCallsToHalf
			, '0' AS TalkTimeHalf
			, '0' AS RouterQueueWaitTimeToHalf
			, '0' AS AnsInterval6
			, '0' AS HoldTimeToHalf
			, '0' AS TotalCallsAbandToHalf
			, '0' AS TotalAbandonMinusServiceAban
			, '0' AS CallsHandleMinusSericeCallsHandle
			, '0' AS AbandInterval6
			, '0' AS AbandInterval7
			, '0' AS AbandInterval8
			, '0' AS AbandInterval9
			, '0' AS AbandInterval10
			, '0' AS AnsInterval7
			, '0' AS AnsInterval8
			, '0' AS AnsInterval9
			, '0' AS AnsInterval10
			, '0' AS WorkNotReadyTimeToHalf
			, '0' AS sHoldTimeToHalf
			, '0' AS TalkInTimeToHalf
			, '0' AS TalkTimeToHalf
			, '0' AS CallsAnsweredToHalf
			, SUM(AvailTimeToHalf) AS AvailTimeToHalf
		FROM
			ABAB WITH (NOLOCK)
			, CDCD WITH (NOLOCK)
		WHERE
			ABAB.ID = CDCD.ID
			AND CDCD.ID3 IN (1000,10001,1002,1003)
			AND DATEPART(hh, DATETIME) BETWEEN 6 AND 21
			AND DATENAME(dw, DATETIME) NOT IN ('Sunday', 'Saturday')
			AND DATETIME >= '09/01/09'
		GROUP BY
			DATETIME
		)
		table6 ON table5.DateTime = table6.DateTime



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 12/30/2009 :  12:22:15  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
I think you can replace all the UNION's in table 1 with this block of code instead.

SELECT
	[datetime]
	, SUM([workNotReadyTimeTohalf]) AS [TeamACWOld]
	, [callTypeId]
FROM
	(
	SELECT
		[datetime]
		, [workNotReadyTimeTohalf]
		, CASE
			WHEN [SkillID] IN (1, 2, 3, 4, 5) THEN 1234
			WHEN [SkillID] IN (6, 7, 8, 9, 10) THEN 5678
			WHEN [SkillID] IN (11, 12) THEN 1212
			WHEN [SkillID] IN (13, 14) THEN 3434
		END AS [callTypeID]
	FROM
		ABCD
	WHERE
		[datetime] >= '20090901' --1st Sept 2009 ?? imposibile to tell from your string 09/01/09 (UK / US?)
		AND [skillID] BETWEEN 1 and 14
	)
	skillMap
GROUP BY
	[datetime]
	, [callTypeId]



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

CleoTao
Starting Member

11 Posts

Posted - 12/30/2009 :  12:46:48  Show Profile  Reply with Quote
actually, i change the table1 code to

select table1.DateTime, case when TeamACW is null then '0' else TeamACW end as TeamACW, HandledTime
from
(select DateTime, SUM(WorkNotReadyTimeToHalf) as TeamACWOld
from ABCD with (nolock)
where SkillID in (1,2,3,4) and DateTime>='09/01/09'
group by DateTime)table1
left outer join
(select DateTime, sum(HandleTimeHalf) as HandledTime,sum(HandleTimeHalf-TalkTimeHalf-HoldTimeToHalf) as TeamACW
from EFGH with (nolock) where CallID in (10,11) and DateTime>='09/01/09'
group by DateTime)table2 on table1.DateTime=table2.DateTime
)table1

and i am testing it to see if it will speed a little
Go to Top of Page

CleoTao
Starting Member

11 Posts

Posted - 12/30/2009 :  12:52:08  Show Profile  Reply with Quote
now 10mins passed, still running, still no good
Go to Top of Page

CleoTao
Starting Member

11 Posts

Posted - 12/30/2009 :  16:06:43  Show Profile  Reply with Quote
i dont think a query could cause so big difference, sometimes, 5mins, sometimes 20mins, sometimes, worse 1 to 2 hours

now i believe the issue is at the server side
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37159 Posts

Posted - 12/30/2009 :  16:12:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
Are the tables indexed? How big are they? When was the last time you ran update stats and alter index?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

CleoTao
Starting Member

11 Posts

Posted - 12/30/2009 :  16:52:28  Show Profile  Reply with Quote
when you said the derived table only last in the scope, what exactly does that mean?

if the query
select table1.A,C,D,E,F,G
from
select table1.A,C,D
from
(select A, B
from tableA)table1
left out join
(select A,C,D
from tableB)table2 on table1.A = table2.A
)table1 left out join
(select A,E,F,G
from tableC)table2 on table1.A= table2.A

there are two table1 and table2, how long each of them last?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37159 Posts

Posted - 12/30/2009 :  17:08:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
Just until the derived table completes and returns the needed data. So the alias can be reused since it no longer exists.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

CleoTao
Starting Member

11 Posts

Posted - 12/30/2009 :  17:10:43  Show Profile  Reply with Quote
so above query should work ok, right?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37159 Posts

Posted - 12/30/2009 :  17:11:14  Show Profile  Visit tkizer's Homepage  Reply with Quote
Well it has syntax errors, so no.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

CleoTao
Starting Member

11 Posts

Posted - 12/30/2009 :  17:16:47  Show Profile  Reply with Quote
you mean "out"? that is typo

it should be "left outer join", except that, it should have no problem?
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000