DECLARE
@l_DefDate DATE
, @l_DefStr VARCHAR(1)
, @l_DefDec DECIMAL(1, 0)
, @l_LogId INT
SET @l_DefDate = CONVERT(DATE, '1901-01-01', 121)
SET @l_DefStr = ''
SET @l_DefDec = 0
SET @p_IdType = COALESCE(@p_IdType, 'MasterPortfolioId')
SET @p_Content = COALESCE(@p_Content,'PortfolioHistorical')
IF @p_LowRunId IS NULL
OR @p_HighRunId IS NULL
RETURN 'Must supply valid low and high run numbers';
CREATE TABLE #temp (
[PortfolioStatisticsId] [int] NOT NULL,
[Date] [date] NOT NULL,
[Type] [INT] NOT NULL,
[BreakdownValue] [decimal](9, 5) NULL
)
INSERT INTO #temp (
PortfolioStatisticsId
, [Date]
, [Type]
, [BreakdownValue]
)
SELECT Aggr.PortfolioStatisticsId AS PortfolioStatisticsId
, F.[Date] AS [Date]
, F.[Type] AS [Type]
, F.BreakdownValue AS BreakdownValue
FROM
( SELECT meta.ItemId as MasterPortfolioId
, meta.[Date] as PortfolioDate
, xmd4.NodeValue as 'SalePosition'
, C.[Date]
, C.[Type]
, C.BreakdownValue
FROM
(
SELECT
B.*
, xmd3.NodeValue AS [Date]
FROM
(SELECT A.DocumentId
, A.[Type]
, xmd2.NodeValue as BreakdownValue
, xmd2.Node.GetAncestor(1) as Asc2
FROM
(
SELECT
m.DocumentId
, xmd.NodeValue AS [Type]
, xmd.Node.GetAncestor(1) AS Asc1
FROM XOIConfig..Metadata m
INNER JOIN [RawXML]..XMLDownload_DUMP_ID xmd
ON m.DocumentId = xmd.DocumentID
INNER JOIN [RawXML]..XMLNodeName xmn
ON xmd.NodeNameID = xmn.ID
INNER JOIN [RawXML]..XMLDownload_DUMP_ID xmd1
ON xmd.DocumentID = xmd1.DocumentID
AND xmd.Node.GetAncestor(2) = xmd1.Node
INNER JOIN [RawXML]..XMLNodeName xmn1
ON xmd1.NodeNameID = xmn1.ID
WHERE
m.[Content] = @p_Content
AND m.[IdType] = @p_IdType
AND m.[RunId] BETWEEN @p_LowRunId AND @p_HighRunId
AND xmn1.Value = 'CreditQualityBreakdown'
AND xmn.Value = 'Type'
AND xmd.Node.GetLevel() = 5
) A
INNER JOIN [RawXML]..XMLDownload_DUMP_ID xmd2
ON xmd2.Node = A.Asc1 and xmd2.DocumentID = A.DocumentId
INNER JOIN [RawXML]..XMLNodeName xmn2
ON xmd2.NodeNameID = xmn2.ID
WHERE xmn2.Value = 'BreakdownValue'
) B
INNER JOIN [RawXML]..XMLDownload_DUMP_ID xmd3
ON xmd3.Node.GetAncestor(1) = B.Asc2
AND xmd3.DocumentID = B.DocumentId
INNER JOIN [RawXML]..XMLNodeName xmn3
ON xmd3.NodeNameID = xmn3.ID
WHERE xmn3.Value = 'Date'
) C
INNER JOIN [RawXML]..XMLDownload_DUMP_ID xmd4 ON xmd4.DocumentID = C.DocumentId AND xmd4.Node.GetAncestor(1) = C.Asc2.GetAncestor(1)
INNER JOIN [RawXML]..XMLNodeName xmn4 on xmd4.NodeNameID = xmn4.ID
INNER JOIN XOIConfig..Metadata meta on meta.DocumentId = xmd4.DocumentID
WHERE xmn4.Value = '_SalePosition'
) F
INNER JOIN XMLDownload..PortfolioMain pm ON pm.MasterPortfolioId = F.MasterPortfolioId
AND pm.PortfolioDate = F.PortfolioDate
INNER JOIN XMLDownload..PortfolioSummary_HoldingAggregate Aggr
ON Aggr.PortfolioId = pm.PortfolioId
AND Aggr.SalePosition = F.SalePosition;
WITH TGT AS
( SELECT tt.* FROM [XMLDownload].dbo.PortfolioBreakdown_CreditQuality tt
INNER JOIN [XMLDownload].dbo.PortfolioSummary_HoldingAggregate aggr
ON aggr.PortfolioStatisticsId = tt.PortfolioStatisticsId
INNER JOIN [XMLDownload].dbo.PortfolioMain pm
ON aggr.PortfolioId = pm.PortfolioId
INNER JOIN XOIConfig..Metadata m
ON pm.MasterPortfolioId = m.ItemId AND pm.PortfolioDate = m.[Date]
WHERE m.[Content] = @p_Content
AND m.[IdType] = @p_IdType
AND m.[RunId] BETWEEN @p_LowRunId AND @p_HighRunId )
MERGE TGT AS f
USING (
SELECT * FROM #temp
) AS xmlf
ON f.PortfolioStatisticsId = xmlf.PortfolioStatisticsId
AND f.[Type] = xmlf.[Type]
AND f.[Date] = xmlf.[Date]
WHEN MATCHED
AND ( f.BreakdownValue ! = xmlf.BreakdownValue )
THEN
UPDATE SET f.BreakdownValue = xmlf.BreakdownValue
, [TimeStamp] = GETDATE()
WHEN NOT MATCHED BY TARGET
THEN
INSERT ( PortfolioStatisticsId
, [Type]
, [Date]
, BreakdownValue
, [TimeStamp])
VALUES ( xmlf.PortfolioStatisticsId
, xmlf.[Type]
, xmlf.[Date]
, xmlf.BreakdownValue
, GETDATE() )
WHEN NOT MATCHED BY SOURCE
THEN
DELETE 怎么优化 度慢的递归表查询?
--#2.看着代码，也没什么，根据表之间的关联，建立相应的索引
DocumentId
, ItemId
, [Date]
, AncestorName
, AncestorNode
, Node
, NodeValue
, NodeName)
AS (
SELECT
[m].[DocumentId]
, [m].[ItemId] COLLATE Latin1_General_BIN
, [m].[Date] COLLATE Latin1_General_BIN
, CAST(NULL AS VARCHAR(50)) COLLATE Latin1_General_BIN AS AncestorName
, xddi.Node.GetAncestor(1) AS AncestorNode
, xddi.Node
, [xddi].NodeValue COLLATE Latin1_General_BIN
, [xnn].[Value] COLLATE Latin1_General_BIN
FROM [XOIConfig]..[Metadata] m WITH (NOLOCK)
INNER JOIN [RawXML]..[XMLDownload_DUMP_ID] xddi WITH (NOLOCK)
ON [m].[DocumentId] = [xddi].[DocumentID]
INNER JOIN [RawXML]..[XMLNodeName] xnn WITH (NOLOCK)
ON [xddi].[NodeNameID] = [xnn].[ID]
INNER JOIN [RawXML]..[XMLDownload_DUMP_ID] xddi2 WITH (NOLOCK)
ON [m].[DocumentId] = [xddi2].[DocumentID]
AND xddi2.[Node] = [xddi].[Node].GetAncestor(1)
INNER JOIN [RawXML]..[XMLNodeName] xnn2 WITH (NOLOCK)
ON [xddi2].[NodeNameID] = [xnn2].[ID]
WHERE m.[IdType] = @p_IdType
AND m.[Content] = @p_Content
AND m.[RunId] BETWEEN @p_LowRunId AND @p_HighRunId
AND [xnn].[Value] = 'HoldingDetail'
AND [xnn2].[Value] = 'Holding'
UNION ALL
SELECT
[xddi].[DocumentID] AS DocumentId
, [xml].[ItemId]
, [xml].[Date] COLLATE Latin1_General_BIN
, [xml].[NodeName] AS AncestorName
, Cast([xml].[Node] as hierarchyid) AS AncestorNode
, [xddi].[Node]
, [xddi].[NodeValue]
, [xnn].[Value]
FROM [xml]
INNER JOIN [RawXML]..[XMLDownload_DUMP_ID] xddi WITH (NOLOCK)
ON [xml].[DocumentId] = [xddi].[DocumentID]
AND [xml].Node = xddi.Node.GetAncestor(1)
INNER JOIN [RawXML]..[XMLNodeName] xnn WITH (NOLOCK)
ON [xddi].[NodeNameID] = [xnn].[ID] )