Currently I am building a search(filter) dash board application which is querying the underlying tables (OLTP) in MS SQL server 2008.

There are around 10 tables I need to query for getting the consolidated search results based on the filter condition. Most of the tables can be joined together, but there are some un-related tables as well.

There is performance trade off if I query each of these table and join them directly since number of concurrent users using this application will be more than 1000. Also some of the tables contain more than million records. That's why I didn't tried to use a stored procedure.

Based on this I though of using an Indexed view which contain all the fields required by search filter. And this indexed view getting refreshed using some trigger. Is there any other approach I can use in this scenario?