我们目前正试图加速一些查询,我们遇到了一些我(不是DBA只是.NET开发人员)无法解释或理解的东西.我们在SQL Server 2005上运行此查询. 我们有以下查询(为了参数而做小而简单); SELECT *FROM
我们有以下查询(为了参数而做小而简单);
SELECT
*
FROM
RandomTable
WHERE
MoneyColumn <> 0
GROUP BY
SomeColumn
此查询在大约三秒内运行,然后我们随机尝试跟随加速(真的在黑暗中拍摄)
SELECT
*
FROM
RandomTable
WHERE
isnull(MoneyColumn,0) <> 0
GROUP BY
SomeColumn
这会将查询速度降低到大约一秒钟.
这个列没有NULL值(但是由于数据库设计很糟糕)但是它是空的……
它是否为NULLABLE,这使得SQL Server可以做些什么来解决这个问题,这使得它在没有提到ISNULL的地方变慢了？我根本不知道为什么ISNULL会让它表现得更快(并且如此大的余地).我认为当查询中有ISNULL语句时,SQL实际上还有更多工作要做.
任何人都可以对此有所了解吗？
EDIT执行计划已添加
有了ISNULL
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.5000.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="9019.76" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="1.48105" StatementText="SELECT debiteur_id, MIN(Faktuurdatum) AS OldestOpenInvoiceDate, ISNULL(SUM(Totaal_Open),0) AS TotalOpenAmount
FROM dbo.tbl_Faktuur 
WHERE (Afgehandeld_NeeJa = 0 OR Afgehandeld_NeeJa IS NULL)
AND (ISNULL(Totaal_Open,0) <> 0) 
--AND (Totaal_Open <> 0) 
GROUP BY debiteur_id" StatementType="SELECT">
<StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
<QueryPlan DegreeOfParallelism="1" MemoryGrant="1520" CachedPlanSize="54" CompileTime="11" CompileCPU="11" CompileMemory="704">
<RelOp AvgRowSize="23" EstimateCPU="0.000901976" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9019.76" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.48105">
<OutputList>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
<ColumnReference Column="Expr1003" />
<ColumnReference Column="Expr1005" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1005" />
<ScalarOperator ScalarString="isnull([Expr1004],($0.0000))">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1004" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="($0.0000)" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="23" EstimateCPU="0.291662" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9019.76" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.48014">
<OutputList>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
<ColumnReference Column="Expr1003" />
<ColumnReference Column="Expr1004" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1004" />
<ScalarOperator ScalarString="CASE WHEN [Expr1013]=(0) THEN NULL ELSE [Expr1014] END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1013" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1014" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="23" EstimateCPU="0.291662" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9019.76" LogicalOp="Aggregate" NodeId="2" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.48014">
<OutputList>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
<ColumnReference Column="Expr1003" />
<ColumnReference Column="Expr1013" />
<ColumnReference Column="Expr1014" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="156794" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1003" />
<ScalarOperator ScalarString="MIN([directpay].[dbo].[tbl_Faktuur].[Faktuurdatum])">
<Aggregate AggType="MIN" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Faktuurdatum" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1013" />
<ScalarOperator ScalarString="COUNT_BIG([directpay].[dbo].[tbl_Faktuur].[Totaal_Open])">
<Aggregate AggType="COUNT_BIG" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1014" />
<ScalarOperator ScalarString="SUM([directpay].[dbo].[tbl_Faktuur].[Totaal_Open])">
<Aggregate AggType="SUM" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<HashKeysBuild>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
</HashKeysBuild>
<RelOp AvgRowSize="23" EstimateCPU="0.255" EstimateIO="0.634196" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="27420" LogicalOp="Index Seek" NodeId="4" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.889196">
<OutputList>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Faktuurdatum" />
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="298726" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Faktuurdatum" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
</DefinedValue>
</DefinedValues>
<Object Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Index="[_dta_index_tbl_Faktuur_5_583009158__K13_K9_K19_K2_5]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Afgehandeld_NeeJa" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="isnull([directpay].[dbo].[tbl_Faktuur].[Totaal_Open],($0.0000))<($0.0000) OR isnull([directpay].[dbo].[tbl_Faktuur].[Totaal_Open],($0.0000))>($0.0000)">
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="LT">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="($0.0000)" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="($0.0000)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="($0.0000)" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="($0.0000)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Hash>
</RelOp>
</ComputeScalar>
</RelOp>
</ComputeScalar>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
没有ISNULL
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.5000.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1322.43" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.274954" StatementText="SELECT debiteur_id, MIN(Faktuurdatum) AS OldestOpenInvoiceDate, ISNULL(SUM(Totaal_Open),0) AS TotalOpenAmount
FROM dbo.tbl_Faktuur 
WHERE (Afgehandeld_NeeJa = 0 OR Afgehandeld_NeeJa IS NULL)
--AND (ISNULL(Totaal_Open,0) <> 0) 
AND (Totaal_Open <> 0) 
GROUP BY debiteur_id" StatementType="SELECT">
<StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
<QueryPlan CachedPlanSize="47" CompileTime="9" CompileCPU="9" CompileMemory="528">
<RelOp AvgRowSize="23" EstimateCPU="0.000132243" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1322.43" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.274954">
<OutputList>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
<ColumnReference Column="Expr1003" />
<ColumnReference Column="Expr1005" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1005" />
<ScalarOperator ScalarString="isnull([Expr1004],($0.0000))">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1004" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="($0.0000)" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="23" EstimateCPU="0.167304" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1322.43" LogicalOp="Aggregate" NodeId="1" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.274822">
<OutputList>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
<ColumnReference Column="Expr1003" />
<ColumnReference Column="Expr1004" />
</OutputList>
<MemoryFractions Input="0" Output="0" />
<Hash>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1003" />
<ScalarOperator ScalarString="MIN([directpay].[dbo].[tbl_Faktuur].[Faktuurdatum])">
<Aggregate AggType="MIN" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Faktuurdatum" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1004" />
<ScalarOperator ScalarString="SUM([directpay].[dbo].[tbl_Faktuur].[Totaal_Open])">
<Aggregate AggType="SUM" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<HashKeysBuild>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
</HashKeysBuild>
<RelOp AvgRowSize="23" EstimateCPU="0.030319" EstimateIO="0.0771991" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="27420" LogicalOp="Index Seek" NodeId="2" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.107518">
<OutputList>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Faktuurdatum" />
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Debiteur_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Faktuurdatum" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
</DefinedValue>
</DefinedValues>
<Object Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Index="[_dta_index_tbl_Faktuur_5_583009158__K13_K9_K19_K2_5]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Afgehandeld_NeeJa" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
<EndRange ScanType="LT">
<RangeColumns>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="($0.0000)">
<Const ConstValue="($0.0000)" />
</ScalarOperator>
</RangeExpressions>
</EndRange>
</SeekPredicate>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Afgehandeld_NeeJa" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
<StartRange ScanType="GT">
<RangeColumns>
<ColumnReference Database="[directpay]" Schema="[dbo]" Table="[tbl_Faktuur]" Column="Totaal_Open" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="($0.0000)">
<Const ConstValue="($0.0000)" />
</ScalarOperator>
</RangeExpressions>
</StartRange>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</Hash>
</RelOp>
</ComputeScalar>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
ISNULL()版本构成了谓词
unsargable的一部分.
除了意味着它不能有效地使用索引(它仍然可以从查询中的另一个谓词中寻找前导列),这也使得构造在估计行数时对优化器更加不透明那将匹配.
两个计划都有大约27,420行进入哈希匹配运算符,但它们的估计行数有多少不同.较快的计划估计为9,019,较慢的计划为1,322.
这两个估计实际上都是非常错误的,因为实际上您的组有156,794个不同的值.由于错误的估计,查询没有分配足够的内存来构建哈希表,并且两者都需要溢出到光盘.较慢的一个特别是内存不足,所以这种情况多次发生.
您可以尝试更新现有统计信息或查看创建一些多列统计信息,以便尝试更准确地估计将返回的不同组的数量,从而分配不同的计划或更多内存.