View vs plain query Performance problems

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

View vs plain query Performance problems

Author

Topic

raowyr
Starting Member

7 Posts

Posted - 2013-06-05 : 06:45:37

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 06:49:38

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

raowyr
Starting Member

7 Posts

Posted - 2013-06-05 : 07:04:40

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...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 07:09:36

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 07:11:18

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

raowyr
Starting Member

7 Posts

Posted - 2013-06-05 : 07:16:37

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.

raowyr
Starting Member

7 Posts

Posted - 2013-06-05 : 07:18:04

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 07:18:50

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

raowyr
Starting Member

7 Posts

Posted - 2013-06-05 : 07:24:56

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 07:29:29

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

raowyr
Starting Member

7 Posts

Posted - 2013-06-05 : 07:42:40

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! =(

raowyr
Starting Member

7 Posts

Posted - 2013-06-06 : 07:02:12

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 07:14:17

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources