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
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 454
Thank you for your assistance you have been very helpful
DesmoShane
DesmoShane
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 454
Thank you for your help it is much appreciated
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68765 Visits: 18570
Erland Sommarskog (7/30/2013)
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.


Nobody ever said the view is materialized nor that the full 20 million records are stored in tempdb. Selecting 20 million records and a select * however can have an impact on tempdb due to spooling, sorting, hash tables and spillover - far more than the impact on the user databases.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

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