November 14, 2013 at 2:17 pm
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
November 14, 2013 at 9:10 pm
You have views and functions in play here, you haven't given us any information about the tables involved, the indexing on those tables, the data value skew situation, row counts, query plans - pretty much nothing useful for us to help you with.
Search for guidance on how to post performance issues so you can helps us help you.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply