Linked Server Query In A View

  • Hi,

    I hope someone can offer some advice....

    I have the following query which works pretty well when I run it in SSMS:

    SELECT

    detail.order_no,

    detail.product,

    detail.val,

    header.customer

    FROM

    [SALESSQL].salesdb.dbo.OrderDetails AS detail

    JOIN [SALESSQL].salesdb.dbo.OrderHeaders AS header

    ON header.order_no = detail.order_no

    JOIN dbo.Company AS company

    ON company.comp_erpcust = header.customer COLLATE Latin1_General_Bin

    WHERE

    header.order_no = '203927 '

    The actual execution plan is attached - working.sqlplan.

    I need to put this in a view because the CRM system that calls it will only select from a table or a view, so here is the View definition:

    CREATE VIEW vw_test

    AS

    SELECT

    detail.order_no,

    detail.product,

    detail.val,

    header.customer

    FROM

    [SALESSQL].salesdb.dbo.OrderDetails AS detail

    JOIN [SALESSQL].salesdb.dbo.OrderHeaders AS header

    ON header.order_no = detail.order_no

    JOIN dbo.Company AS company

    ON company.comp_erpcust = header.customer COLLATE Latin1_General_Bin

    Now, when I run this query :

    SELECT

    order_no,

    product,

    val,

    customer

    FROM

    vw_test

    WHERE

    order_no = '203927 '

    I get a new execution plan that performs the filtering in the local database instead of remotely :angry:

    The estimated execution plan for this query is attached - not_working.sqlplan.

    The two database have different collations, the remote one using Latin1_General_Bin, the local uses Latin1_General_CI_AS.

    Does anyone have any idea how I can re-write this VIEW such that the filtering is performed remotely as it is in the standalone query?

    Thank you for your time.

    EDIT****: I cannot attach the sqlplans - yes I can!

    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]

  • Silly question, Magoo, since this has stayed quiet a bit. If you put the where clause IN the view, and then call the result from the view, do you still get the bad plan?

    I know this has something to do with the optimizer pre- or post- processing when dealing with linked servers, but I can't remember all the rules, especially when it comes to non-materialized views.

    This isn't a simplified version of the view, is it? It doesn't have anything like ROW_NUMBER() or other computed result components hiding in it?


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Thanks Craig - any offer of advice on this one is welcome!

    That is almost the exact query - although it is a simplified version of my real query - all I have done is reduce the number of columns in the SELECT to make it cleaner.

    There are no other complications - no row_numbers or other tables or extra predicates , just more columns from the two tables.

    I am working on the exact query posted here now - once I get that working I will try adding in more columns.

    I will try the WHERE clause inside the view to see what happens - post back in a bit.

    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]

  • Craig, if I put the WHERE clause inside the VIEW then

    SELECT * from vw_test

    Works just fine.

    Do you think the collation difference could be the cause and do you have any thoughts on setting collation settings on the Linked Server?

    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]

  • Ah, my brain coughed up something from the bottom of my soul and... oh, wait, I may have just remembered it.

    I can't get to skydrive, cause it's blocked from work and they don't want me uploading the financial world to my private collection. Can't say I blame them. However, can you check out your sqlplans and see if you went to timeout on the optimization plan?

    Also, can you test a SELECT * FROM (<select from the view>) WHERE header.field = 'yournumberhere '?

    I'm curious to see if you get the same full data pull back when it's both a subquery as well as a view (which is supposed to ACT like a subquery).

    I have a feeling it's because the view isn't allowing for the parameter passback through the MSDTC.

    Your next step (I know, you'll want to shoot me) is a temp table insert from a procedure call on the foreign server to FORCE it to execute foreignly and only return the data you need... and then do what you need to locally with the result set.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • mister.magoo (1/18/2011)


    Craig, if I put the WHERE clause inside the VIEW then

    SELECT * from vw_test

    Works just fine.

    Do you think the collation difference could be the cause and do you have any thoughts on setting collation settings on the Linked Server?

    Dangit, I was afraid of that.

    I think it's a combination of factors that is causing the Linked Server and the optimizer to choke on it. Eventually the optimizer just says 'to heck with it, gimme!' from the foreign server and goes from there.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Craig Farrell (1/18/2011)


    Ah, my brain coughed up something from the bottom of my soul and... oh, wait, I may have just remembered it.

    I can't get to skydrive, cause it's blocked from work and they don't want me uploading the financial world to my private collection. Can't say I blame them. However, can you check out your sqlplans and see if you went to timeout on the optimization plan?

    NO, the non-working plan is an estimate - I can't leave it running as there are too many records to allow that sort of load on the servers/network.

    Also, can you test a SELECT * FROM (<select from the view>) WHERE header.field = 'yournumberhere '?

    I'm curious to see if you get the same full data pull back when it's both a subquery as well as a view (which is supposed to ACT like a subquery).

    No, trying the subquery works just fine - no full data pull.

    I have a feeling it's because the view isn't allowing for the parameter passback through the MSDTC.

    Your next step (I know, you'll want to shoot me) is a temp table insert from a procedure call on the foreign server to FORCE it to execute foreignly and only return the data you need... and then do what you need to locally with the result set.

    Well, my next step is going to be to replicate the required data across between the servers - sending updates regularly will be less load than a full pull every time!

    But I have allowed myself one more day (spare time only) to try and resolve this...

    I am restricted by the third party CRM package that can only SELECT from a SET - be it a table or a a view...

    Thanks for your time and help.

    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]

  • This may sound odd but...

    What are the security settings on the linked server and what permissions do the linked accounts have on the remote server?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • mister.magoo (1/18/2011)


    Craig Farrell (1/18/2011)


    Ah, my brain coughed up something from the bottom of my soul and... oh, wait, I may have just remembered it.

    I can't get to skydrive, cause it's blocked from work and they don't want me uploading the financial world to my private collection. Can't say I blame them. However, can you check out your sqlplans and see if you went to timeout on the optimization plan?

    NO, the non-working plan is an estimate - I can't leave it running as there are too many records to allow that sort of load on the servers/network.

    Doesn't matter, the timout will be there as it's a compile-time property.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi Gail, thanks for your time.

    Security is currently (for testing purposes) set to log in remotely as "sa" - I have also tried using the login that owns the tables

    (It didn't sound odd - I have just been reading about the security aspect of distributed queries and potentially poor performance due to inability to read the stats....)

    There is no mention of the word timeout in the sqlplan.

    I could paste it in here but that may upset someone!

    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]

  • At last - used IE and i can upload the plans!

    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]

  • Well now, that's intriguing. It's ignoring the join logic and simply sending the where clause, but it's splitting the where clause to the two foreign tables and doing two unique SELECT calls to the underlying foreign schema in the working version. In the non-working version, which is insulated by the view, it doesn't perform the where clause component of this task, thus the data volume.

    I have no idea how to fix this (yet), but at least it nails down the specific problem.

    EDIT: Sonofagun. I'll want someone to verify me on this, I haven't browsed XML plans enough, but it seems to have parameterized the optimization plan for the call with the view. This turned Filter StartupExpression="false", and thus doesn't pass it as a constant.

    The optimizer decided to create a re-usable plan.

    Try it with OPTION( RECOMPILE)?


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Craig Farrell (1/18/2011)


    Well now, that's intriguing. It's ignoring the join logic and simply sending the where clause, but it's splitting the where clause to the two foreign tables and doing two unique SELECT calls to the underlying foreign schema in the working version. In the non-working version, which is insulated by the view, it doesn't perform the where clause component of this task, thus the data volume.

    I have no idea how to fix this (yet), but at least it nails down the specific problem.

    Yes, intriguing is one word for it!

    It at least sends the WHERE clause to each remote query (in the working one) so that I only get back the rows I need...

    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]

  • I have now tested a straight select from one of the remote tables and get the same problem when I put that inside a view...

    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]

  • mister.magoo (1/18/2011)


    I have now tested a straight select from one of the remote tables and get the same problem when I put that inside a view...

    Gyeah, okay, so you've got forced parameterization from the optimizer once you start layering the view into play, period. Good troubleshoot.

    Hm. I don't have 2k8 to play with to try workarounds, so you're kind of my guinea pig here... which would probably help if I had any ideas to experiment with! :hehe:

    Doing a bit of research. You've piqued my interest.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Viewing 15 posts - 1 through 15 (of 23 total)

    You must be logged in to reply to this topic. Login to reply