SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using a View from one database to a second database that returns a large result set


Using a View from one database to a second database that returns a large result set

Author
Message
DesmoShane
DesmoShane
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 454
I have been asked a question today that I am having a hard time providing an answer for and would like some assistance to understand the impacts on performance and get some guidance of how this works in 2008R2

There are 2 databases on one sql server. One database has a view that query’s the second database on the same sql server the view is a 'select * from table’ query and returns a large record set of around 20 million rows

My questions

1) How do the mechanics of this work in the sql engine level?
2) What impact is there on the destination database and on the originating database?
3) What happens when the data being referenced in the view changes in the originating db?
4) Are there any other considerations?

Any help to understand how this works at the engine level would be much appreciated
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32841 Visits: 18559

There are 2 databases on one sql server. One database has a view that query’s the second database on the same sql server the view is a 'select * from table’ query and returns a large record set of around 20 million rows

My questions

1) How do the mechanics of this work in the sql engine level?
2) What impact is there on the destination database and on the originating database?
3) What happens when the data being referenced in the view changes in the originating db?
4) Are there any other considerations?


1. As long as cross database permissions are in place, and since this is on the same server, there is little difference than if the view was referencing a table within the same database. You would want to reference the objects by 3 part names.
2. 20 million records in the select statement will have a bigger impact on tempdb
3. Data changes, then your results change - just the same as if the view were to reference a table within the same database.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Kalyan@Boddupalli
Kalyan@Boddupalli
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 26
As it is selecting 20 million records, its obvious that it effects the performance of other transactions on the server as the tempdb will be heavily loaded.
1. Make sure nolocks are maintained on the tables from where the data is getting selected
2. check the where clause is having the columns which are indexed
3. Check the query execution plan in the dev server for confirming there are no table scans(if table scans are there, then add indexes on the required columns)
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2135 Visits: 872
Kalyan Boddupalli (7/30/2013)
1. Make sure nolocks are maintained on the tables from where the data is getting selected


Do you mean that the NOLOCK hint should be used? Have ye no shame!

The NOLOCK hint can be very important when running diagnostic queries in a live system, but it rarely is a good thing to put in application code, and absolutely not in a view definition!

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2135 Visits: 872
My biggest concern is the use of SELECT *. This is a bad thing to put in a view definition (or in application code in general).

Else, there is not very many difference between an inter-database view and an intra-database view. The impact depends on indexes and how the view is used. The view definition may qualify 20 million rows, but that does not matter if the view is always queried with WHERE clauses which filter out a small subset by indexed columns.

Well, there is one difference to an intra-database view: you can't index it. Then again, you can't index a view with SELECT * anyway.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32841 Visits: 18559
Erland Sommarskog (7/30/2013)
Kalyan Boddupalli (7/30/2013)
1. Make sure nolocks are maintained on the tables from where the data is getting selected


Do you mean that the NOLOCK hint should be used? Have ye no shame!

The NOLOCK hint can be very important when running diagnostic queries in a live system, but it rarely is a good thing to put in application code, and absolutely not in a view definition!


I agree here. There is little to no reason to make that recommendation.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

DesmoShane
DesmoShane
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 454
Thanks for your assistance it is very helpful , you say that the view is created in tempdb and it can have a heavy impact ,so each time the view is accessed how is the view "rebuilt" does it leave the 20 million records in the tempdb and add extra records to the view or keep it in memory or is it recreated from scratch each time or maybe left in buffer if sufficient memory is available (where is the information contained in the view held and how is it processed?) , what I am trying to understand is the process that sql uses to retrieve the information/update the information /and return the information . Thanks again for your help in this
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2135 Visits: 872
DesmoShane (7/30/2013)
You say that the view is created in tempdb and it can have a heavy impact ,so each time the view is accessed how is the view "rebuilt" does it leave the 20 million records in the tempdb and add extra records to the view or keep it in memory or is it recreated from scratch each time


The person who said that was flat wrong.

A view is nothing but a macro. When SQL Server processes a query with a view, SQL Server expands the query in the view into the query, and then optimizes the resulting query. The view is never materialised as part of the process. However, depending on the resulting query, the query plan may include operators (spools, hashing, sorting etc) that use space in tempdb. But that has nothing to do with the fact that the query includes a view or that it spans multiple databases.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
DesmoShane
DesmoShane
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 454
Thanks Erland excellent response thank you , so my understanding is ... the view contains the sql and because the view can be accessed so as to cause a very large operation to occur on it it "potentially" could have an impact if the resulting query on the view is poorly used .The actual view itself does not have any requirements for resources until called

E.G. select top 1 no problem at all , select * from is going to take a while and have heavy usage of tempdb .
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2135 Visits: 872
DesmoShane (7/30/2013)
E.G. select top 1 no problem at all , select * from is going to take a while and have heavy usage of tempdb .


Maybe. All depending on the view definition, SELECT TOP 1 without ORDER BY could be an expensive operation, although it is less likely. SELECT * without a WHERE clause will take more resources, but again, not necessarily in tempdb. If the query can be implemented with only streaming operators, that will not happen.

And, again, the fact that this is a view or that it spans multiple databases, have zero importance here. It's a query, plain and simple.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search