I have a performance problem related to SQL Server/linked servers/views.
I hope you can help me understand what is the best way to do what I want =).
- I have a database K with 3 linked servers L1,L2,L3 to 3 databases X,Y,Z.
- In X,Y,Z i have, respectively, 3 views called V1,V2,V3.
- I want to query the union of V1,V2,V3 by database K with linked server L1,L2,L3.
After some testing, this is the situation:
- In SSMS, if I run this pseudo query
SELECT * FROM (L1.V1 u L2.V2 u L3.V3) WHERE some filters
the performance is really great - If I create a view VK in database K, which contains the union of the three views and then i run the query
SELECT * FROM VK WHERE some filters
the performance is worse than case 1
Questions
- Why is the performance so different?
- How can I improve performance in case 2?
I'm interested in improving performance in case 2 because I need a view to map with nHbinernate in our software…
Thanks in advance, regards
UPDATE AFTER JOHN ALAN's POST
Ok, I try but with no results.
I'm not a DBA and my skills on DB configuration is really limited.
Can we proceed step by step?
-
On remote server (
called Y
) I created a new account (called linkedserver
) by security->logins->new login. I select login name then sql authentication and I choose a password. for deafault database I selectmaster
. Inserver roles
tab I selectpublic
. inUser mapping
tab I select the databases involved in remote queries and, for each of them, I selectdb_ddladmin
andpublic
role. Then, for each database involved in remote query, I checked the effective permission for linkedserver user and there are a lot ofALTER
and a lot ofCREATE
permissions but not SHOW PLAN (then I selected this one too). -
On the database server (
called X
) where the linked server to Y exists, I created a linked server (called L1
). In security TAB, I selectedlocal user sa
andremote user linkedserver
with its password.
When i run the query across L1 without a VIEW I have good result, If I put the query in a VIEW low performace, nothing have changed…
I think I did some step wrong, but I don't know where…
FOR BETTER CLARITY
This is the query I run without a view using linked server:
select * from
(
select * from dolph.agendasdn.dbo.vistaaccettazionegrp
union
select * from dolph.acampanet.dbo.vistaaccettazionegrp
union
select * from municipio.dbnet.dbo.vistaaccettazionegrp
) a
where cognome = 'test' and nome = 'test'
into the view I put only this code
select * from dolph.agendasdn.dbo.vistaaccettazionegrp
union
select * from dolph.acampanet.dbo.vistaaccettazionegrp
union
select * from municipio.dbnet.dbo.vistaaccettazionegrp
then I called select * from VIEW where cognome = 'test' and nome = 'test'
So..
- 1st case 0-1 seconds.
- 2nd case >15 seconds…
I think this is absurd!
EXECUTION PLAN
The execution plan with plain query, without the use of a view:
the execution plan using the view:
Best Answer
Your problem begins and ends with statistics and estimates. I have reproduced your situation on my servers and found some interesting hints, and a workaround solution.
First things first, let's take a look at your execution plan:
When a view is used we can see that a filter is applied after the Remote Query is executed, while without the view there was no filter applied at all. The truth is that the filter was applied inside the Remote Query, at the remote server, before retrieving the data over the network.
Well, obviously applying the filter at the remote server and thus retrieving less data is a better option, and obviously that only happens when not using a view.
So... what is so intersting...?
Surprisingly, when I changed the filter from
cognome = 'test'
tocognome = N'test'
(unicode representation of the string) the view used the same execution plan as the first query did.I guess the reason is that somehow when using the view SQL Server estimated that there will be a small number of rows returning from the (remote) query, and that a local filtering will be cheaper, but when SQL Server had to implicit convert
NVARCHAR
toVARCHAR
, statistics could no longer be used and the decision to filter locally was not taken.I have looked for the statistics locally, but the view had no statistics, so my guess is that the view uses the remote statistics in a way that ad-hoc query does not, and than takes the wrong decision.
OK, so what solves the problem?
I stated earlier that there is a workaround (at least until someone comes up with a better solution), and no, I don't mean using unicode for your strings.
I wanted to give an answer first, I still have to find why, but when using an
Inline Function
SQL Server behaves exactly the same as with the query (without view), so replacing the view with the function will give the same result, in a simple query, and with good peformance (at least in my environment).My code suggestion for you is:
The query will then be:
This works on my servers, but of course test it first.
Note: I do not recommend using
SELECT *
at all, as it is prone to future errors, I simply used it because it was in your question and there was no need for me to change that when I can add this remark instead :)