>Got it. I only included the two parts that are different between the two. That is the queries to FLAIRTR2 and CATR1.
>
>Batch Version Plan:
>SELECT FLAIRTR2.*
>FROM FLAIRData.dbo.FlairTR2 FLAIRTR2
>INNER JOIN #Temp TMP
> ON FLAIRTR2.FiscalYear = TMP.FY
> AND FLAIRTR2.Account = TMP.Account
> AND FLAIRTR2.CFI = TMP.CFI
> AND FLAIRTR2.SWDN = TMP.SWDN
> AND FLAIRTR2.PrimaryDoc7 = TMP.Prima
>
>(1 row(s) affected)
>
>StmtText
>----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> |--Parallelism(Gather Streams, ORDER BY:([FLAIRTR2].[UniqueID] ASC))
> |--Sort(ORDER BY:([FLAIRTR2].[UniqueID] ASC))
> |--Compute Scalar(DEFINE:([FLAIRTR2].[PrimaryDoc7]=substring([FLAIRTR2].[PrimaryDoc], 1, 7)))
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([FLAIRData].[dbo].[FLAIRTR2] AS [FLAIRTR2]))
> |--Nested Loops(Inner Join, OUTER REFERENCES:([TMP].[Site], [TMP].[GLC], [TMP].[PrimaryDoc], [TMP].[SWDN], [TMP].[CFI], [TMP].[Account], [TMP].[FY]) WITH PREFETCH)
> |--Sort(ORDER BY:([TMP].[FY] ASC, [TMP].[Account] ASC, [TMP].[CFI] ASC, [TMP].[GLC] ASC, [TMP].[PrimaryDoc] ASC, [TMP].[SWDN] ASC, [TMP].[Site] ASC))
> | |--Table Scan(OBJECT:([tempdb].[dbo].[#Temp_______________________________________________________________________________________________________________000000001D3A] AS [TMP]))
> |--Index Seek(OBJECT:([FLAIRData].[dbo].[FLAIRTR2].[IX_FLAIRTR2_ReconKey] AS [FLAIRTR2]), SEEK:([FLAIRTR2].[FiscalYear]=[TMP].[FY] AND [FLAIRTR2].[Account]=[TMP].[Account] AND [FLAIRTR2].[CFI]=[TMP].[CFI] AND [FLAIRTR2].[GLC]=[TM
>
>(8 row(s) affected)
>
>StmtText
>----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>SELECT CATR1.*
>FROM FLAIRData.dbo.CATR1 CATR1
>INNER JOIN #Temp TMP
> ON CATR1.FiscalYear = TMP.FY
> AND CATR1.Account = TMP.Account
> AND CATR1.CFI = TMP.CFI
> AND CATR1.SWDN11 = TMP.SWDN
> AND CATR1.AgencyDocNumber = TMP.PrimaryDoc
> AND CATR1
>
>(1 row(s) affected)
>
>StmtText
>----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> |--Compute Scalar(DEFINE:([CATR1].[SWDN11]=substring([CATR1].[SWDN], 1, 11)))
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([FLAIRData].[dbo].[CATR1] AS [CATR1]))
> |--Parallelism(Gather Streams)
> |--Nested Loops(Inner Join, OUTER REFERENCES:([TMP].[Site], [TMP].[PrimaryDoc], [TMP].[SWDN], [TMP].[CFI], [TMP].[Account], [TMP].[FY]) WITH PREFETCH)
> |--Sort(ORDER BY:([TMP].[FY] ASC, [TMP].[Account] ASC, [TMP].[CFI] ASC, [TMP].[SWDN] ASC, [TMP].[PrimaryDoc] ASC, [TMP].[Site] ASC))
> | |--Table Scan(OBJECT:([tempdb].[dbo].[#Temp_______________________________________________________________________________________________________________000000001D3A] AS [TMP]))
> |--Index Seek(OBJECT:([FLAIRData].[dbo].[CATR1].[IX_CATR1_Recons] AS [CATR1]), SEEK:([CATR1].[FiscalYear]=[TMP].[FY] AND [CATR1].[Account]=[TMP].[Account] AND [CATR1].[CFI]=[TMP].[CFI] AND [CATR1].[SWDN11]=[TMP].[SWDN] AND [CATR1].[Ag
>
>
>
>
>I'm not able to get the SP Version's Plan since it uses a temporary table within the stored procedure. However, it does use a Clustered Index Scan and goes through all 17million records before the join.
>
>I'm thinking you're right, given the way I've done this. The RECOMPILE option (that a couple of people suggested) did not work though.
>
>Richard

Post the exact indexes definition on the two FLAIRData's tables and a solution we find it.