| Author |
 Topic  |
|
CleoTao
Starting Member
11 Posts |
 Posted - 12/29/2009 : 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
USA
5072 Posts |
Posted - 12/29/2009 : 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... |
Edited by - russell on 12/29/2009 21:53:27 |
 |
|
|
CleoTao
Starting Member
11 Posts |
Posted - 12/30/2009 : 08:06:45
|
| the problem is sometimes this select query will block other UPDATE statement, how is that possible? |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3451 Posts |
Posted - 12/30/2009 : 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 |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3451 Posts |
Posted - 12/30/2009 : 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 |
|
|
|
CleoTao
Starting Member
11 Posts |
Posted - 12/30/2009 : 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
|
|
|
|
CleoTao
Starting Member
11 Posts |
Posted - 12/30/2009 : 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?
|
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3451 Posts |
Posted - 12/30/2009 : 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 |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3451 Posts |
Posted - 12/30/2009 : 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 |
|
|
|
CleoTao
Starting Member
11 Posts |
Posted - 12/30/2009 : 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? |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3451 Posts |
Posted - 12/30/2009 : 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 |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3451 Posts |
Posted - 12/30/2009 : 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 |
|
|
|
CleoTao
Starting Member
11 Posts |
Posted - 12/30/2009 : 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 |
|
|
|
CleoTao
Starting Member
11 Posts |
Posted - 12/30/2009 : 12:52:08
|
| now 10mins passed, still running, still no good |
|
|
|
CleoTao
Starting Member
11 Posts |
Posted - 12/30/2009 : 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 |
|
|
|
tkizer
Almighty SQL Goddess
USA
38200 Posts |
|
|
CleoTao
Starting Member
11 Posts |
Posted - 12/30/2009 : 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? |
|
|
|
tkizer
Almighty SQL Goddess
USA
38200 Posts |
|
|
CleoTao
Starting Member
11 Posts |
Posted - 12/30/2009 : 17:10:43
|
| so above query should work ok, right? |
|
|
|
tkizer
Almighty SQL Goddess
USA
38200 Posts |
|
|
CleoTao
Starting Member
11 Posts |
Posted - 12/30/2009 : 17:16:47
|
you mean "out"? that is typo
it should be "left outer join", except that, it should have no problem? |
|
|
|
Topic |
|
|
|
how long derived table last?