All geek questions in one place

Why will ISNULL improve query performance?

We are currently trying to speed up the execution of some queries, and we are faced with something that I (not a database administrator, just a .NET developer) can not explain or understand. We are executing this query on SQL Server 2005.

We have the following query (made small and simple for an argument);

SELECT * FROM RandomTable WHERE MoneyColumn <> 0 GROUP BY SomeColumn

This request runs around three seconds, then we accidentally tried to execute it to speed it up (done in the dark really)

 SELECT * FROM RandomTable WHERE isnull(MoneyColumn,0) <> 0 GROUP BY SomeColumn

This reduces the query speed to about one second.

There are no NULL values ​​in this column (but due to the fact that database design is not possible), it is, however, NULLABLE ...

Is the fact that it is NULLABLE doing SQL Server something to account for, which slows down when ISNULL is not mentioned? I just don't know why ISNULL will make it work faster (and with such a big difference). I would think that SQL will really have more features if there is an ISNULL statement in the query.

Can anyone shed some light on this?

EDIT Implementation Plans Added

With 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&#xD;&#xA;FROM dbo.tbl_Faktuur &#xD;&#xA;WHERE (Afgehandeld_NeeJa = 0 OR Afgehandeld_NeeJa IS NULL)&#xD;&#xA;AND (ISNULL(Totaal_Open,0) &lt;&gt; 0) &#xD;&#xA;--AND (Totaal_Open &lt;&gt; 0) &#xD;&#xA;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))&lt;($0.0000) OR isnull([directpay].[dbo].[tbl_Faktuur].[Totaal_Open],($0.0000))&gt;($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>

Without 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&#xD;&#xA;FROM dbo.tbl_Faktuur &#xD;&#xA;WHERE (Afgehandeld_NeeJa = 0 OR Afgehandeld_NeeJa IS NULL)&#xD;&#xA;--AND (ISNULL(Totaal_Open,0) &lt;&gt; 0) &#xD;&#xA;AND (Totaal_Open &lt;&gt; 0) &#xD;&#xA;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>
+4
source share
2 answers

The ISNULL() version makes this part of the predicate unsargable .

As well as the meaning that it cannot use your index efficiently (it can still search in the leading column from another predicate in the query), it also makes the construct more opaque to the optimizer when it comes to estimating the number of rows that will match.

Both plans have an estimated 27,420 lines included in the hash mat operator, but they differ in how many evaluation lines come out. A faster plan estimates 9.019, and a slower one - 1.322.

Both of these estimates are actually very erroneous, because in fact there are 156,794 different values ​​for your group in the column. As a result of poor ratings, not a single request allocates enough memory to create a hash table, and both must spill onto disk. The slower, the stronger the memory, and this happens several times.

You can try to update existing statistics or look at creating several statistics from several columns in order to try to get a more accurate estimate of the number of individual groups by the elements that will be returned, and thus a different plan or more allocated memory.

+3
source

Adding two filtered, spanning indexes can help (the question why ISNULL helps speed has already been answered):

 USE Directpay /* 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 */ -- Prepare two filtered indices to split the OR condition CREATE NONCLUSTERED INDEX IX_tbl_faktuur_debiteur_id_faktuurdatum_flt_1 ON tbl_Faktuur (debiteur_id, faktuurdatum) INCLUDE (Totaal_Open) WHERE (Afgehandeld_NeeJa = 0); CREATE NONCLUSTERED INDEX IX_tbl_faktuur_debiteur_id_faktuurdatum_flt_2 ON tbl_Faktuur (debiteur_id, faktuurdatum) INCLUDE (Totaal_Open) WHERE (Afgehandeld_NeeJa IS NULL); -- Rewrite the query SELECT debiteur_id , min(OldestOpenInvoiceDate) AS OldestOpenInvoiceDate , isnull(sum(TotalOpenAmount),0) AS TotalOpenAmount FROM ( SELECT debiteur_id , min(Faktuurdatum) AS OldestOpenInvoiceDate , sum(Totaal_Open) AS TotalOpenAmount FROM dbo.tbl_Faktuur WHERE Afgehandeld_NeeJa = 0 GROUP BY debiteur_id UNION ALL SELECT debiteur_id , min(Faktuurdatum) AS OldestOpenInvoiceDate , sum(Totaal_Open) AS TotalOpenAmount FROM dbo.tbl_Faktuur WHERE Afgehandeld_NeeJa IS NULL GROUP BY debiteur_id ) Unhandled GROUP BY debiteur_id
0
source

Source: https://habr.com/ru/post/1439329/

More articles:


All Articles