November 15, 2021 at 7:57 pm
Hi everyone
I am in the process of moving over Access queries to SQL Server. One Access query took close to 2 hours to run. Once moved over to SQL Server and using Columnstore Index, the same query took about 6 seconds. Wow!
I have created a view to so I can reference it again. The query will be used as a data source for other queries so I want to add indexes to the the view I created. I went to View->Indexes-> Create Index. The Create Index is greyed out. This is way I used to create index for the main table. How can I create the index for the view? If I have to code it then I need some sample code I can use and modify.
Thank you
November 15, 2021 at 8:07 pm
You can only create indexes on views if they met certain criteria: https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver15
Indexed views are only valuable in limited circumstances. If you've already created indexes on the table that allowed your query to complete in 6 seconds, what leads you to believe you need to create an index on the view?
November 15, 2021 at 8:37 pm
Thanks for the reply.
I was hoping to re-use the view in another query but I have changed plans so no need to create index on view.
November 15, 2021 at 9:52 pm
With the view, if the indexes on the table are "good enough" for it, then you could still use the view without the index on it. This would allow you to reuse the view and get the performance benefits of the indexed tables.
To add to what ratbak said, if you have SQL Server Standard edition (vs Enterprise), indexed views can be created, but SQL will not use them unless you explicitly tell it to use that index. It is due to this (and the other limitations) that I don't use indexed views on my systems, but instead rely on the indexes on the tables. If it turns out the table joins are going to result in too poor of performance AND live data isn't as important, I will use a second table to store the data and have a scheduled job (nightly for example) move over new data to the table. Then the bottleneck is either disk I/O or network I/O. Introduces latency in getting data, but works pretty well for reporting purposes.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 15, 2021 at 9:59 pm
Thanks for the reply.
I was hoping to re-use the view in another query but I have changed plans so no need to create index on view.
You can reference a view from other 'queries' - preferably from a stored procedure. The view would then be incorporated into the other query and an execution plan generated based on the combined code.
Note: don't go down the path of creating views that reference other views. It is way too easy to end up referencing the same tables multiple times when that isn't necessary - and can end up causing performance issues.
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
November 15, 2021 at 10:01 pm
Thank you everyone! this is good stuff to know
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply