title: TSQL Query of View takes a long time
body: <p>I have a series of views I created for a bunch of different reports. They build off each other to a final view that returns less than 5k records. When I open this view, it takes less than a second to run. If I add a <code>WHERE collumn IS NOT NULL</code> it takes over 2 minutes. While this is fine for reports I don't need to run very often, there are others that I have to run weekly, daily, and sometimes more often. </p>
  
  <p>My solution was to create a stored procedure that makes a table from the view's data, and query that. I set the stored procedure to run before the scheduled reports automatically fire off. This works OK but I have to run the stored procedure manually every time I want to get an updated report. </p>
  
  <p>Is there a better way?</p>
  
  <p>View [MW_Submitted_ROY] to find Submitted Weeks financials. This view combines an active table with historic data in a static table. I massaged the data in the static table to be in the same format as the active one. </p>
  
  <pre><code>SELECT     oe_po_no, cus_no, user_def_fld_1, user_def_fld_2, tot_sls_amt
  FROM         MW_Submitted_Weeks_OldMacola
  WHERE     MW_Submitted_Weeks_OldMacola.oe_po_no LIKE 'adv%'
  UNION
  SELECT     oe_po_no, cus_no, user_def_fld_1, user_def_fld_2, tot_sls_amt
  FROM         oehdrhst_sql
  WHERE     oehdrhst_sql.oe_po_no LIKE 'adv%'
  </code></pre>
  
  <p>View [MW_Company_Weeks] to generate all possible weeks per company that they would submit financials for the current and previous year. </p>
  
  <pre><code>SELECT     MW.MW_Weeks.Year, MW.MW_Weeks.Week, MW.MW_CompanyCodes.cmp_code
  FROM         MW.MW_Weeks CROSS JOIN
                    MW.MW_CompanyCodes
   WHERE     (MW.MW_Weeks.WEDate &lt;= GETDATE()) AND (MW.MW_Weeks.Year &gt; YEAR(GETDATE()) - 2)
  </code></pre>
  
  <p>View [MW_Submitted_Weeks_By_RBC_ROY] combines more info about the company I need to generate reports</p>
  
  <pre><code> SELECT     dbo.cicmpy.textfield2 AS [Group], dbo.cicmpy.cmp_code, dbo.cicmpy.cmp_name,          dbo.cicmpy.cmp_fadd3 AS Owner, dbo.cicmpy.SalesPersonNumber, 
                    MW.MW_Submitted_ROY.user_def_fld_1 AS Year, MW.MW_Submitted_ROY.user_def_fld_2 AS Week, MW.MW_Submitted_ROY.oe_po_no AS Description, 
                    MW.MW_Submitted_ROY.tot_sls_amt
   FROM         dbo.cicmpy INNER JOIN
                    MW.MW_Submitted_ROY ON dbo.cicmpy.cmp_code = MW.MW_Submitted_ROY.cus_no
   WHERE     (dbo.cicmpy.cmp_code LIKE 'C%') AND (dbo.cicmpy.cmp_status = 'a') AND (dbo.cicmpy.cmp_type = 'C')
  </code></pre>
  
  <p>Which brings us to the final View [MW_Missing_Weeks] which shows me which weeks are missing, and the value of the previous year submitted financials. This is where adding the 'is not null' makes it take forever.</p>
  
  <pre><code> SELECT     MW.MW_Company_Weeks.Year, MW.MW_Company_Weeks.Week, MW.MW_Company_Weeks.cmp_code, bPrev.tot_sls_amt, bPrev.[Group], bPrev.cmp_name, bPrev.Owner, 
                    bPrev.SalesPersonNumber, bPrev.Description
   FROM         MW.MW_Company_Weeks LEFT OUTER JOIN
                    MW.MW_Submitted_Weeks_By_RBC_ROY ON MW.MW_Company_Weeks.cmp_code = MW.MW_Submitted_Weeks_By_RBC_ROY.cmp_code AND 
                    MW.MW_Company_Weeks.Year = MW.MW_Submitted_Weeks_By_RBC_ROY.Year AND MW.MW_Company_Weeks.Week = MW.MW_Submitted_Weeks_By_RBC_ROY.Week LEFT OUTER JOIN
                    MW.MW_Submitted_Weeks_By_RBC_ROY AS bPrev ON MW.MW_Company_Weeks.cmp_code = bPrev.cmp_code AND MW.MW_Company_Weeks.Year = bPrev.Year + 1 AND 
                    MW.MW_Company_Weeks.Week = bPrev.Week
   WHERE     (MW.MW_Submitted_Weeks_By_RBC_ROY.cmp_code IS NULL) AND bPrev.tot_sls_amt IS NOT NULL
  </code></pre>