title: Using T-SQL AVG or taking Average after results returned using LINQ
body: <p>I have a stored procedure that uses a view to pull 6 averages. The SQL database is SQL Server 2000. When I run it in the Query analyzer, it takes roughly 9 seconds. What can I do to get better performance? Should I return the rows using LINQ and determine an average that way? Will it be faster?</p>
  
  <p>Here's an example of my current sproc:</p>
  
  <pre><code>create procedure [TestAvg]
  (
      @CustomerNumber int
  )
  as
  
  select
  (select AVG(OrderTime) from OrderDetails where ProductID = 12 and DateDiff(day, DateFulfilled, GetDate()) &lt;= 7 and CustomerNumber = @CustomerNumber) as P12D7,
  (select AVG(OrderTime) from OrderDetails where ProductID = 12 and DateDiff(day, DateFulfilled, GetDate()) &lt;= 30 and CustomerNumber = @CustomerNumber) as P12D30,
  (select AVG(OrderTime) from OrderDetails where ProductID = 12 and DateDiff(day, DateFulfilled, GetDate()) &lt;= 90 and CustomerNumber = @CustomerNumber) as P12D90,
  (select AVG(OrderTime) from OrderDetails where ProductID = 16 and DateDiff(day, DateFulfilled, GetDate()) &lt;= 7 and CustomerNumber = @CustomerNumber) as P16D7,
  (select AVG(OrderTime) from OrderDetails where ProductID = 16 and DateDiff(day, DateFulfilled, GetDate()) &lt;= 30 and CustomerNumber = @CustomerNumber) as P16D30,
  (select AVG(OrderTime) from OrderDetails where ProductID = 16 and DateDiff(day, DateFulfilled, GetDate()) &lt;= 90 and CustomerNumber = @CustomerNumber) as P16D90
  </code></pre>
  
  <p>Also, let me clarify the view mentioned above. Since this is SQL Server 2000, I cannot use an indexed view because it does use a subquery. I suppose this can be rewritten to use joins. However, the last time we took a query and rewrote it to use joins, data was missing (because the subquery can return a null value which would omit the entire row).</p>