Code too slow (need some input)

[Yafuhenk]

Hi,

I try to write a module which shows me the turnover of the customers in the past years. This code does it but is extremly slow (several minutes on a 1.2 GHZ Cortex A8 processor / 512 MB 7" Tablet).
Number of customers is about 800.
Can I have some input on how I can rewrite this code in a much more intelligent way?

Thanks

Henk

Try
   'Set title
   lblOverview.Text = "ABC analyses"
   Dim FY, FY1, FY2 As Int
   'SelectFY is in this case the current Financial year
   FY = SelectedFY
   FY1 = FY - 1
   FY2 = FY - 2
   'This SQL finds all the customers who bought something in the financial years FY, FY1 and FY2
   SQLString = "SELECT CustomerNumber, CustomerName, SUM(Turnover) FROM TurnoverData WHERE FiscalYear = '" & FY & "' OR FiscalYear = '" & FY1 & "' OR FiscalYear = '" & FY2 & "') GROUP BY CustomerNumber ORDER BY SUM(Turnover) DESC"
   Cursor1 = SQL1.ExecQuery(SQLString)
   NumberOfColumns = Cursor1.ColumnCount + 2 '2 Columns added because of the result of FY1 and FY2 which whould be in the scrollview as well.
   ColumnWidth = SV.Width / NumberOfColumns
   Dim ColumnName(NumberOfColumns) As String
   ColumnName(0) = "Customer"
   ColumnName(1) = "Name"
   ColumnName(2) = "Turnover " & FY & " (EUR)"
   ColumnName(3) = "Turnover " & FY1 & " (EUR)"
   ColumnName(4) = "Turnover " & FY2 & " (EUR)"
   SelectedRow = -1
   'set aligment
   SetAlignment
   'add header
   SetHeader(ColumnName)
   'add rows
   For i = 0 To Cursor1.RowCount - 1
      Dim Col(NumberOfColumns) As String
      Cursor1.Position = i
      For j = 0 To NumberOfColumns - 4 'Only the two first fields are written in the array: CustomerNumber and CustomerName
         Col(j) = Cursor1.GetString2(j) 'Col(0) = CustomerNumber 
      Next
      'All customers selected with their total turnover in financial year FY
      SQLString = "SELECT SUM(Turnover) FROM TurnoverData WHERE CustomerNumber = '" & Col(0) & "' AND SUBSTR(Col10,8) = '" & FY & "'"
      Cursor2 = SQL1.ExecQuery(SQLString1)
      If Cursor2.RowCount = 0 Then 'If the customer didn't buy anything this year set Turnover to zero
         Col(j) = "0"
      Else
         Cursor2.Position = 0
         Col(j) = Cursor2.GetString2(0)
      End If
      'All customers selected with their total turnover in financial year FY - 1      
      SQLString = "SELECT SUM(Turnover) FROM TurnoverData WHERE CustomerNumber = '" & Col(0) & "' AND SUBSTR(Col10,8) = '" & FY1 & "'"
      Cursor2 = SQL1.ExecQuery(SQLString1)
      If Cursor2.RowCount = 0 Then
         Col(j + 1) = "0"
      Else
         Cursor2.Position = 0
         Col(j + 1) = Cursor2.GetString2(0)
      End If
      'All customers selected with their total turnover in financial year FY - 2      
      SQLString = "SELECT SUM(Turnover) FROM TurnoverData WHERE CustomerNumber = '" & Col(0) & "' AND SUBSTR(Col10,8) = '" & FY2 & "'"
      Cursor2 = SQL1.ExecQuery(SQLString1)
      If Cursor2.RowCount = 0 Then
         Col(j + 2) = "0"
      Else
         Cursor2.Position = 0
         Col(j + 2) = Cursor2.GetString2(0)
      End If
      AddRow(Col) 'Adds the row to the scrollview
   Next
   Cursor2.Close
   Cursor1.Close
Catch
   Log(LastException.Message)
End Try

 

[Erel]

Do you have an index on CustomerNumber?

 

[Yafuhenk]

Yes Erel this:

SQL1.ExecNonQuery("CREATE INDEX CustomerIndex ON TurnoverData (CustomerNumber)")

solved my problems indeed

Thanks

 

[Erel]

Great. So how much time does it take to run it now?

 

[Yafuhenk]

Between 10 and 15 seconds!

Regards

Henk