SQL Server query performance issue using synonyms -

i check synonym table records against database table.

the following statement works fine me, rid of insert , temporary table:

if object_id('tempdb..#tmp_table') not null      drop table #tmp_table  select      id      #tmp_table     synonyms_table (nolock)       created_date between getdate() - 4 , getdate() - 1   select id #tmp_table tmp     left join main_table main (nolock) on tmp.id collate sql_latin1_general_cp1_ci_as = main.id main.id null 

the problem is, both tables huge , if going use left join or not exist, going slow:

select id  synonyms_table sy (nolock)  left join main_table main (nolock) on sy.id collate sql_latin1_general_cp1_ci_as = main.id sy.created_date between getdate() - 4 , getdate() - 1    , main.id null 

maybe here knows solutions me :)

you try this, bearing in mind optimiser might decide run execution plan goes non-performant version:

with base (     select          id collate database_default id              synonyms_table (nolock)             created_date between dateadd(day, -4, getdate()) , dateadd(day, -1, getdate())) select     id     base b     left join main_table main (nolock) on b.id = main.id      main.id null; 

one of overheads collation, pre-collate data in common-table expression.