title: SQL / VBScript / Intelligent Algorithm to find sum combinations quickly
body: <p>Am trying to list out all the possible sequential (continuous and forward direction only) sum combinations , within the same subject.
  Listing out the row_id and the number of rows involved in the sum.</p>
  
  <pre><code>Sample :
  
  Input (Source Table :) 
  
  DLID    Subject Total   
  1   Science 70  
  2   Science 70  
  3   Science 70  
  4   Science 70  
  5   Maths   80  
  6   Maths   80  
  7   English 90  
  8   English 90  
  9   English 90  
  10  Science 75  
  
  Expected Result :           
  ID  Number of Rows  Subject Total
  1   1   Science 70
  2   1   Science 70
  3   1   Science 70
  4   1   Science 70
  5   1   Maths   80
  6   1   Maths   80
  7   1   English 90
  8   1   English 90
  9   1   English 90
  10  1   Science 75
  1   2   Science 140
  2   2   Science 140
  3   2   Science 140
  5   2   Maths   160
  7   2   English 180
  8   2   English 180
  1   3   Science 210
  2   3   Science 210
  7   3   English 270
  1   4   Science 280
  </code></pre>
  
  <p>VBSript Code :</p>
  
  <p>' myarray - reads the entire table from access database
  ' "i" is the total number of rows read
  ' "j" if to access each row one by one
  ' "m" is the number of subsequent rows with same subject , we are trying to check
  ' "n" is a counter to start from each row and check upto m - 1 rows whether same sub
  ' "k" is used to store the results into "resultarray"</p>
  
  <pre><code>myarray(0,j) = holds the row_id
  myarray(1,j) = holds the subject
  myarray(2,j) = holds the score
  myarray(3 4 5 6 are other details
  i is the total number of rows - around 80,000
  There can be conitnuous records from the same subject as many as 700 - 800
  m = is the number of rows matching / number of rows leading to the sum
  
  
  
  For m = 1 to 700
  For j = 0 to i-m
  matchcount = 1
  
  For n = 1 to m-1 
  if  myarray(1,j) = myarray (1,j+n) Then 
  matchcount = matchcount + 1
  Else
  Exit For
  End If
  Next
  
  If matchcount = m Then
  resultarray(2,k) = 0
  For o = 0 to m - 1
  resultarray(2,k) = CDbl(resultarray(2,k)) + CDbl (myarray (2,j+o))
  resultarray(1,k) = m
  resultarray(0,k) = ( myarray (0,j) )
  resultarray(3,k) = ( myarray (3,j) )
  resultarray(4,k) = ( myarray (4,j) )
  resultarray(5,k) = ( myarray (1,j) )
  resultarray(7,k) = ( myarray (5,j) )
  resultarray(8,k) = ( myarray (6,j) )
  Next
  resultarray(2,k) = round(resultarray(2,k),0)
  k = k + 1
  ReDim Preserve resultarray(8,k)
  End If
  
  Next
  Next
  </code></pre>
  
  <p>Code is working perfect , but is very slow.
  Am dealing with 80,000 row and from 5 to 900 continuous rows of same subject.
  So the number of combinations , comes in a few millions.
  Takes few hours for one set of 80,000 rows. have to do many sets daily.</p>
  
  <p>Please suggest how to speed this up.
  Better Algorithm / Code Improvements / Different Language to code
  Please assist.</p>