I have a problem with performance of a query embedded in some 3rd party software.
The query (see below) performs a select of some fields on one server WHERE the product_id is IN a sub-query that exists on another server.
WHERE ServiceProvider.[ServiceProviderGuid] IN (
WHERE [vwCRMServiceProviderAccess].[UserGuid] = '4D569648-C703-47BA-BF66-D8E39F5C9D49' )
The sub-query runs against a view which contains a fully qualified reference to a table on another server.
Here is the issue: I can run the main query and it returns in less than 1 second. I can extract just the sub-query and it runs in 1 second when executed from main server, performing a cross-server query. But when these are both run in the full query, it takes 33 seconds to return!
I can't figure out what would cause this. I've attempted to trace this using profiler. I can easily capture information regarding the base query, but I am unable to see the cross-server portion from a profiler trace running on the remote server.
The actual execution plan shows that 98% of the cost of running this query resides in the remote query portion, but I am stumped by the fact that I can just execute that sub-query portion and have it return in 1 second.
Do you have any ideas about how I might be able to trouble-shoot this, or what might be happening?
Thanks in advance for your thoughts and advice.