Increase performance of views that gets created from different database

Transact-SQL
1

I have 10 to 15 tables in master database and I have 5 databases that uses data from master database.

Index has been created on those tables. Now I want to create views on tables of master database and also the performance should be faster. Please suggest me the process

Thanks in advance

2

impossible without knowing specifics. Table schemas, sizes, master database tables, the queries you will put in the views, storage setup on the machine/vm, Whether you are CPU, I/O or NETWORK bound.

let's start with a question, "what are your performance objectives?" e.g. some query (post it!) runs in 20 minutes but I have to get it down to 10 minutes if possibile.

3

Yes....I have created indexes on those tables. If those tables are in same database , the query runs fast.

If the same tables are in master database and retrieve data from another database, then what will be performance between [masterdb].dbo.Table1 or View created in 2nd database and retrieving data using view?

4

Assuming the query is identical I don't see why the query would have any difference in speed i.e. basically its just the difference between

SELECT Col1, Col2, ... FROM dbo.MyTable WHERE ColX=@ParamY ORDER BY ColZ
and
SELECT Col1, Col2, ... FROM MyOtherDB.dbo.MyTable WHERE ColX=@ParamY ORDER BY ColZ

If your VIEW contains JOINS that are not relevant to the Columns that you select from the VIEW, and if those JOINs were not present in the original query, then THAT sort of thing will make a difference to performance.