Menu
I've a query that performs join on many tables which is resulting in poor performance.
To improve the performance, I've created an indexed view and I see a significant improvement in the performance of the query on view with date filter. However, my concern is about the storage of the index. From what I have read, the unique clustered index is stored on SQL Server. Does it mean it stores separately the entire data resulting as part of joins within the view? If so, if I've included all columns from tables that are part of the joins in the view, would the disk space on the server consumed be approx double the disk space without indexed view? And every time I enter data into underlying tables, the data is duplicated for the indexed view?
553
Indexes can help improve the performance of a nested-loop join in several ways. The biggest benefit often comes when you have a clustered index on the joining column in one of the tables. The presence of a clustered index on a join column frequently determines which table SQL Server chooses as the inner table.
Simple sequential full table scans and then a join on hashes for instance will usually be much faster. Let's look at another example: select e. last_name, d.
SQL index is considered as one of the most important factors in the SQL Server performance tuning field. It helps in speeding up the queries by providing swift access to the requested data, called index seek operation, instead of scanning the whole table to retrieve a few records.
That is correct. An indexed view is basically an additional table that contains a copy of all the data in a sorted way. That's what makes it so fast, but as everything in SQL Server land, it comes at a price - in this case the additional storage required and the additional time required to keep all the copies of the data in sync.
The same is true for a normal index on a table. It is also a copy of the index keys (plus some information of where to find the original row), that needs additional storage and additional time during updates to be maintained.
To figure out if adding an index on a table or view makes sense, requires you to look at the entire system and see if the performance improvement for the one query is worth the performance degradation of other queries.
In your case you should also (first) check if additional indexes on the underlying tables might help your queries.
106
Yes, that is correct. An indexed view persists all data in the view separately from the source tables. Depending on the columns and joins, the data is duplicated, and can actually be many times larger than the source tables.
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
