1

I see this type of code a lot in my databases

IF EXISTS(SELECT 1 FROM sample WHERE ID=@id)
BEGIN    
UPDATE sample SET name='cat' WHERE id=@id;   
END

So from my primary analysis it does 2 scan in the sample table which is quiet unnecessary , so when analysed

Assumption 1: if id exists

IF EXISTS(SELECT 1 FROM sample WHERE ID=4)
BEGIN    
UPDATE sample SET name='cat' WHERE id=4;
END
--with out if clause
UPDATE sample SET name='cat' WHERE id=4;

When checking the query plan I get this. Assumption 1

in which an extra scan is done for the IF clause which is 11% of the batch

Assumption 2: if id not exits

IF EXISTS(SELECT 1 FROM sample WHERE ID=30)
BEGIN    
UPDATE sample SET name='cat' WHERE id=30;
END
--with out if clause
UPDATE sample SET name='cat' WHERE id=30;

Assumption 2 here even though there is nothing to update sql server used the UPDATEoperator Now this makes the first query a bit efficient in which it skips the update operation , So why sql server used the TABLE UPDATEoperator even if there's nothing to update. which query is efficient? In my case most of the time the id will exist in my database so I opted the second method but is there any work around for this?

<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.0.1600.22" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
  <Batch>
  <Statements>
    <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.00330166" StatementText="IF exists ( SELECT 1 FROM sample where id=30)&#xD;" StatementType="COND WITH QUERY" QueryHash="0xC6C2301D8BFF33B0" QueryPlanHash="0xEE6BE951A74F117D">
      <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
      <QueryPlan DegreeOfParallelism="0" CachedPlanSize="8" CompileTime="1" CompileCPU="1" CompileMemory="104">
        <RelOp AvgRowSize="11" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00330166">
          <OutputList>
            <ColumnReference Column="Expr1005" />
          </OutputList>
          <ComputeScalar>
            <DefinedValues>
              <DefinedValue>
                <ColumnReference Column="Expr1005" />
                <ScalarOperator ScalarString="CASE WHEN [Expr1006] THEN (1) ELSE (0) END">
                  <IF>
                    <Condition>
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Column="Expr1006" />
                        </Identifier>
                      </ScalarOperator>
                    </Condition>
                    <Then>
                      <ScalarOperator>
                        <Const ConstValue="(1)" />
                      </ScalarOperator>
                    </Then>
                    <Else>
                      <ScalarOperator>
                        <Const ConstValue="(0)" />
                      </ScalarOperator>
                    </Else>
                  </IF>
                </ScalarOperator>
              </DefinedValue>
            </DefinedValues>
            <RelOp AvgRowSize="9" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Semi Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00330156">
              <OutputList>
                <ColumnReference Column="Expr1006" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <NestedLoops Optimized="false">
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1006" />
                  </DefinedValue>
                </DefinedValues>
                <ProbeColumn>
                  <ColumnReference Column="Expr1006" />
                </ProbeColumn>
                <RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Constant Scan" NodeId="2" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="1.157E-06">
                  <OutputList />
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <ConstantScan />
                </RelOp>
                <RelOp AvgRowSize="11" EstimateCPU="0.0001669" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table Scan" NodeId="3" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032919" TableCardinality="9">
                  <OutputList />
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                    <DefinedValues />
                    <Object Database="[test]" Schema="[dbo]" Table="[sample]" IndexKind="Heap" />
                    <Predicate>
                      <ScalarOperator ScalarString="[test].[dbo].[sample].[id]=(30)">
                        <Compare CompareOp="EQ">
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[test]" Schema="[dbo]" Table="[sample]" Column="id" />
                            </Identifier>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Const ConstValue="(30)" />
                          </ScalarOperator>
                        </Compare>
                      </ScalarOperator>
                    </Predicate>
                  </TableScan>
                </RelOp>
              </NestedLoops>
            </RelOp>
          </ComputeScalar>
        </RelOp>
      </QueryPlan>
    </StmtSimple>
  </Statements>
</Batch>
<Batch>
  <Statements>
    <StmtSimple StatementCompId="2" StatementEstRows="1" StatementId="2" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="0.0132947" StatementText="UPDATE [sample] set [name] = @1  WHERE [id]=@2" StatementType="UPDATE" QueryHash="0x93FCD1485182723F" QueryPlanHash="0x71BB5E8A8AF43EF8">
      <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
      <QueryPlan DegreeOfParallelism="0" CachedPlanSize="16" CompileTime="1" CompileCPU="1" CompileMemory="120">
        <RelOp AvgRowSize="9" EstimateCPU="1E-06" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Update" NodeId="0" Parallel="false" PhysicalOp="Table Update" EstimatedTotalSubtreeCost="0.0132947">
          <OutputList />
          <RunTimeInformation>
            <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
          </RunTimeInformation>
          <Update DMLRequestSort="false">
            <Object Database="[test]" Schema="[dbo]" Table="[sample]" IndexKind="Heap" />
            <SetPredicate>
              <ScalarOperator ScalarString="[test].[dbo].[sample].[name] = [Expr1004]">
                <ScalarExpressionList>
                  <ScalarOperator>
                    <MultipleAssign>
                      <Assign>
                        <ColumnReference Database="[test]" Schema="[dbo]" Table="[sample]" Column="name" />
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1004" />
                          </Identifier>
                        </ScalarOperator>
                      </Assign>
                    </MultipleAssign>
                  </ScalarOperator>
                </ScalarExpressionList>
              </ScalarOperator>
            </SetPredicate>
            <RelOp AvgRowSize="24" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00329372">
              <OutputList>
                <ColumnReference Column="Bmk1000" />
                <ColumnReference Column="Expr1004" />
              </OutputList>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1004" />
                    <ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(10),[@1],0)">
                      <Identifier>
                        <ColumnReference Column="ConstExpr1011">
                          <ScalarOperator>
                            <Convert DataType="varchar" Length="10" Style="0" Implicit="true">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Column="@1" />
                                </Identifier>
                              </ScalarOperator>
                            </Convert>
                          </ScalarOperator>
                        </ColumnReference>
                      </Identifier>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="15" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Top" NodeId="2" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00329362">
                  <OutputList>
                    <ColumnReference Column="Bmk1000" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <Top RowCount="true" IsPercent="false" WithTies="false">
                    <TopExpression>
                      <ScalarOperator ScalarString="(0)">
                        <Const ConstValue="(0)" />
                      </ScalarOperator>
                    </TopExpression>
                    <RelOp AvgRowSize="19" EstimateCPU="8.84E-05" EstimateIO="0.0032035" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table Scan" NodeId="3" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032919" TableCardinality="9">
                      <OutputList>
                        <ColumnReference Column="Bmk1000" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <TableScan Ordered="true" ForcedIndex="false" NoExpandHint="false">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Column="Bmk1000" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[test]" Schema="[dbo]" Table="[sample]" IndexKind="Heap" />
                        <Predicate>
                          <ScalarOperator ScalarString="[test].[dbo].[sample].[id]=CONVERT_IMPLICIT(int,[@2],0)">
                            <Compare CompareOp="EQ">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[test]" Schema="[dbo]" Table="[sample]" Column="id" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Column="ConstExpr1010">
                                    <ScalarOperator>
                                      <Convert DataType="int" Style="0" Implicit="true">
                                        <ScalarOperator>
                                          <Identifier>
                                            <ColumnReference Column="@2" />
                                          </Identifier>
                                        </ScalarOperator>
                                      </Convert>
                                    </ScalarOperator>
                                  </ColumnReference>
                                </Identifier>
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </Predicate>
                      </TableScan>
                    </RelOp>
                  </Top>
                </RelOp>
              </ComputeScalar>
            </RelOp>
          </Update>
        </RelOp>
        <ParameterList>
          <ColumnReference Column="@1" ParameterCompiledValue="'bijude 1 '" ParameterRuntimeValue="'bijude 1 '" />
          <ColumnReference Column="@2" ParameterCompiledValue="(4)" ParameterRuntimeValue="(30)" />
        </ParameterList>
      </QueryPlan>
    </StmtSimple>
  </Statements>
</Batch>

1
  • 1
    Just use UPDATE sample SET name='cat' WHERE id=@id; - if no rows are found to update nothing will be updated. Mar 18 '15 at 13:19
2
  • Your plans show that the only option for looking up a scalar id in the table is a scan. This clearly indicates a missing index.
  • Your code is incorrect under concurrency.
  • Query plans have to work for any parameter value.

To give an analogy, consider this example in a non-SQL language:

int x;
void f(int i)
{
 if (i != 1)
  x++;
}

You are asking the equivalent of "when I'm calling the function with the value 1, why did the compiler generate the code for x++, even though is not executed?"

You are approaching this from a completely wrong angle, trying to second guess optimizer choices without understanding how queries are executed. Read:

2
  • Agree with the optimizer will generate a general plan , but why this much cost? And I did liked that analogy. Oops I came to conclusion with out proper understanding , need to go back and try hard.
    – Biju jose
    Mar 18 '15 at 9:50
  • If you want to understand why the cost is 75%, post the actual plan (.sqlplan XML) not a picture. It contains information that can explain the cost. You can also inspect the XML yourself, or use a better plan viewer like SQL Sentry Plan Explorer. Mar 18 '15 at 11:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.