Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using a View from one database to a second database that returns a large result set Expand / Collapse
Author
Message
Posted Monday, July 29, 2013 8:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:50 PM
Points: 47, Visits: 400
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
Post #1478804
Posted Monday, July 29, 2013 10:53 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 20,466, Visits: 14,098

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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1478821
Posted Tuesday, July 30, 2013 12:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 1:16 AM
Points: 2, Visits: 25
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)
Post #1478833
Posted Tuesday, July 30, 2013 2:05 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:43 AM
Points: 756, Visits: 631
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
Post #1478868
Posted Tuesday, July 30, 2013 2:09 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:43 AM
Points: 756, Visits: 631
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
Post #1478871
Posted Tuesday, July 30, 2013 2:42 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 20,466, Visits: 14,098
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1479163
Posted Tuesday, July 30, 2013 3:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:50 PM
Points: 47, Visits: 400
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
Post #1479177
Posted Tuesday, July 30, 2013 3:10 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:43 AM
Points: 756, Visits: 631
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
Post #1479180
Posted Tuesday, July 30, 2013 3:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:50 PM
Points: 47, Visits: 400
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 .
Post #1479184
Posted Tuesday, July 30, 2013 3:30 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:43 AM
Points: 756, Visits: 631
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
Post #1479189
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse