title: Simple Self Join Query Bad Performance
body: <p>Could anyone advice on how do I improve the performance of the following query. Note, the problem seems to be caused by <code>where</code> clause.</p>
  
  <p>Data (table contains a huge set of rows - 500K+, the set of parameters it's called with assums the return of 2-5K records per query, which takes 8-10 minutes currently):</p>
  
  <pre><code>USE [SomeDb]
  GO
  
      SET ANSI_NULLS ON
      GO
  
      SET QUOTED_IDENTIFIER ON
      GO
  
      CREATE TABLE [dbo].[Data](
          [x] [money] NOT NULL,
          [y] [money] NOT NULL,
       CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED 
      (
          [x] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
      ) ON [PRIMARY]
  
      GO
  </code></pre>
  
  <p>The Query </p>
  
  <pre><code>select top 10000
  s.x as sx,
  e.x as ex,
  s.y as sy,
  e.y as ey,
  e.y - s.y as y_delta,
  e.x - s.x as x_delta
  from Data s 
      inner join Data e
      on e.x &gt; s.x and e.x - s.x between xFrom and xTo
  --where e.y - s.y &gt; @yDelta -- when uncommented causes a huge delay
  </code></pre>
  
  <p>Update 1 - Execution Plan</p>
  
  <hr>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-16"?&gt;
  &lt;ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.2100.60" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"&gt;
    &lt;BatchSequence&gt;
      &lt;Batch&gt;
        &lt;Statements&gt;
          &lt;StmtSimple StatementCompId="1" StatementEstRows="100" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0263655" StatementText="select top 100&amp;#xD;&amp;#xA;s.x as sx,&amp;#xD;&amp;#xA;e.x as ex,&amp;#xD;&amp;#xA;s.y as sy,&amp;#xD;&amp;#xA;e.y as ey,&amp;#xD;&amp;#xA;e.y - s.y as y_delta,&amp;#xD;&amp;#xA;e.x - s.x as x_delta&amp;#xD;&amp;#xA;from Data s &amp;#xD;&amp;#xA;    inner join Data e&amp;#xD;&amp;#xA; on e.x &amp;gt; s.x and e.x - s.x between 100 and 105&amp;#xD;&amp;#xA;where e.y - s.y &amp;gt; 0.01&amp;#xD;&amp;#xA;" StatementType="SELECT" QueryHash="0xAAAC02AC2D78CB56" QueryPlanHash="0x747994153CB2D637" RetrievedFromCache="true"&gt;
            &lt;StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /&gt;
            &lt;QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="13" CompileCPU="13" CompileMemory="424"&gt;
              &lt;MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" /&gt;
              &lt;OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="52199" EstimatedPagesCached="14561" EstimatedAvailableDegreeOfParallelism="4" /&gt;
              &lt;RelOp AvgRowSize="55" EstimateCPU="1E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0263655"&gt;
                &lt;OutputList&gt;
                  &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /&gt;
                  &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /&gt;
                  &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /&gt;
                  &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /&gt;
                  &lt;ColumnReference Column="Expr1004" /&gt;
                  &lt;ColumnReference Column="Expr1005" /&gt;
                &lt;/OutputList&gt;
                &lt;ComputeScalar&gt;
                  &lt;DefinedValues&gt;
                    &lt;DefinedValue&gt;
                      &lt;ColumnReference Column="Expr1004" /&gt;
                      &lt;ScalarOperator ScalarString="[SomeDb].[dbo].[Data].[y] as [e].[y]-[SomeDb].[dbo].[Data].[y] as [s].[y]"&gt;
                        &lt;Arithmetic Operation="SUB"&gt;
                          &lt;ScalarOperator&gt;
                            &lt;Identifier&gt;
                              &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /&gt;
                            &lt;/Identifier&gt;
                          &lt;/ScalarOperator&gt;
                          &lt;ScalarOperator&gt;
                            &lt;Identifier&gt;
                              &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /&gt;
                            &lt;/Identifier&gt;
                          &lt;/ScalarOperator&gt;
                        &lt;/Arithmetic&gt;
                      &lt;/ScalarOperator&gt;
                    &lt;/DefinedValue&gt;
                    &lt;DefinedValue&gt;
                      &lt;ColumnReference Column="Expr1005" /&gt;
                      &lt;ScalarOperator ScalarString="[SomeDb].[dbo].[Data].[x] as [e].[x]-[SomeDb].[dbo].[Data].[x] as [s].[x]"&gt;
                        &lt;Arithmetic Operation="SUB"&gt;
                          &lt;ScalarOperator&gt;
                            &lt;Identifier&gt;
                              &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /&gt;
                            &lt;/Identifier&gt;
                          &lt;/ScalarOperator&gt;
                          &lt;ScalarOperator&gt;
                            &lt;Identifier&gt;
                              &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /&gt;
                            &lt;/Identifier&gt;
                          &lt;/ScalarOperator&gt;
                        &lt;/Arithmetic&gt;
                      &lt;/ScalarOperator&gt;
                    &lt;/DefinedValue&gt;
                  &lt;/DefinedValues&gt;
                  &lt;RelOp AvgRowSize="39" EstimateCPU="1E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0263555"&gt;
                    &lt;OutputList&gt;
                      &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /&gt;
                      &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /&gt;
                      &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /&gt;
                      &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /&gt;
                    &lt;/OutputList&gt;
                    &lt;RunTimeInformation&gt;
                      &lt;RunTimeCountersPerThread Thread="0" ActualRows="100" ActualEndOfScans="1" ActualExecutions="1" /&gt;
                    &lt;/RunTimeInformation&gt;
                    &lt;Top RowCount="false" IsPercent="false" WithTies="false"&gt;
                      &lt;TopExpression&gt;
                        &lt;ScalarOperator ScalarString="(100)"&gt;
                          &lt;Const ConstValue="(100)" /&gt;
                        &lt;/ScalarOperator&gt;
                      &lt;/TopExpression&gt;
                      &lt;RelOp AvgRowSize="39" EstimateCPU="151828" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0263455"&gt;
                        &lt;OutputList&gt;
                          &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /&gt;
                          &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /&gt;
                          &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /&gt;
                          &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /&gt;
                        &lt;/OutputList&gt;
                        &lt;RunTimeInformation&gt;
                          &lt;RunTimeCountersPerThread Thread="0" ActualRows="100" ActualEndOfScans="0" ActualExecutions="1" /&gt;
                        &lt;/RunTimeInformation&gt;
                        &lt;NestedLoops Optimized="false"&gt;
                          &lt;OuterReferences&gt;
                            &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /&gt;
                            &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /&gt;
                          &lt;/OuterReferences&gt;
                          &lt;RelOp AvgRowSize="23" EstimateCPU="1.80448" EstimateIO="3.76461" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1640290"&gt;
                            &lt;OutputList&gt;
                              &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /&gt;
                              &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /&gt;
                            &lt;/OutputList&gt;
                            &lt;RunTimeInformation&gt;
                              &lt;RunTimeCountersPerThread Thread="0" ActualRows="15225" ActualEndOfScans="0" ActualExecutions="1" /&gt;
                            &lt;/RunTimeInformation&gt;
                            &lt;IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false"&gt;
                              &lt;DefinedValues&gt;
                                &lt;DefinedValue&gt;
                                  &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /&gt;
                                &lt;/DefinedValue&gt;
                                &lt;DefinedValue&gt;
                                  &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /&gt;
                                &lt;/DefinedValue&gt;
                              &lt;/DefinedValues&gt;
                              &lt;Object Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Index="[PK_Data]" Alias="[e]" IndexKind="Clustered" /&gt;
                            &lt;/IndexScan&gt;
                          &lt;/RelOp&gt;
                          &lt;RelOp AvgRowSize="23" EstimateCPU="0.902317" EstimateIO="1.88387" EstimateRebinds="1" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Clustered Index Seek" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0263655" TableCardinality="1640290"&gt;
                            &lt;OutputList&gt;
                              &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /&gt;
                              &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /&gt;
                            &lt;/OutputList&gt;
                            &lt;RunTimeInformation&gt;
                              &lt;RunTimeCountersPerThread Thread="0" ActualRows="100" ActualEndOfScans="15224" ActualExecutions="15225" /&gt;
                            &lt;/RunTimeInformation&gt;
                            &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"&gt;
                              &lt;DefinedValues&gt;
                                &lt;DefinedValue&gt;
                                  &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /&gt;
                                &lt;/DefinedValue&gt;
                                &lt;DefinedValue&gt;
                                  &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /&gt;
                                &lt;/DefinedValue&gt;
                              &lt;/DefinedValues&gt;
                              &lt;Object Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Index="[PK_Data]" Alias="[s]" IndexKind="Clustered" /&gt;
                              &lt;SeekPredicates&gt;
                                &lt;SeekPredicateNew&gt;
                                  &lt;SeekKeys&gt;
                                    &lt;EndRange ScanType="LT"&gt;
                                      &lt;RangeColumns&gt;
                                        &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /&gt;
                                      &lt;/RangeColumns&gt;
                                      &lt;RangeExpressions&gt;
                                        &lt;ScalarOperator ScalarString="[SomeDb].[dbo].[Data].[x] as [e].[x]"&gt;
                                          &lt;Identifier&gt;
                                            &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /&gt;
                                          &lt;/Identifier&gt;
                                        &lt;/ScalarOperator&gt;
                                      &lt;/RangeExpressions&gt;
                                    &lt;/EndRange&gt;
                                  &lt;/SeekKeys&gt;
                                &lt;/SeekPredicateNew&gt;
                              &lt;/SeekPredicates&gt;
                              &lt;Predicate&gt;
                                &lt;ScalarOperator ScalarString="([SomeDb].[dbo].[Data].[x] as [e].[x]-[SomeDb].[dbo].[Data].[x] as [s].[x])&amp;gt;=($100.0000) AND ([SomeDb].[dbo].[Data].[x] as [e].[x]-[SomeDb].[dbo].[Data].[x] as [s].[x])&amp;lt;=($105.0000) AND ([SomeDb].[dbo].[Data].[y] as [e].[y]-[SomeDb].[dbo].[Data].[y] as [s].[y])&amp;gt;(0.01)"&gt;
                                  &lt;Logical Operation="AND"&gt;
                                    &lt;ScalarOperator&gt;
                                      &lt;Compare CompareOp="GE"&gt;
                                        &lt;ScalarOperator&gt;
                                          &lt;Arithmetic Operation="SUB"&gt;
                                            &lt;ScalarOperator&gt;
                                              &lt;Identifier&gt;
                                                &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /&gt;
                                              &lt;/Identifier&gt;
                                            &lt;/ScalarOperator&gt;
                                            &lt;ScalarOperator&gt;
                                              &lt;Identifier&gt;
                                                &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /&gt;
                                              &lt;/Identifier&gt;
                                            &lt;/ScalarOperator&gt;
                                          &lt;/Arithmetic&gt;
                                        &lt;/ScalarOperator&gt;
                                        &lt;ScalarOperator&gt;
                                          &lt;Const ConstValue="($100.0000)" /&gt;
                                        &lt;/ScalarOperator&gt;
                                      &lt;/Compare&gt;
                                    &lt;/ScalarOperator&gt;
                                    &lt;ScalarOperator&gt;
                                      &lt;Compare CompareOp="LE"&gt;
                                        &lt;ScalarOperator&gt;
                                          &lt;Arithmetic Operation="SUB"&gt;
                                            &lt;ScalarOperator&gt;
                                              &lt;Identifier&gt;
                                                &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /&gt;
                                              &lt;/Identifier&gt;
                                            &lt;/ScalarOperator&gt;
                                            &lt;ScalarOperator&gt;
                                              &lt;Identifier&gt;
                                                &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /&gt;
                                              &lt;/Identifier&gt;
                                            &lt;/ScalarOperator&gt;
                                          &lt;/Arithmetic&gt;
                                        &lt;/ScalarOperator&gt;
                                        &lt;ScalarOperator&gt;
                                          &lt;Const ConstValue="($105.0000)" /&gt;
                                        &lt;/ScalarOperator&gt;
                                      &lt;/Compare&gt;
                                    &lt;/ScalarOperator&gt;
                                    &lt;ScalarOperator&gt;
                                      &lt;Compare CompareOp="GT"&gt;
                                        &lt;ScalarOperator&gt;
                                          &lt;Arithmetic Operation="SUB"&gt;
                                            &lt;ScalarOperator&gt;
                                              &lt;Identifier&gt;
                                                &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /&gt;
                                              &lt;/Identifier&gt;
                                            &lt;/ScalarOperator&gt;
                                            &lt;ScalarOperator&gt;
                                              &lt;Identifier&gt;
                                                &lt;ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /&gt;
                                              &lt;/Identifier&gt;
                                            &lt;/ScalarOperator&gt;
                                          &lt;/Arithmetic&gt;
                                        &lt;/ScalarOperator&gt;
                                        &lt;ScalarOperator&gt;
                                          &lt;Const ConstValue="(0.01)" /&gt;
                                        &lt;/ScalarOperator&gt;
                                      &lt;/Compare&gt;
                                    &lt;/ScalarOperator&gt;
                                  &lt;/Logical&gt;
                                &lt;/ScalarOperator&gt;
                              &lt;/Predicate&gt;
                            &lt;/IndexScan&gt;
                          &lt;/RelOp&gt;
                        &lt;/NestedLoops&gt;
                      &lt;/RelOp&gt;
                    &lt;/Top&gt;
                  &lt;/RelOp&gt;
                &lt;/ComputeScalar&gt;
              &lt;/RelOp&gt;
            &lt;/QueryPlan&gt;
          &lt;/StmtSimple&gt;
        &lt;/Statements&gt;
      &lt;/Batch&gt;
    &lt;/BatchSequence&gt;
  &lt;/ShowPlanXML&gt;
  </code></pre>