title: How to speed up a join and sum product query in SQL Server 2008?
body: <p>I have two tables A and B.  A has float variables X1, X2, X3, ... , X9, sumprod and 14,000 rows.  B has float variables X1, X2, X3, ... , X9, a text variable 'Model' with values such as 'Model 1', 'Model 2' and so on, and 50 rows.<br>
  I am trying to join and performs a sumproduct operation using the following code:</p>
  
  <pre><code>Update A set a.sumprod = a.X1*b.X1 + a.X2*b.X2 + ... + a.X9*b.X9
  from a left join b
  on b.Model = 'Model 2';
  </code></pre>
  
  <p>I have multiple such queries with different tables as A, and corresponding different join conditions on the Model variable in table B.  I have identified these queries as taking the longest time in my stored procedure and am looking for a way to make them faster.</p>
  
  <p>I have tried variants of this query like below without any material changes in runtime:  </p>
  
  <p>Variant 1:</p>
  
  <pre><code>Update A
  set a.sumprod = a.X1*b.X1 + a.X2*b.X2 + ... + a.X9*b.X9
  from a left join b
  on 1 = 1
  where b.Model = 'Model 2';
  </code></pre>
  
  <p>Variant 2:</p>
  
  <pre><code>merge A
  using (select X1, X2, ..., X9 from B where Model = 'Model 2') C
  on 1 = 1
  when matched then update
  set sumprod = a.X1*c.X1 + a.X2*c.X2 + ... + a.X9*c.X9;
  </code></pre>
  
  <p>Edit for greater clarity:</p>
  
  <p>There are multiple table A's: A1, A2, A3, ... Each table A# contains explanatory variables (X1, X2 etc) for a model (corresponding to the model number in table B).</p>
  
  <p>So table A1 may be:</p>
  
  <p>X1 | X2 | X3 | X4 | Sumprod</p>
  
  <p>6  | 7  | 3  | 5  |</p>
  
  <p>5  | 3  | 4  | 4  |</p>
  
  <p>...</p>
  
  <p>Table A2 would have a different number of explanatory variables, and the explanatory variables themselves would be different. Also, the number of rows would be different from A1.</p>
  
  <p>Table B has model coefficients for each model like so:</p>
  
  <p>Model   | X1 | X2 | X3 | X4 | X5 | X6 | X7 | X8 | X9</p>
  
  <p>Model 1 | 3  | 2  | 5  | 9  | 0  | 0  | 0  | 0  | 0</p>
  
  <p>Model 2 | 4  | 7  | 8  | 3  | 5  | 8  | 0  | 0  | 0</p>
  
  <p>...</p>
  
  <p>Model 1 has four explanatory variables, so the Model 1 row in table B has zero coefficients for columns X5 onwards.</p>
  
  <p>What I want to do in the sumprod column of each table A is take the sum product of the explanatory variables and the coefficients from the correct row in table B. There is no common row identifier between the table A's and the coefficient table B. I am taking the sum product of EACH row in A1 with a SINGLE row in B.</p>
  
  <p>After the join, I want the sumprod column of table A1 to be populated as below:</p>
  
  <p>X1 | X2 | X3 | X4 | Sumprod</p>
  
  <p>1  | 7  | 3  | 5  | 6*3 + 7*2 + 3*5 + 5*9 = 92</p>
  
  <p>5  | 3  | 4  | 4  | 5*3 + 3*2 + 4*5 + 4*9 = 77</p>
  
  <p>...</p>
  
  <p>Values for the explanatory variables are fixed but values for the model coefficients are user inputs and are expected to change fairly often.</p>
  
  <p>From the initial comments, it seems that this is not a good database structure for what I want to do. Any suggestions for how I can make this faster?</p>