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 filtersthe 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 filtersthe performance is worse than case 1
- 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 select
server rolestab I select
User mappingtab I select the databases involved in remote queries and, for each of them, I select
publicrole. Then, for each database involved in remote query, I checked the effective permission for linkedserver user and there are a lot of
ALTERand a lot of
CREATEpermissions 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 selected
local user saand
remote user linkedserverwith 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'
- 1st case 0-1 seconds.
- 2nd case >15 seconds...
I think this is absurd!
The execution plan with plain query, without the use of a view:
the execution plan using the view: