January 18, 2011 at 12:20 pm
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);January 18, 2011 at 2:37 pm
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?
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
January 18, 2011 at 3:17 pm
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);January 18, 2011 at 3:23 pm
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);January 18, 2011 at 3:26 pm
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.
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
January 18, 2011 at 3:27 pm
mister.magoo (1/18/2011)
Craig, if I put the WHERE clause inside the VIEW thenSELECT * 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.
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
January 18, 2011 at 3:39 pm
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);January 18, 2011 at 3:48 pm
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
January 18, 2011 at 3:51 pm
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
January 18, 2011 at 4:04 pm
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);January 18, 2011 at 4:09 pm
At last - used IE and i can upload the plans!
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 18, 2011 at 4:33 pm
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)?
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
January 18, 2011 at 4:37 pm
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);January 18, 2011 at 4:38 pm
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);January 18, 2011 at 4:42 pm
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.
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