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.