Larry Kruse (8/29/2012)

---

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.

SELECT

ServiceProvider.[ServiceProviderGuid],

ServiceProvider.[ServiceProviderTypeGuid],

ServiceProvider.[ServiceProviderName],

ServiceProvider.[ServiceProviderId],

ServiceProvider.[MainPlant],

ServiceProvider.[ServiceProviderParentGuid]

FROM ServiceProvider

WHERE ServiceProvider.[ServiceProviderGuid] IN (

SELECTvwCRMServiceProviderAccess.[ServiceProviderGuid]

FROMvwCRMServiceProviderAccess

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.

Larry