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
 how long derived table last?

Author  Topic 

CleoTao
Starting Member

11 Posts

Posted - 2009-12-29 : 20:04:55
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

5072 Posts

Posted - 2009-12-29 : 21:52:35
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...
Go to Top of Page

CleoTao
Starting Member

11 Posts

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-30 : 08:09:27
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
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-30 : 08:10:47
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 - 2009-12-30 : 11:39:42
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 - 2009-12-30 : 11:43:27
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
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-30 : 11:50:24
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
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-30 : 11:58:31
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 - 2009-12-30 : 12:09:03
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
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-30 : 12:14:01
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
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-30 : 12:22:15
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 - 2009-12-30 : 12:46:48
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 - 2009-12-30 : 12:52:08
now 10mins passed, still running, still no good
Go to Top of Page

CleoTao
Starting Member

11 Posts

Posted - 2009-12-30 : 16:06:43
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

38200 Posts

Posted - 2009-12-30 : 16:12:37
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 - 2009-12-30 : 16:52:28
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

38200 Posts

Posted - 2009-12-30 : 17:08:17
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 - 2009-12-30 : 17:10:43
so above query should work ok, right?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-30 : 17:11:14
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 - 2009-12-30 : 17:16:47
you mean "out"? that is typo

it should be "left outer join", except that, it should have no problem?
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -