· Sep 14, 2021

Do Views Speed up query response time?

So I am working with an inherited SQL query that queries 8 different tables. 5 of which have over a million records. 
I have 3 different servers. 

Server 1, Server 2 and Server 3. They all have the same data/tables/structure across all servers. 
Server 3 has an Iris database engine and the other 2 are Cache 2015.1.4.

I have a problem with views and performance across the servers being inconsistent. 

Thankfully Server 1 is the current live server that performs "fast enough".
Server 2 is being synced from Server 1 and acts as a report server.

And Server 3 is supposedly a new Live server. I am trying to diagnose why Server 2 and 3 aren't as performant as Server 1.

My question/theory is, that the creation of a View does some cache magic behind the scenes to help with query caching? 
I am unable to create the view on Server 2 as the Portal times out.... but if the query was able to be created and ran at least once, would further executions of it be faster and is my theory correct? 
Does the Cache magic consist of: 'If the underlying data was changed and new records added the view only has to update relating to changes in the data and not have to reprocess everything again'?

To make things more tricky, I don't have permission to create Views on Server 3... If the creation of a view doesn't assist in anyway to query execution then it doesn't matter and it all comes down to indexes I guess but I don't have permission to create those either!

Any information would be great.

Product version: Caché 2015.1
$ZV: Build 803_6
Discussion (1)0
Log in or sign up to continue

The VIEW as such could be understood as a complex SELECT which definition is stored in the system 
At the first time processing, all code for access is generated and cached in the system
and marked with a signature. this may take a few seconds.
In parallel, a significant amount of structural data are also cached

The next time you call it, the signature is calculated and the already cached code is executed. 
You save time for code generation and compilation.
If this happens shortly after the first call you take additional advantage of the cached structural data.
How much and how long structural data are caches depends on the available buffer pool