puffdaddy411
asked on
Decrease time to insert data from one table to another.
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.
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
ASKER
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........................ .......... .......... .......... .......... .......... .......... ..........
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........................
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.
DS.Tables("TimeTable") = DS.Tables("SQL_Result").Co