title: Indexing Views in SQL Server 2008
body: <p>I have a table which has several hundred million rows of data. There is a field called <code>EventId</code> which is an integer field. </p>
  
  <p>I have a number of different views which will return only data with a specific EventId</p>
  
  <p>If I run a query </p>
  
  <pre><code>SELECT TOP 1000 * FROM vw_MyView 
  </code></pre>
  
  <p>It takes 5 mins to return the rows. What do I need to add indexes to? There are currently indexes on the master table for the primary key (clustered on <code>LogId</code>) and a non-clustered on <code>EventId</code> as that is being used in the where clause of each view.</p>
  
  <p>I know views can be indexed - what field(s) should I be indexing on in the view? Should I just run the DB Engine Tuning Wizard and see what it says?</p>
  
  <p><strong>UPDATE following feedback</strong></p>
  
  <p>My master table with all the data inside is along the lines of the following schema</p>
  
  <pre><code>LogId (int) PK
  EventId (int)
  Param1 varchar(255)
  Param2 varchar(255)
  ..
  ..
  ..
  Param24 varchar(255)
  </code></pre>
  
  <p>Each event type has different parameters hence the generic field names in the master table. </p>
  
  <p>I have a view for each type of event, where the ParamX fields in the master table are given a proper field name through the view. </p>
  
  <p>So the view for one event would be something like </p>
  
  <pre><code>SELECT LogId, Param1 AS Name, Param2 AS Address1, Param3 AS Address2
  WHERE EventId = 10
  </code></pre>
  
  <p>I tried the query </p>
  
  <pre><code>SELECT TOP 1000 LogId from vw_MyView 
  </code></pre>
  
  <p>and that worked fast. It's the other fields which are slowing it down, which I assume is down to poor indexing?</p>
  
  <p><strong>UPDATE 2 - More info</strong></p>
  
  <p>Previously the data for each event was stored in a table for each event. This meant that adding new events would require a new table for each one. </p>
  
  <p>I am bulk importing data into a temp table then moving that over into my master table. The bulk import makes it fast, but I am concerned that having such a large master table could mean that querying it becomes so slow that it is unusable. </p>
  
  <p>The millions of rows of data is over 10 years or so and so I could probably move the first 8 years of data over to another database for archive purposes so that just the latest 2 years are kept. </p>
  
  <p>So the question is do I continue with the master table approach which would require no maintenance but potentially a lot of indexing, or go back to the original approach of having a table per event?</p>
  
  <p>Thanks for the feedback guys, really appreciate it</p>