October 3, 2015 at 10:00 am
Hi Any one can help me on this below query. its getting more time to exec and got deadlock. So Plz help me on this query to avoid deadlock.
SELECT m1.Value AS InterfaceName, m1.MessageDateTime, m2.GroupId, COUNT(mError.Id) AS ErrorCount
FROM (
SELECT m1.Value, MAX(m1.MessageDateTime) as MessageDateTime FROM Message m1
WHERE m1.TypeId = 9 AND (m1.Value LIKE 'F02' )
GROUP BY m1.Value
) AS m1
JOIN Message m2 ON m1.MessageDateTime = m2.MessageDateTime AND m1.Value = m2.Value AND m2.TypeId = 9
LEFT JOIN Message mError ON mError.GroupId = m2.GroupId AND mError.TypeId = 1
GROUP BY m1.Value, m1.MessageDateTime, m2.GroupId
ORDER BY m1.Value
Thanks
AK
October 3, 2015 at 2:06 pm
Table definitions, index definitions, execution plan (as a .sqlplan file) and the deadlock graph please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2015 at 8:28 am
I would try two compound indexes on the Message table.
CREATE INDEX IX_Message ON [Message](Value asc ,TypeId asc )
CREATE INDEX IX_Message_1 ON [Message](GroupId asc ,TypeId asc)
This should make the query a lot quicker (maybe 10 times the current speed)
I also rewrote your query just so I could get a better idea about what it's doingL
;WITH m1 AS
(
SELECT m1.Value,
MAX(m1.MessageDateTime) as MessageDateTime
FROM Message m1
WHERE m1.TypeId = 9
AND m1.Value LIKE 'F02'
GROUP BY m1.Value
)
SELECT m1.Value AS InterfaceName,
m1.MessageDateTime,
m2.GroupId,
COUNT(mError.Id) AS ErrorCount
FROM m1
INNER JOIN Message m2
ON m1.MessageDateTime = m2.MessageDateTime
AND m1.Value = m2.Value
AND m2.TypeId = 9
LEFT JOIN Message mError
ON mError.GroupId = m2.GroupId
AND mError.TypeId = 1
GROUP BY m1.Value,
m1.MessageDateTime,
m2.GroupId
ORDER BY m1.Value
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply