I have two sql server 2016 databases A and B. Database A retrieves data from database B using a linked server. The tsql statement used joins large tables from database B. To optimize I thought about using the openquery statement.
Since these are tables with millions of records, I am thinking of using a transactional replication in order to have the data table directly on database A, in order to process them locally.
What do you think of this solution? can you offer me other solutions?