>None of those worked... Thanks though
>
Strange.

if you execute the sp in QA, and after run the INDEX OPTIMIZATION ?

>>>Because these tables come from a mainframe, the unique clustered index comes from NEWID(). I'm still looking for a real key.
>>
>>Very often who writes the software it doesn't plan it with the fundamental principle:
>>the coherence and the invariance of the rules.
>>Probably MSSQL it has different rules in to assign the costs of batch and sp.
>>
>>However
>>
>>

>>-- we try to deceive it
>>
>>SELECT FLAIRTR2.*
>>FROM FLAIRData.dbo.FlairTR2
>>WHERE pk IN
>> (select FLAIRTR2.pk   -- pk has to be or the key of the clustered index or it has to belong to the index that we want that SQL uses
>>   FROM FLAIRData.dbo.FlairTR2 FLAIRTR2
>>    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)
>>
>>-- we force the master JOIN table to #Temp
>>SELECT FLAIRTR2.*
>>FROM #Temp TMP
>>  JOIN FLAIRData.dbo.FlairTR2 FLAIRTR2
>>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
>>OPTION ( FORCE ORDER )
>>
>>
>>-- we force the FLAIRTR2 index
>>SELECT FLAIRTR2.*
>>FROM #Temp TMP
>>  JOIN FLAIRData.dbo.FlairTR2 FLAIRTR2 WITH ( INDEX (indexname) )
>>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
>>
>>

>>The force solutions is static,
>>and therefore it is a desperate solution.