title: Scaling issue with a view in sql server 2005
body: <p>I have an issue with scaling when using a view in MS SQL Server 2005. My defined view returns the data of interest on a small test dataset; however, when I try to return the view on a large dataset (>10000 rows), my query just runs and runs without returning.  Is this an error in the efficiency of the view definition, or maybe do I need to include indexing? Are there other ways that I could make the view return data more quickly/efficiently so that it can be practically used?</p>
  
  <p>My current view definition is as follows: -</p>
  
  <pre><code>CREATE VIEW reportedProjects AS
    SELECT vc.projectID, vc.sampleID, vc.runID, vc.ngsSubpanel,
    va.[HGVS_c], va.[HGVS_p], va.geneID, va.transcriptID,
    sc.score, sc.scorer, sc.checker
  
    FROM dbo.varCall vc, dbo.varAnnotation va, dbo.varScore sc
  
  
    WHERE vc.varCallID = va.varCallID
    AND vc.variantID = va.variantID
    AND va.variantID = sc.variantID
    AND va.transcriptID = sc.transcriptID;
  
  GO
  </code></pre>
  
  <p>-- <strong>Update</strong> --</p>
  
  <p>Apologies for any red herrings this may have thrown up for readers. A change of base table columns (not listed in the above problem description) to join on within the view has solved my issue. All suggestions offered were much appreciated though.</p>