Can this sproc statement be written differently

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.
All Forums
SQL Server 2012 Forums
Transact-SQL (2012)
Can this sproc statement be written differently
Author
Topic
mgreen84
Yak Posting Veteran
94 Posts
Posted - 2015-02-02 : 12:37:20
Is there faster method to write this script?

Select top 100 etl_source_id
,source_name
,sub_source_name
,source_location
,exp_record_cnt
,act_record_cnt
,received_dt
,source_type
,delimiter
,line_term
,file_size
,load_program
,content_type
,content_sub_type
,mod_dt
,active_flag
,processed_flag

From server1.rdc_CZ.dbo.cz_source_manager

Where etl_source_id not in (select etl_source_id from server2.rdc_CZ.dbo.cz_source_manager)
ScottPletcher
Aged Yak Warrior
550 Posts
Posted - 2015-02-02 : 12:59:17
How many rows are in each table?

Which index(es), if any, in each table contain etl_source_id?
tkizer
Almighty SQL Goddess
38200 Posts
Posted - 2015-02-02 : 13:24:13
Post the execution plan in xml format.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
mgreen84
Yak Posting Veteran
94 Posts
Posted - 2015-02-02 : 15:15:08
Execution Plan:

<?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.2" Build="11.0.5058.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="937557" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="1218.67" StatementText="SELECT etl_source_id,source_name,sub_source_name,source_location,exp_record_cnt,act_record_cnt,received_dt,source_type,delimiter,line_term,file_size,load_program,content_type,content_sub_type,mod_dt,active_flag,processed_flag  #xD;#xA;		FROM PHCMA280.rdc_CZ.dbo.cz_source_manager  --must point to production in all environments, do not change to local environment --@DK01242014#xD;#xA;		where etl_source_id not in (select etl_source_id from rdc_CZ.dbo.cz_source_manager)" StatementType="SELECT" QueryHash="0x701022D81762979F" QueryPlanHash="0x1811ECDC8DA31F45" RetrievedFromCache="false">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan CachedPlanSize="72" CompileTime="215" CompileCPU="23" CompileMemory="416">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="393216" EstimatedPagesCached="786432" EstimatedAvailableDegreeOfParallelism="8" />
            <RelOp AvgRowSize="1849" EstimateCPU="14.6045" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="937557" LogicalOp="Left Anti Semi Join" NodeId="0" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="1218.67">
              <OutputList>
                <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="etl_source_id" />
                <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="source_name" />
                <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="sub_source_name" />
                <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="source_location" />
                <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="exp_record_cnt" />
                <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="act_record_cnt" />
                <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="received_dt" />
                <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="source_type" />
                <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="delimiter" />
                <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="line_term" />
                <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="file_size" />
                <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="load_program" />
                <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="content_type" />
                <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="content_sub_type" />
                <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="mod_dt" />
                <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="active_flag" />
                <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="processed_flag" />
              </OutputList>
              <Merge ManyToMany="false">
                <InnerSideJoinColumns>
                  <ColumnReference Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="etl_source_id" />
                </InnerSideJoinColumns>
                <OuterSideJoinColumns>
                  <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="etl_source_id" />
                </OuterSideJoinColumns>
                <Residual>
                  <ScalarOperator ScalarString="[PHCMA280].[rdc_CZ].[dbo].[cz_source_manager].[etl_source_id]=[rdc_CZ].[dbo].[cz_source_manager].[etl_source_id]">
                    <Compare CompareOp="EQ">
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="etl_source_id" />
                        </Identifier>
                      </ScalarOperator>
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="etl_source_id" />
                        </Identifier>
                      </ScalarOperator>
                    </Compare>
                  </ScalarOperator>
                </Residual>
                <RelOp AvgRowSize="1849" EstimateCPU="1151.21" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3453610" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1151.21">
                  <OutputList>
                    <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="etl_source_id" />
                    <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="source_name" />
                    <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="sub_source_name" />
                    <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="source_location" />
                    <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="exp_record_cnt" />
                    <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="act_record_cnt" />
                    <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="received_dt" />
                    <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="source_type" />
                    <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="delimiter" />
                    <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="line_term" />
                    <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="file_size" />
                    <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="load_program" />
                    <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="content_type" />
                    <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="content_sub_type" />
                    <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="mod_dt" />
                    <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="active_flag" />
                    <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="processed_flag" />
                  </OutputList>
                  <ComputeScalar>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="etl_source_id" />
                        <ScalarOperator ScalarString="[PHCMA280].[rdc_CZ].[dbo].[cz_source_manager].[etl_source_id]">
                          <Identifier>
                            <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="etl_source_id" />
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="source_name" />
                        <ScalarOperator ScalarString="[PHCMA280].[rdc_CZ].[dbo].[cz_source_manager].[source_name]">
                          <Identifier>
                            <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="source_name" />
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="sub_source_name" />
                        <ScalarOperator ScalarString="[PHCMA280].[rdc_CZ].[dbo].[cz_source_manager].[sub_source_name]">
                          <Identifier>
                            <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="sub_source_name" />
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="source_location" />
                        <ScalarOperator ScalarString="[PHCMA280].[rdc_CZ].[dbo].[cz_source_manager].[source_location]">
                          <Identifier>
                            <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="source_location" />
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="exp_record_cnt" />
                        <ScalarOperator ScalarString="[PHCMA280].[rdc_CZ].[dbo].[cz_source_manager].[exp_record_cnt]">
                          <Identifier>
                            <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="exp_record_cnt" />
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="act_record_cnt" />
                        <ScalarOperator ScalarString="[PHCMA280].[rdc_CZ].[dbo].[cz_source_manager].[act_record_cnt]">
                          <Identifier>
                            <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="act_record_cnt" />
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="received_dt" />
                        <ScalarOperator ScalarString="[PHCMA280].[rdc_CZ].[dbo].[cz_source_manager].[received_dt]">
                          <Identifier>
                            <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="received_dt" />
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="source_type" />
                        <ScalarOperator ScalarString="[PHCMA280].[rdc_CZ].[dbo].[cz_source_manager].[source_type]">
                          <Identifier>
                            <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="source_type" />
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="delimiter" />
                        <ScalarOperator ScalarString="[PHCMA280].[rdc_CZ].[dbo].[cz_source_manager].[delimiter]">
                          <Identifier>
                            <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="delimiter" />
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="line_term" />
                        <ScalarOperator ScalarString="[PHCMA280].[rdc_CZ].[dbo].[cz_source_manager].[line_term]">
                          <Identifier>
                            <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="line_term" />
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="file_size" />
                        <ScalarOperator ScalarString="[PHCMA280].[rdc_CZ].[dbo].[cz_source_manager].[file_size]">
                          <Identifier>
                            <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="file_size" />
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="load_program" />
                        <ScalarOperator ScalarString="[PHCMA280].[rdc_CZ].[dbo].[cz_source_manager].[load_program]">
                          <Identifier>
                            <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="load_program" />
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="content_type" />
                        <ScalarOperator ScalarString="[PHCMA280].[rdc_CZ].[dbo].[cz_source_manager].[content_type]">
                          <Identifier>
                            <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="content_type" />
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="content_sub_type" />
                        <ScalarOperator ScalarString="[PHCMA280].[rdc_CZ].[dbo].[cz_source_manager].[content_sub_type]">
                          <Identifier>
                            <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="content_sub_type" />
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="mod_dt" />
                        <ScalarOperator ScalarString="[PHCMA280].[rdc_CZ].[dbo].[cz_source_manager].[mod_dt]">
                          <Identifier>
                            <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="mod_dt" />
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="active_flag" />
                        <ScalarOperator ScalarString="[PHCMA280].[rdc_CZ].[dbo].[cz_source_manager].[active_flag]">
                          <Identifier>
                            <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="active_flag" />
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="processed_flag" />
                        <ScalarOperator ScalarString="[PHCMA280].[rdc_CZ].[dbo].[cz_source_manager].[processed_flag]">
                          <Identifier>
                            <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="processed_flag" />
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                    </DefinedValues>
                    <RelOp AvgRowSize="1849" EstimateCPU="1151.21" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3453610" LogicalOp="Remote Query" NodeId="2" Parallel="false" PhysicalOp="Remote Query" EstimatedTotalSubtreeCost="1151.21">
                      <OutputList>
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="etl_source_id" />
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="source_name" />
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="sub_source_name" />
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="source_location" />
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="exp_record_cnt" />
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="act_record_cnt" />
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="received_dt" />
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="source_type" />
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="delimiter" />
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="line_term" />
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="file_size" />
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="load_program" />
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="content_type" />
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="content_sub_type" />
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="mod_dt" />
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="active_flag" />
                        <ColumnReference Server="[PHCMA280]" Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="processed_flag" />
                      </OutputList>
                      <RemoteQuery RemoteSource="PHCMA280" RemoteQuery="SELECT "Tbl1002"."etl_source_id" "Col1008","Tbl1002"."source_name" "Col1009","Tbl1002"."sub_source_name" "Col1010","Tbl1002"."source_location" "Col1011","Tbl1002"."exp_record_cnt" "Col1012","Tbl1002"."act_record_cnt" "Col1013","Tbl1002"."received_dt" "Col1014","Tbl1002"."source_type" "Col1015","Tbl1002"."delimiter" "Col1016","Tbl1002"."line_term" "Col1017","Tbl1002"."file_size" "Col1018","Tbl1002"."load_program" "Col1019","Tbl1002"."content_type" "Col1020","Tbl1002"."content_sub_type" "Col1021","Tbl1002"."mod_dt" "Col1022","Tbl1002"."active_flag" "Col1023","Tbl1002"."processed_flag" "Col1006" FROM "rdc_CZ"."dbo"."cz_source_manager" "Tbl1002" ORDER BY "Col1008" ASC" />
                    </RelOp>
                  </ComputeScalar>
                </RelOp>
                <RelOp AvgRowSize="15" EstimateCPU="3.79912" EstimateIO="49.0505" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3453610" LogicalOp="Clustered Index Scan" NodeId="38" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="52.8497" TableCardinality="3453610">
                  <OutputList>
                    <ColumnReference Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="etl_source_id" />
                  </OutputList>
                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Column="etl_source_id" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[rdc_CZ]" Schema="[dbo]" Table="[cz_source_manager]" Index="[PK__cz_file__07020F22]" IndexKind="Clustered" />
                  </IndexScan>
                </RelOp>
              </Merge>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>
tkizer
Almighty SQL Goddess
38200 Posts
Posted - 2015-02-02 : 15:21:40
Unfortunately I'm not able to open that XML plan, and I need to see the GUI for it. But I did spot a clustered index scan of over 3 million rows. I would start there, checking to see if there's an index that can be added to avoid the scan.

I would probably also rewrite it to not use NOT IN. I usually use NOT EXISTS or LEFT JOIN with IS NULL. Those might get a better plan than NOT IN, though it might be equivalent. Something for you to test.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
mgreen84
Yak Posting Veteran
94 Posts
Posted - 2015-02-02 : 15:39:19
Yeah I'll start with the index. Iknow not exist has gotten me better performance vs the left join in the past

Thanks
ScottPletcher
Aged Yak Warrior
550 Posts
Posted - 2015-02-02 : 16:37:12
You really should comprehensively review the indexing, including looking at index missing and index usage stats. Most likely the tables should be clustered on etl_source_id rather than an identity column (my best guess).

If you want to do that, let me know.
Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS
SQLTeam.com Weblog via RSS
- Advertisement -
Resources
