Decrease time to insert data from one table to another.

puffdaddy411
puffdaddy411 used Ask the Experts™
on
I have an application which tracks numbers throughout a given timespan.  In this example, I have two tables.  One table is a result of a SQL statement.  It includes 2 columns: a number and a time.  The other table is a contains 2 columns.  One of which is a time flow ranging from 8am to 5pm by 1 minute increments.  The other column in this table is used to insert the "Number" value of the first table (adding values when multiple numbers are for the same minute).  The second table is used to create the background table of a graph.

Right now, I am simply using For Each statements to populate the second table from the first table.  I add some logic in to reduce the amount of loops once the times match and the value is inserted/added.  

Is there a better way to insert this data?  Is there a way to create a datarelation between the Time value column of the SQL result table and the Time value column of my 2nd table which runs 8am to 5pm (by minute)....then do a mass insert???  I'm looking for a way to reduce the time this takes.  Of course, my example is dumbed down for the interest of simplicity.  The real "2nd table" has a longer span than 8am to 5pm and repeats this process 4-6 times.  THEN...the entire process is repeated once per 60-90 seconds to update the graph.  
'Something like this.
 
For Each Row as System.Data.DataRow in DS.Tables("SQL_Result").Rows
     For Each Row1 as System.Data.DataRow in DS.Tables("TimeTable").Rows
          If Row.Item("Time") = Row1.Item("Time") Then
               Row1.Item("Number") += Row.Item("Number")
               Exit For
          End If
     Next
Next

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
can you try this:

DS.Tables("TimeTable") = DS.Tables("SQL_Result").Copy()

Author

Commented:
That will not work.  Creating a copy of my SQL_Result table will NOT accomplish my task.  
EVERY minute from 8am to 5pm is in the "TimeTable"
 
 
SQL_Result:
 
Time / Value
08:05 / 1000
08:05 / 500
08:08 / 300
 
 
TimeTable after work is complete.
 
Time / Value
08:00 / 0
08:01 / 0
08:02 / 0
08:03 / 0
08:04 / 0
08:05 / 1500
08:06 / 0
08:07 / 0
08:08 / 300
 
And so on..............................................................................................
Most Valuable Expert 2012
Top Expert 2014
Commented:
There is an easier way

For i as Integer = 0 to dTable1.Rows.Count - 1
      dTable2.DefaultView.RowFilter = "TimeValue = '" & dTable1.Rows(i).Item("TimeValue")
      If dTable2.DefaultView.Count = 1 Then
         dTable2.DefaultView.Item(0).Item("Value") = dTable1.Rows(i).Item("Value")
      End If
      dTable2.DefaultView.RowFilter = ""
Next


There is no shortcut of batch insert though.

Author

Commented:
Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial