I have an indexed local table and I am thinking of joining it to a linked server table since I do not have the information I need to the local table.
Either that or create a query that brings data to a local stage table from linked server and the join that local table. This will need maintenance however because is not a day to day table join so I do not know what data I might use and also the date range. So the stage table could well become huge.
I'm thinking of the complication this join will produce.
I will try to use filter to the linked table so I can avoid full data read and I am using indexed columns on local table. The problem is that some of the linked table joined columns are not indexed.
What are the complications that may be created in that case? Will the local table indexes be used or since the are missing from linked table, be ignored?
Also I am using read uncommitted transactions. Is this actually working when using linked tables?
If the linked table is slowing the select down, does this mean that the local database is also slowed and not using uncommitted reads?
Any other tips you might think to make performance better?