Sql DeadLock in SubQuery with Selfjoin

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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