SQL Server Performance

blocking qureies

Discussion in 'ALL SQL SERVER QUESTIONS' started by ADE calker, Nov 14, 2013.

  1. ADE calker New Member

    i have this queries that are block , i have rewritten them , any suggestions to make it better
    BLOCKER
    insert into BREx.BusinessRuleEngine
    ( BSSCommOrder_OrderXID,
    BusinessRuleEngineDefinitionID,
    BusinessRuleEngineDefinitionSet,
    Live,
    BusinessRuleEngineActionTypeID,
    BusinessRuleEngineStatusTypeID
    )
    --now check based on last order attached to customer
    select c.BSSCommOrder_OrderXID,
    c.BusinessRuleEngineDefinitionID,
    c.BusinessRuleEngineDefinitionSet,
    bed.Live,
    bed.BusinessRuleEngineActionTypeID ,
    1 --To be determined.
    from breX.[vwBusinessRuleEngine_ChecksBasedOnOrder] c
    join breX.BusinessRuleEngineDefinition bed (readuncommitted) on c.BusinessRuleEngineDefinitionID = bed.BusinessRuleEngineDefinitionID
    where c.Status_BusinessRuleEngineDefinitionCheckSet=1

    The querry that the above BRE querry blocks is as below

    BLOCKING
    1. The SQL below is from the Processor

    INSERT INTO @BEMain (
    BusinessExceptionID ,
    BE_ETS_LinkGroupID ,
    BE_ETS_LinkRuleID ,
    LinkItemTypeID ,
    beValue ,
    CompareValue ,
    MainCaseNum
    )
    SELECT bus.BusinessExceptionID,
    bus.BE_ETS_LinkGroupID,
    bus.BE_ETS_LinkRuleID,
    bus.LinkItemTypeID,
    bus.beValue,
    mc.CompareValue,
    mc.MainCaseNum
    FROM ( -- Get BusinessExceptionData like parameter, processname etc
    SELECT bec.BusinessExceptionID,
    belr.BE_ETS_LinkGroupID ,
    belr.BE_ETS_LinkRuleID ,
    belr.LinkItemTypeID ,
    ipeX.fnBE_ETS_GetLinkItemValue(
    belr.LinkItemTypeID,
    belr.LinkItemValue,
    bec.BusinessExceptionID
    ) AS beValue
    FROM be.BusinessException bec WITH (READUNCOMMITTED)
    JOIN ipeX.BE_ETS_LinkRule belr WITH (READUNCOMMITTED) ON belr.BE_ETS_LinkRuleID IS NOT NULL
    JOIN ipeX.vwBE_ETS_LinkSelection vbels WITH (READUNCOMMITTED) ON bec.BusinessExceptionID = vbels.BusinessExceptionID
    ) AS bus
    CROSS APPLY ipeX.fnBE_ETS_MatchLinkItemValueToMaincase(bus.BE_ETS_LinkRuleID, SUBSTRING(bus.beValue, 1, 60)) mc
  2. davidfarr Member

    Most of the objects being referenced in the two queries above are reading and writing to completely different tables, and therefore should not block each other, with the possible exception of this:
    Code:
    ipeX.fnBE_ETS_MatchLinkItemValueToMaincase(bus.BE_ETS_LinkRuleID, SUBSTRING(bus.beValue, 1, 60))
    
    What does that function do ? If that function is referencing the same objects as the 'blocking' query then that may explain why it is being blocked.

    With regard to both queries; I cannot see your table structures or the data in them, and I do not know your business requirements for the query. For this reason I cannot advise you on improvements to either query.

Share This Page