SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 View vs plain query Performance problems
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raowyr
Starting Member

7 Posts

Posted - 06/05/2013 :  06:45:37  Show Profile  Reply with Quote
Hi guys,
i have a big troubles with a view and its performance.

My situation is this:

I have a Database with 2 linked server (sa is the user) to 2 other sql server instances on two differents server.

I have to do the union of three views hosted on the 2 linked server.

The query is really simple, but, my problem is that if I run the plain union query inside sql managment studio, with a filter on surname, the performance are great (1/2 seconds), if I create a view with the union inside and then i filter the view result the performace are really bad (>20 secs!!!).

Here the 2 query and query plan

1. Plain query without using view

select * from (
select * from dolph2.agendasdn.dbo.vistaanagraficagrp
union
select * from dolph2.acampanet.dbo.vistaanagraficagrp
union
select * from municipio2.dbnet.dbo.vistaanagraficagrp
) a where cognome = 'prova'

http://www.freeimagehosting.net/gr87r

2. Query using the union inside a view

select * from anagrafiche2 where cognome = 'prova'

http://www.freeimagehosting.net/js2ng

Someone can help me to understand and improve the 2case?

I need to put the query into the view beacuse i need to map it with hibernate into our software application.

=) thanks and regards

Edited by - raowyr on 06/05/2013 06:50:51

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/05/2013 :  06:49:38  Show Profile  Reply with Quote
Can you try putting the results of linked server tables onto two local tables and create the view using them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

raowyr
Starting Member

7 Posts

Posted - 06/05/2013 :  07:04:40  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

Can you try putting the results of linked server tables onto two local tables and create the view using them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Hi, thanks in advance.
I've doing what you say. I created 3 tables A1,A2,A3 (with data contained into the tables from linked servers) and then i run the queries:

1. select * from (select * from A1 Union select * from A2 Union select * from A3) a where cognome = 'esposito'

query on local and linked server have same performance

2. i put the union of the local A1,2,3 tables into the view

select * from anagrafiche2 where cognome = 'esposito'

in this case i have same performance than case 1.

But this can't be the solution. Data from linked server change frequently...

Edited by - raowyr on 06/05/2013 07:09:36
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/05/2013 :  07:09:36  Show Profile  Reply with Quote
why do you do filter at last

why not do filter while creating A1,A2,A3 itself
that way, you will be having a much less recordset to apply union on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/05/2013 :  07:11:18  Show Profile  Reply with Quote
Data from linked server change frequently

in that case, isnt it better to replicate it across? if you want to access tables frequently and they also change frequently

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

raowyr
Starting Member

7 Posts

Posted - 06/05/2013 :  07:16:37  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

why do you do filter at last

why not do filter while creating A1,A2,A3 itself
that way, you will be having a much less recordset to apply union on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




hi,
i need to get the union of information about persons take from 3 different database.

The filter is dynamic on surname, name and birthdate, so, i can't put this filter into the view.
Go to Top of Page

raowyr
Starting Member

7 Posts

Posted - 06/05/2013 :  07:18:04  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

Data from linked server change frequently

in that case, isnt it better to replicate it across? if you want to access tables frequently and they also change frequently

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




This can be a solution, but the question is:

why plain query across linked server is better than the union inside a view?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/05/2013 :  07:18:50  Show Profile  Reply with Quote
quote:
Originally posted by raowyr

quote:
Originally posted by visakh16

why do you do filter at last

why not do filter while creating A1,A2,A3 itself
that way, you will be having a much less recordset to apply union on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




hi,
i need to get the union of information about persons take from 3 different database.

The filter is dynamic on surname, name and birthdate, so, i can't put this filter into the view.


not in the view
what i suggested was to put it at table creation time
and in view you'll only have records for the person which you'll merge from all the three tables.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

raowyr
Starting Member

7 Posts

Posted - 06/05/2013 :  07:24:56  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by raowyr

quote:
Originally posted by visakh16

why do you do filter at last

why not do filter while creating A1,A2,A3 itself
that way, you will be having a much less recordset to apply union on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




hi,
i need to get the union of information about persons take from 3 different database.

The filter is dynamic on surname, name and birthdate, so, i can't put this filter into the view.


not in the view
what i suggested was to put it at table creation time
and in view you'll only have records for the person which you'll merge from all the three tables.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




It's a web application, with a lot of user which, at the same time, can search persons... i don't think is a good solution for this task.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/05/2013 :  07:29:29  Show Profile  Reply with Quote
you mean you're trying to do a union of three tables at runtime through an application used by lots of concurrent users. Doesnt sound like a good idea to me.
In that case why not connect directly to other databases rather than using linked server route?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

raowyr
Starting Member

7 Posts

Posted - 06/05/2013 :  07:42:40  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

you mean you're trying to do a union of three tables at runtime through an application used by lots of concurrent users. Doesnt sound like a good idea to me.
In that case why not connect directly to other databases rather than using linked server route?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



Beacause there is a limitation with hibernate (a framework used by programmatically side).
Hibernate need a table or view to map as an entity.

So, i need an unique point of access (table or view) which contains the union from 3 differente database.

But what i don't understand is why the plain query run fast than the union into the view, both across linked server! =(

Edited by - raowyr on 06/05/2013 07:42:54
Go to Top of Page

raowyr
Starting Member

7 Posts

Posted - 06/06/2013 :  07:02:12  Show Profile  Reply with Quote
Hi,
maybe someone can have my same problem in future then i post here an external link (i hope i can do this on this forum) where another user have found a reply to my question:

http://dba.stackexchange.com/questions/43754/sql-server-linked-server-and-query-performance/43874?noredirect=1#comment77612_43874

Edited by - raowyr on 06/06/2013 07:02:59
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/06/2013 :  07:14:17  Show Profile  Reply with Quote
quote:
Originally posted by raowyr

Hi,
maybe someone can have my same problem in future then i post here an external link (i hope i can do this on this forum) where another user have found a reply to my question:

http://dba.stackexchange.com/questions/43754/sql-server-linked-server-and-query-performance/43874?noredirect=1#comment77612_43874


thanks
certainly this would be beneficial for lot of people
Much appreciated in sharing this across!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000