locked
Indexing Views and UNION-Design Question RRS feed

  • Question

  • Hello,

    We have data from say company1 in table1. Data from company2 in table2. etc. So we have this view that Union's Table1, Table2 etc. So I want to improve our view foundation but I can't index when a UNION is used. So the next logical step is to consolidate Table1, Table2, etc. into Table_All. From there your view could point to the Table_All and be indexed. Here is where I am looking for critical review.

    1. We are using enterprise edition so I could perhaps do a partitioned view.

    2. Leave well enough alone and just use UNION in the view and accept the lack of indexing.

    3. Change the schema as the best option for growing size & load would be to have an aggregate table with indexed view.

    It seems to me as much as option 3 will shake things up that it is the best way to go. I have this feeling that as we add more clients the view will grow and grow and the performance of unioning all those tables will plummet. Option 1 I am unsure of as my only knowledge is Google knowledge.

     

    Any input greatly appreciated.

     

    Friday, October 1, 2010 6:42 PM

Answers

  • Well, in your particular example (non-indexed) view with the union is a bad choice when you need to query specific row. You said that row can be stored in either tables - so what is the point to have a union and search the table2 if you already found the row in table1?

    Indexed view is not the best option either - you will basically double the storage size and have performance hit because of the background view maintenance.

    Why don't you want to combine everything into the one table? The schema with the multiple databases (one db per customer) will introduce a lot of headache at the future. Think about maintenance, metadata changes, disaster recovery. Only cases when it could be beneficial if your customers require full backup of their databases and/or you need to allow them to access the databases directly and worry about the security.


    Thank you!

    My blog: http://aboutsqlserver.com

    • Marked as answer by zzpluralza Monday, October 4, 2010 4:15 PM
    Monday, October 4, 2010 3:57 PM

All replies

  • Let we start with the different question. What problems are you trying to solve with the indexed view?

    Thank you!

    My blog: http://aboutsqlserver.com

    Friday, October 1, 2010 7:25 PM
  • OK so table1 has Customer Data. Table 2 has the same schema but different records from table 1. The website wants to query for customer xyz. To do so we would search table1 then table 2. Especially since one purchase might have been recorded in table1 while another purchase recorded in table2. So we would need to query both tables to get all orders.

    Instead do a view and get all records with one query. The current view uses Unions to pull table1 & 2 together.

    So now we anticipate having more customer tables but they will reside in a different database. So we will have Dbase A > tbl_CustData1, // Dbase B > tbl_CustData1 // Dbase C > tbl_CustData1 etc.  So again we would have 1 view to pull them all together but I HAVE to use Union cross database.

    My concern is with growth over time we are going to notice performance hits with a union view especially without indexes?

    But if I get all tables to 1 table somehow then I can have an indexed view.

     

    Does this make sense?

    Monday, October 4, 2010 3:43 PM
  • Well, in your particular example (non-indexed) view with the union is a bad choice when you need to query specific row. You said that row can be stored in either tables - so what is the point to have a union and search the table2 if you already found the row in table1?

    Indexed view is not the best option either - you will basically double the storage size and have performance hit because of the background view maintenance.

    Why don't you want to combine everything into the one table? The schema with the multiple databases (one db per customer) will introduce a lot of headache at the future. Think about maintenance, metadata changes, disaster recovery. Only cases when it could be beneficial if your customers require full backup of their databases and/or you need to allow them to access the databases directly and worry about the security.


    Thank you!

    My blog: http://aboutsqlserver.com

    • Marked as answer by zzpluralza Monday, October 4, 2010 4:15 PM
    Monday, October 4, 2010 3:57 PM