Author |
Topic |
CleoTao
Starting Member
11 Posts |
Posted - 2009-12-29 : 20:04:55
|
i wrote a query likeselect table1.*, table2.*from(select * from a where condition1)table1left outer join(select * from b where condition2)table2on table1.XX = table2.XXunion allselect table1.*, table2.*from (select * from c where condition3)table1left outer join(select * from d where condition4)table2on table1.DD = table2.DDthis query sometimes works ok around 5mins, sometimes take 15mins, sometimes, 1houri am trying to optimize the query, i have two questions here1. 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 query2. no. we can all alias our derived tables or correlated subqueries with the same name and they would not conflictyou need to identify what is making it take so long. check exec plan, blocking, indexes... |
|
|
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? |
|
|
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 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
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 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
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 similari found if only this query will work very good, take around 2mins, if union all other two, it will take more timeselect '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 AnswerWaitTimeHalffrom (select DateTime,sum(TeamACW) as TeamACWfrom (select table1.DateTime, case when TeamACW is null then '0' else TeamACW end as TeamACW, HandledTimefrom(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 DateTimeunion 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 DateTimeunion 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 DateTimeunion 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, CallTypeIDfrom 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)table5left 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 AvailTimeToHalffrom 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 - 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? |
|
|
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.databasesCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
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 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
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 changebasically, this query can finish in 5mins, sometimes in 15mins, sometimes worsei 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 twowhy too many UNION ALL will cause the issue? |
|
|
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 AnswerWaitTimeHalfFROM ( 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 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
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 ) skillMapGROUP BY [datetime] , [callTypeId] Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
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, HandledTimefrom(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 TeamACWfrom EFGH with (nolock) where CallID in (10,11) and DateTime>='09/01/09'group by DateTime)table2 on table1.DateTime=table2.DateTime)table1and i am testing it to see if it will speed a little |
|
|
CleoTao
Starting Member
11 Posts |
Posted - 2009-12-30 : 12:52:08
|
now 10mins passed, still running, still no good |
|
|
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 hoursnow i believe the issue is at the server side |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
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 queryselect table1.A,C,D,E,F,Gfromselect table1.A,C,Dfrom (select A, Bfrom tableA)table1left out join(select A,C,Dfrom tableB)table2 on table1.A = table2.A)table1 left out join(select A,E,F,Gfrom tableC)table2 on table1.A= table2.Athere are two table1 and table2, how long each of them last? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
CleoTao
Starting Member
11 Posts |
Posted - 2009-12-30 : 17:10:43
|
so above query should work ok, right? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
CleoTao
Starting Member
11 Posts |
Posted - 2009-12-30 : 17:16:47
|
you mean "out"? that is typoit should be "left outer join", except that, it should have no problem? |
|
|
Previous Page&nsp;
Next Page
|
|
|