September 22, 2011 at 7:28 am
Dear all,
I am working on tuning a query and thought to share my experience with you and ask some questions that have been puzzling me.
I am attaching the original query and its execution plan. I created suitable indexes but it led to index scan heavily.
Now, in the modified version, I created a temp table which imports all the data from linked server at once and then used this in joins with local tables and surprised to see that query runs in 4-5 seconds with index seeks on the tables I wanted to see. I did not change anything else.
My question is why this behavior was observed. I just avoided linked server table in the join, imported it locally and then used that in the join instead.
Please let me know what actually happened here.
Thanks
Chandan
September 22, 2011 at 7:36 am
The short answer is that neither of the servers know what the other server sees.
So the safest way to go about building a plan is to download all data and hack it out once you get there.
What changed in the second plan is that the server could better estimate the output so it produced a little <lot> more efficient plan.
September 22, 2011 at 7:49 am
Ninja's_RGR'us (9/22/2011)
The short answer is that neither of the servers know what the other server sees.So the safest way to go about building a plan is to download all data and hack it out once you get there.
What changed in the second plan is that the server could better estimate the output so it produced a little <lot> more efficient plan.
Thanks. The original query is actually a view but i simply expanded this for it to look better. In case I suggest them to import this in a temp table, how can I implement this in the view. I think their current application executes a query against this view only. If there is no other option to continue with the view, the only option I see is going for a procedure.Please suggest
PS: I remember your famous quote 'Nowhere in this world we can break such a huge procedure on this forum' .
But this is a small one, so looking for your opinion:-P
Regards
Chandan
September 22, 2011 at 7:59 am
Oh darn, a view in a view + slow linked server.
Sounds like I'd do a sp for each report they need.
I'd also look at some sort of replication, or at least copying the new data over every x minutes or hours.
September 22, 2011 at 11:46 am
Ninja's_RGR'us (9/22/2011)
Oh darn, a view in a view + slow linked server.Sounds like I'd do a sp for each report they need.
I'd also look at some sort of replication, or at least copying the new data over every x minutes or hours.
Its not a nested view. Its just a simple view having some joins in a local table and involving one remote table in this join.
Does it mean that whenever a remote table is involved in a join, local indexes won't help?
September 23, 2011 at 5:30 am
chandan_jha18 (9/22/2011)
Ninja's_RGR'us (9/22/2011)
Oh darn, a view in a view + slow linked server.Sounds like I'd do a sp for each report they need.
I'd also look at some sort of replication, or at least copying the new data over every x minutes or hours.
Its not a nested view. Its just a simple view having some joins in a local table and involving one remote table in this join.
Does it mean that whenever a remote table is involved in a join, local indexes won't help?
I,m not studied this deep, but in my experience unless you pass via a local temp table, there's no way to make it run fast.
You have to find a way to make the "whole thing" run either locally or remotely. Not mixed.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply