SQL -将跟踪表转换为运行时统计信息
我有一个以下格式的跟踪表。
CREATE TABLE [dbo].[trace](
[trcId] [bigint] IDENTITY(1,1) NOT NULL,
[trcDateTime] [datetime] NULL,
[trcProgram] [nvarchar](150) NULL,
[trcCode] [nvarchar](8) NULL,
[trcText] [nvarchar](max) NULL,
[trcXML] [nvarchar](max) NULL,
[trcCorrGuid] [nvarchar](36) NULL, ...现在我意识到，我可以使用从跟踪中选择的行来分析响应时间。我想创建另一个表，如下所示：
CREATE TABLE [dbo].[executionHistory](
trcCorrId [nvarchar](36) NOT NULL,
startHIP datetime NOT NULL,
stopHIP datetime NOT NULL,
startOrch1 datetime NOT NULL,
stopOrch1 datetime NOT NULL,
startOrch2 datetime NOT NULL,
stopOrch2 datetime NOT NULL,
startWebMethoddatetime NOT NULL,
stopWebMethod datetime NOT NULL, 或者也可以创建一个更通用的：
CREATE TABLE [dbo].[executionHistory](
trcCorrId [nvarchar](36) NOT NULL,
eventName [nvarchar](36) NOT NULL,
eventStart datetime NOT NULL,
eventStop datetime NOT NULL Baiscally我有一个调用BizTalk编排的C# (HIP)程序(作为web服务的orch1 -published )。该编排调用orch2，后者调用另一个web服务。我想从上面的表中记录/分析/汇总响应时间，而不是我的跟踪表。
HIP程序的开始可以如下确定:其中trcProgram = ' HIP‘和trcCode = '0250’，以及HIP程序的结束，其中trcProgram = 'HIP‘和trcCode = '0299’。
我对四个里程碑中的每一个都有类似的模式，例如: start Orch1: where trcProgram = 'Orch1‘and trcCode = '0010’和end Orch1: where trcProgram = 'Orch1‘and trcCode = '9999’。
每次执行都由trcCorrGuid唯一标识。这是一个标记在与同一执行相关的所有行上的GUID。
如何执行优雅而高效的SQL查询来从跟踪表中加载executionHistory表？我折腾了一段时间，但意识到我写的东西可能真的很草率--并通过了几次--我需要从可能做过这样事情的人那里得到建议。
我打算从获取一个不同的Guids列表开始。然后，我将执行类似于CASE语句的操作，但需要一个可以处理两个值而不是一个值(trcProgram和trcCode)的CASE。也许嵌套的CASE语句可以工作？
提前谢谢你，
尼尔·沃尔特斯
更新：
这是我到目前为止正在做的事情：
select trace.trcDateTime,
EventName =
CASE trace.trcCode
-- HIP
WHEN '0250' THEN
CASE trace.trcProgram
WHEN 'HIP:RCT.HIP.Components:Push' THEN 'Start:HIP'
ELSE 'NA'
END
WHEN '0299' THEN
CASE trace.trcProgram
WHEN 'HIP:RCT.HIP.Components:Push' THEN 'Stop:HIP'
ELSE 'NA'
END
-- Orch 1
WHEN '0010' THEN
CASE trace.trcProgram
WHEN 'Orch:WCFSubmitPolicyAction' THEN 'Start:Orch:WCFSubmitPolicyAction'
WHEN 'Orch:CallRCTWebService' THEN 'Start:Orch:CallRCTWebService'
ELSE 'NA'
END
WHEN '9999' THEN
CASE trace.trcProgram
WHEN 'Orch:WCFSubmitPolicyAction' THEN 'Stop:Orch:WCFSubmitPolicyAction'
WHEN 'Orch:CallRCTWebService' THEN 'Stop:Orch:CallRCTWebService'
ELSE 'NA'
END
-- WebMethod Push
WHEN '1210' THEN
CASE trace.trcProgram
WHEN 'WebMethod:CreateValuationMinimal' THEN 'Start:WebMethod:CreateValuationMinimal'
ELSE 'NA'
END
WHEN '1289' THEN
CASE trace.trcProgram
WHEN 'Orch:CallRCTWebService' THEN 'Stop:WebMethod:CreateValuationMinimal'
ELSE 'NA'
END
-- WebMethod Pull
WHEN '1950' THEN
CASE trace.trcProgram
WHEN 'WebMethod:ExportValuationRecordIdCustom' THEN 'Start:WebMethod:ExportValuationRecordIdCustom'
ELSE 'NA'
END
WHEN '1951' THEN
CASE trace.trcProgram
WHEN 'WebMethod:ExportValuationRecordIdCustom' THEN 'Stop:WebMethod:ExportValuationRecordIdCustom'
ELSE 'NA'
END
ELSE 'NA'
END
from trace我可以将其存储到临时表中，然后从那里继续工作。
基于Aaron的帖子，这是我到目前为止的工作。我必须在"use“语句后添加分号，并在底部添加"AS PivotTable”。
use ESBSupport;
WITH trace_CTE AS
(
SELECT trcCorrId,
trcProgram + trcCode AS trcUniqueCode,
trcDateTime
FROM trace
WHERE (trcProgram = 'HIP:RCT.HIP.Components:Push' AND trcCode IN ('0250', '0299'))
OR (trcProgram = 'Orch:WCFSubmitPolicyAction' AND trcCode IN ('0010', '9999'))
)
SELECT
trcCorrId,
[HIP:RCT.HIP.Components:Push0250] AS startHIP,
[HIP:RCT.HIP.Components:Push0299] AS stopHIP,
[Orch:WCFSubmitPolicyAction0010] AS startOrch1,
[Orch:WCFSubmitPolicyAction9999] AS stopOrch1
-- // etc., continue this for the other events
FROM trace_CTE
PIVOT
(
MIN(trcDateTime)
FOR trcUniqueCode IN (
[HIP:RCT.HIP.Components:Push0250],
[HIP:RCT.HIP.Components:Push0299],
[Orch:WCFSubmitPolicyAction0010],
[Orch:WCFSubmitPolicyAction9999]
)
) as PivotTable回答 2
Stack Overflow用户
发布于 2009-12-23 00:49:06
我不知道你认为这是优雅还是高效，但这可能是你能做的最好的事情了：
WITH trace_CTE AS
(
SELECT trcCorrId, trcProgram + trcCode AS trcUniqueCode, trcDateTime
FROM trace
WHERE (trcProgram = 'HIP' AND trcCode IN ('0250', '0299'))
OR (trcProgram = 'Orch1' AND trcCode IN ('0010', '9999'))
OR ([more conditions here])
)
SELECT
trcCorrId,
[HIP0250] AS startHIP, [HIP0299] AS stopHIP,
[Orch10010] AS startOrch1, [Orch19999] AS stopOrch1
-- // etc., continue this for the other events
FROM trace_CTE
PIVOT
(
MIN(trcDateTime)
FOR trcUniqueCode IN
(
[HIP0250], [HIP0299],
[Orch10010], [Orch19999],
[(continue with other codes)]
)
)我使用CTE只是为了清理语法，它实际上不会创建另一个遍。PIVOT是非常高效的，尽管如果您的跟踪表很大，这仍然会很慢。
根据我的经验，只要有可能，最好使用触发器来处理这些问题(上下文累积)。特别是对于可能设置为只写表的表，在INSERT上执行一些检查的开销是最小的，而在运行时执行相同的检查将更加痛苦。如果您不这样做，您可能会以每晚的批处理过程来处理这些数字并将它们放入分析表中，这样用户就不必等待查询运行。
最后注意:为了在这个查询上获得任何类型的性能，您肯定需要一个涵盖(trcCorrId，trcDateTime)的(trcProgram，trcCode)索引。
Stack Overflow用户
发布于 2009-12-23 00:56:30
思考的食粮: NVARCHAR(36)需要72字节的存储+长度信息(可变长度guids?)。Uniqueidentifier占用16个字节。
并且您希望基于以下内容创建一个eventName nvarchar(36)：什么？trcProgram？这就是nvarchar(150)。
SELECT coalesce(start.trcCorrGuid, end.trcCorrGuid),
coalesce(start.trcProgram, end.trcProgram) as eventName,
start.trcDateTime as eventStart,
end.trcDateTime as eventEnd
FROM (
SELECT * FROM trace
WHERE trcCode IN ('0250', '0010', ...))
as start
FULL JOIN (
SELECT * FROM trace
WHERE trcCode IN ('0299', '9999', ...))
as end ON start.trcCorrGuid = end.trcCorrGuid
AND start.trcProgram = end.trcProgram;此查询在开始和结束事件之间使用完全联接，以允许任何跟踪不准确(缺少stop或start的关联)。查询的性能将取决于跟踪上的索引。如果你只有一个trcID的主键，那么没有一个查询能创造奇迹，那么你最好写一个游标循环。我示例中的查询至少需要trcCorrGuid上的索引，最好是(trcCorrGuid) include (trcCode, trcProgram)上的索引
https://stackoverflow.com/questions/1947146
复制相似问题
腾讯云开发者
