我有一个针对我们的数据仓库运行了很长时间的查询，需要看看如何优化它。[assessorlogdw].[dbo].[log]表有1.05亿行。这是问题所在。
select a.Nationid,count(a.Nationid)as nationcount from
(select replace(replace(convert(nvarchar(MAX),[parameters]), '<root><nationid>', ''),'</nationid></root>','') as Nationid
from [AssessorLogDW].[dbo].[Log]
where username not like '%erieri.com' and "Parameters" is not null) as a
group by a.Nationid
这是表上的索引。
位于主时间戳上的非聚集日志
Ix_Log_1非群集，位于主类型上
Ix_Log_3非群集位于主应用程序代码上
Ix_Log_4非群集，位于主服务命名空间上
IX U LOG U 5非群集，位于主服务名上
IX U LOG U 6位于主MethodName上
Ix_Log_7非聚集，位于主要持续时间上
ix_log_methodname非聚集在主订阅代码methodname上
ix_log_用户名非聚集在主用户名、subscriptioncode、methodname、timestamp上
ix_log_usernamedate非群集，位于主用户名、时间戳、subscriptioncode上
ix_type appcode methodname非群集，位于主类型appcode，methodname，timestamp上
pk_日志群集，唯一，主键位于主dwid上
执行计划如下：
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.3.1" Build="12.0.5207.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="select a.Nationid,count(a.Nationid)as nationcount from 
 (select replace(replace(convert(nvarchar(MAX),[parameters]), '<root><nationid>', ''),'</nationid></root>','') as Nationid
 from [AssessorLogDW].[dbo].[Log]
 where username not like '%erieri.com' and "Parameters" is not null) as a
group by a.Nationid" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="18373.7" StatementEstRows="8240.4" StatementOptmLevel="FULL" QueryHash="0x288FAF0B2CDAA4D5" QueryPlanHash="0xBC649190D004A5F1" CardinalityEstimationModelVersion="70"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" /><QueryPlan CachedPlanSize="56" CompileTime="27" CompileCPU="12" CompileMemory="672"><ThreadStat Branches="2" /><MemoryGrantInfo SerialRequiredMemory="1536" SerialDesiredMemory="83680" /><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="256000" EstimatedPagesCached="64000" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="2424384" /><TraceFlags IsCompileTime="1"><TraceFlag Value="845" Scope="Global" /><TraceFlag Value="1117" Scope="Global" /><TraceFlag Value="1118" Scope="Global" /><TraceFlag Value="3226" Scope="Global" /><TraceFlag Value="4199" Scope="Global" /></TraceFlags><RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="16480.8" EstimateIO="0" EstimateCPU="0.00164808" AvgRowSize="4039" EstimatedTotalSubtreeCost="18373.7" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1002" /><ColumnReference Column="Expr1003" /></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003" /><ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[globalagg1009],0)"><Convert DataType="int" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="globalagg1009" /></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Parallelism" LogicalOp="Gather Streams" EstimateRows="16480.8" EstimateIO="0" EstimateCPU="2.34046" AvgRowSize="4043" EstimatedTotalSubtreeCost="18373.7" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1002" /><ColumnReference Column="globalagg1009" /></OutputList><Parallelism><RelOp NodeId="3" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="16480.8" EstimateIO="0" EstimateCPU="0.00906444" AvgRowSize="4043" EstimatedTotalSubtreeCost="18371.3" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1002" /><ColumnReference Column="globalagg1009" /></OutputList><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="globalagg1009" /><ScalarOperator ScalarString="SUM([partialagg1008])"><Aggregate Distinct="0" AggType="SUM"><ScalarOperator><Identifier><ColumnReference Column="partialagg1008" /></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><GroupBy><ColumnReference Column="Expr1002" /></GroupBy><RelOp NodeId="4" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="16480.8" EstimateIO="0.00563063" EstimateCPU="0.529902" AvgRowSize="4043" EstimatedTotalSubtreeCost="18371.3" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1002" /><ColumnReference Column="partialagg1008" /></OutputList><MemoryFractions Input="1" Output="1" /><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Column="Expr1002" /></OrderByColumn></OrderBy><RelOp NodeId="5" PhysicalOp="Parallelism" LogicalOp="Repartition Streams" EstimateRows="16480.8" EstimateIO="0" EstimateCPU="3.98045" AvgRowSize="4043" EstimatedTotalSubtreeCost="18370.8" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1002" /><ColumnReference Column="partialagg1008" /></OutputList><Parallelism PartitioningType="Hash"><PartitionColumns><ColumnReference Column="Expr1002" /></PartitionColumns><RelOp NodeId="6" PhysicalOp="Hash Match" LogicalOp="Partial Aggregate" EstimateRows="16480.8" EstimateIO="0" EstimateCPU="276.148" AvgRowSize="4043" EstimatedTotalSubtreeCost="18366.8" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1002" /><ColumnReference Column="partialagg1008" /></OutputList><MemoryFractions Input="0" Output="0" /><Hash><DefinedValues><DefinedValue><ColumnReference Column="partialagg1008" /><ScalarOperator ScalarString="COUNT(replace(replace([Expr1006],N'<root><nationid>',CONVERT_IMPLICIT(nvarchar(max),'',0)),N'</nationid></root>',CONVERT_IMPLICIT(nvarchar(max),'',0)))"><Aggregate Distinct="0" AggType="COUNT_BIG"><ScalarOperator><Intrinsic FunctionName="replace"><ScalarOperator><Intrinsic FunctionName="replace"><ScalarOperator><Identifier><ColumnReference Column="Expr1006" /></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="N'<root><nationid>'" /></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1005"><ScalarOperator><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Const ConstValue="''" /></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="N'</nationid></root>'" /></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1004"><ScalarOperator><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Const ConstValue="''" /></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><HashKeysBuild><ColumnReference Column="Expr1002" /></HashKeysBuild><BuildResidual><ScalarOperator ScalarString="[Expr1002] = [Expr1002]"><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Column="Expr1002" /></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1002" /></Identifier></ScalarOperator></Compare></ScalarOperator></BuildResidual><RelOp NodeId="7" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="6.79042e+007" EstimateIO="0" EstimateCPU="3.39521" AvgRowSize="8061" EstimatedTotalSubtreeCost="18090.7" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1002" /><ColumnReference Column="Expr1006" /></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1002" /><ScalarOperator ScalarString="replace(replace([Expr1007],N'<root><nationid>',CONVERT_IMPLICIT(nvarchar(max),'',0)),N'</nationid></root>',CONVERT_IMPLICIT(nvarchar(max),'',0))"><Intrinsic FunctionName="replace"><ScalarOperator><Intrinsic FunctionName="replace"><ScalarOperator><Identifier><ColumnReference Column="Expr1007" /></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="N'<root><nationid>'" /></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1005"><ScalarOperator><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Const ConstValue="''" /></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="N'</nationid></root>'" /></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1004"><ScalarOperator><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Const ConstValue="''" /></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="8" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="6.79042e+007" EstimateIO="0" EstimateCPU="3.39521" AvgRowSize="8061" EstimatedTotalSubtreeCost="18087.3" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1006" /><ColumnReference Column="Expr1007" /></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1006" /><ScalarOperator ScalarString="CONVERT(nvarchar(max),[AssessorLogDW].[dbo].[Log].[Parameters],0)"><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Database="[AssessorLogDW]" Schema="[dbo]" Table="[Log]" Column="Parameters" /></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1007" /><ScalarOperator ScalarString="CONVERT(nvarchar(max),[AssessorLogDW].[dbo].[Log].[Parameters],0)"><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Database="[AssessorLogDW]" Schema="[dbo]" Table="[Log]" Column="Parameters" /></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="9" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="6.79042e+007" EstimateIO="0" EstimateCPU="72.688" AvgRowSize="4035" EstimatedTotalSubtreeCost="18083.9" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[AssessorLogDW]" Schema="[dbo]" Table="[Log]" Column="Parameters" /></OutputList><Filter StartupExpression="0"><RelOp NodeId="10" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="6.79042e+007" EstimateIO="17953.2" EstimateCPU="57.9398" AvgRowSize="4060" EstimatedTotalSubtreeCost="18011.2" TableCardinality="1.05345e+008" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[AssessorLogDW]" Schema="[dbo]" Table="[Log]" Column="Parameters" /></OutputList><Warnings><ColumnsWithNoStatistics><ColumnReference Database="[AssessorLogDW]" Schema="[dbo]" Table="[Log]" Column="Parameters" /></ColumnsWithNoStatistics></Warnings><IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[AssessorLogDW]" Schema="[dbo]" Table="[Log]" Column="Parameters" /></DefinedValue></DefinedValues><Object Database="[AssessorLogDW]" Schema="[dbo]" Table="[Log]" Index="[ix_TypeAppCodeMethodName]" IndexKind="NonClustered" Storage="RowStore" /><Predicate><ScalarOperator ScalarString="NOT [AssessorLogDW].[dbo].[Log].[UserName] like '%erieri.com'"><Logical Operation="NOT"><ScalarOperator><Intrinsic FunctionName="like"><ScalarOperator><Identifier><ColumnReference Database="[AssessorLogDW]" Schema="[dbo]" Table="[Log]" Column="UserName" /></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="'%erieri.com'" /></ScalarOperator></Intrinsic></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp><Predicate><ScalarOperator ScalarString="[AssessorLogDW].[dbo].[Log].[Parameters] IS NOT NULL"><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[AssessorLogDW]" Schema="[dbo]" Table="[Log]" Column="Parameters" /></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL" /></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp></Hash></RelOp></Parallelism></RelOp></Sort></RelOp></StreamAggregate></RelOp></Parallelism></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
最佳答案
您可以使用CROSS APPLY来避免subquery：
select Nationid, count(*) as nationcount
from [AssessorLogDW].[dbo].[Log] l1 cross apply
( values (replace(replace(convert(nvarchar(max), [parameters]), '<root><nationid>', ''),'</nationid></root>','')
)
) l2(Nationid)
where username not like '%erieri.com' and
[parameters] is not null
group by Nationid;