September 10, 2020 at 7:42 pm
I've inherited a stored procedure that is using a view several times to load temp tables. I'm thinking I can increase performance by loading a temp table with the data from the view and then use the temp table instead of the view. Im just not sure if once a view is called if it stays in memory or the view queries the data each time it is called.
September 10, 2020 at 7:57 pm
Since a view is just a named query - it will be optimized into the outer query referencing the view. If there are tables/views/columns that are not utilized in the outer query, SQL Server will optimize those out of the final plan.
The only way to know for sure is to test. It could be faster - it could be slower - or there may not be any difference at all.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 11, 2020 at 12:14 pm
A view does not stay in memory at all. Unless, you're simply calling SELECT * from the view. A view is just a query. The query that calls that view is used by the optimizer to determine what parts of the view it needs to satisfy the query. The pages from those parts of the view that were used by the optimizer are what get loaded into memory. They'll stay in memory as long as anything else on your system does. It's completely dependent on the load on the system, the queries being run, and the amount of memory that you have. There is no hard and fast value. Let's say a system has 64gb of memory. And, you only ever run one query on it. That query uses about 10gb of memory (we're making stuff up here). Since that's the only query ever run, the pages needed to satisfy the query will get loaded into memory and stay there until the machine gets shut down. Conversely, let's say you have 4gb of memory (and SQL Server can just barely launch). And you're running hundreds of different queries every minute. Chances are, nothing remains in memory longer than is necessary to complete query processing before the next query needs memory.
Hope that helps.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply