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