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 Tuesday, July 30, 2013 3:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:33 PM
Points: 47, Visits: 414
Thank you for your assistance you have been very helpful
Post #1479196
Posted Tuesday, July 30, 2013 3:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:33 PM
Points: 47, Visits: 414
Thank you for your help it is much appreciated
Post #1479201
Posted Tuesday, July 30, 2013 4:02 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 9:45 AM
Points: 21,217, Visits: 14,921
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


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 #1479214
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse