I have a SQL Server 2016 instance and a MySQL instance, where both are on seperate VMs running on the SAME physical server. The MySQL is linked to SQL Server.
My question is, what are possible latency causes for the data transfer? I ran a query on MySQL that executed in 9 seconds. The same query I ran via Linked Server in SSMS (using OPEN QUERY) and that took 56 seconds to execute.
I am trying to pinpoint the causes of the lag to fix this before scaling! All opinions and suggestions are greatly appreciated.