title: Are Views or Functions faster in SQL?
body: <p>I have a table with customer receipts. I'm trying to generate a report based on the user's name, address, and purchases total based by department. The desired output should look like</p>
  
  <hr>
  
  <pre><code>|Customer       |Address                | Clothing  | Electronics | Hardware | Household |
  |Homer Simpson  | 724 Evergreen Terr    | $42       | $20         | $500     | $24       |  
  |Walter White   | 308 Negra Arroyo Lane | $120      | $80         | $52      | $2400     |  
  </code></pre>
  
  <hr>
  
  <p>The receipts table is part of a temporal model. So, the code looks like:</p>
  
  <pre><code>Select c.customername,a.address,r.receiptno,ir.department,ir.total
  from customer c
  inner join customer_address_lnk cal on cal.customerid = c.id
  inner join address a on cal.addressid = a.id
  inner join customer_receipts_lnk crl on crl.customerid = c.id
  inner join receipts r on crl.receiptid = r.id
  inner join receipts_receiptitem_lnk rrl on rrl.receiptid = r.id
  inner join receiptitem ri on ri.id = rrl.receiptitemid
  </code></pre>
  
  <p>The lnk tables are linking tables.</p>
  
  <p>The receiptitem table has the following columns: ID, Department, Amount, CreatedDate, UpdatedDate</p>
  
  <p>The idea is that if the receipt is updated,  the updated amount can be adjusted for returns, price adjustments, and so forth. </p>
  
  <p>The goal is to get the query under 5 sec. Since we have over 125 million rows in the receiptitems table alone, it takes SQL 20+ minutes to calculate the report. </p>
  
  <p>I've tried CTE's on views without success. I've tried different JOIN orders. I've used LEFT Joins. Even Pivot didn't slow it down. I still can't get it under 20 minutes.</p>
  
  <p>Before I start down the path of creating a Function to get it under the 5 second goal, I'm open to any suggestions.  I have limited ability to alter indices at this time.</p>
  
  <p>Any thoughts?</p>