0

I have a view on two tables (stored using SQL 2008) with millions of records.

CREATE VIEW VwSalesAge
AS
    SELECT
        Transactions.ID
        ,Transactions.Amount
        ,Customer.ID
                ,Customer.Name
        ,Customer.Age
    FROM Transactions 
        INNER JOIN Customer 
            ON Transactions.CustomerID=Customer.ID

Now I want to use a physical table to store this information to avoid scannig of large tables for even smaller queries like 

SELECT * 
FROM VsSalesAge 
WHERE Customer.ID = 123

So which one is the best approach in terms of performance.

  1. USE Change Data Capture on both tables and identify changes and apply them on the new table 'TbSalesAge'
  2. Use a materialized view instead of a physical table
  3. Some other method (explain please...)

PS: I don't need real-time replica

Faiz
  • 5,331
  • 10
  • 45
  • 57
  • 1
    What's the problem with is in current normalised form? – TFD Feb 15 '10 at 11:54
  • 2
    Given the view definition presented, the `SELECT` should return instantly. If it doesn't, you definitly need to have a look at your indexes. A covering index on `CustomerID, ID, Amount` and `ID, Name, Age` should take you a long way. – Lieven Keersmaekers Feb 15 '10 at 12:15
  • + 1 for Lieven's comment, index on Id should do the job – 2lazydba Dec 30 '12 at 22:14

1 Answers1

0

IMHO, I think the best approach would be to use an Indexed View. You will need to create the view with the SCHEMABINDING option and there are some restrictions on computed columns, grouping functions, etc., but I think this gets you the single consolidated object with the performance improvements with indexing you're looking for.

rusty_scupper
  • 1