Indexed Views and Reporting

Question

Indexed Views and Reporting

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125561 More actions · Answer 1

I'd reccomend against an indexed view for reasons mentioned earlier by others.

However, if this view is giving the reporting users everything they need, and they can tolerate some degree of latency in regard to how frequently the data is refreshed, then consider simply creating a job that periodically (say every hour for example) selects from the view into a persisted table, and then create a clustered columnstore index on that table. Once done, MyReportingTable is the new data source for reporting purposes.

You may find that querying the entire resultset of the view in one shot is actually faster than a selective query on the view using a WHERE clause, and once the data contained in a flat clustered columnstore, it can be queried very quickly without hitting the online transactional tables.

-- schedule this to run every hour, 15 minutes, etc.

drop table MyReportingTable;

select *

into MyReportingTable

from MyReportingView;

create clustered columnstore index ccsix_MyReportingTable

on MyReportingTable;

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Gazareth One Orange Chip Points: 27737 More actions · Answer 2

Nice idea, but Columnstore indexes are an Enterprise-only feature. A periodically refreshed persisted table with appropriate B-tree indexes may still be a valid approach though.

Mike Scalise SSCrazy Eights Points: 8145 More actions · Answer 3

Yes, these are great suggestions. Thank you, all!

Mike Scalise, PMP
https://www.michaelscalise.com