Difference between four part naming and execute at

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  February 12, 2014 at 4:11 am

  #289351

  Hi,

  This has to be a fairly general question because I am working with a linked server that I don't have admin permission on...

  So, I have this query

  SELECT col1, col2, col3

  FROM [Linked Server].[TestDB].[dbo].[SomeView]

  WHERE col1='Some Value'

  And this query

  EXECUTE('SELECT col1, col2, col3

  FROM [Linked Server].[TestDB].[dbo].[SomeView]

  WHERE col1='''Some Value''') AT [Linked Server]

  Col1 *should* be part of the primary key but I have no way of knowing for sure.

  The EXECUTE AT query returns instantly, as expected (only 3 rows), but the FQN query takes 20+seconds.

  The four part naming query is shown as a Remote Query in the execution plan, including the remote predicate, so it is only bringing those three rows back, but takes much longer to do so.

  Is there anything I can do from this side to enable the four part naming to work as efficiently as the "execute at" query?

  Note, to further muddy the waters, there are other views available to me for similar data (this problem view is prices for order lines, there is another for costs for the order lines) that work just fine both ways.

  To recap: the view is efficient when queried using EXECUTE AT, but slow when queried using four part naming.

  The linked server setup is this:

  

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]