What is the fastest way to get data from vb.net?

Question

I have a loop which run about 300,000 times at every iteration I stored:

time_elps = stwt.Elapsed.TotalMilliseconds
read.Add(time_elps, OFV_best)

Finally I just want to take the values of read (a dictionary of course) to draw a graph on excel.

I tried to export this data to excel:

oxl = CreateObject("Excel.application")
oxl.Visible = True
owb = oxl.Workbooks.Add
osheet = owb.ActiveSheet

For i = 0 To 100
    osheet.Cells(i + 1, 1).Value = read.Item(read.Keys.ElementAt(i))
    osheet.Cells(i + 1, 2).value = read.Keys.ElementAt(i)
Next

and to a text file:

objStreamWriter = New StreamWriter("C:\Users\Dr. Mohamed ElWakil\Desktop\data.txt")

For i = 0 To read.Count - 1
    objStreamWriter.WriteLine(CStr(read.Item(read.Keys.ElementAt(i)) & "," & (read.Keys.ElementAt(i))))
Next

objStreamWriter.Close()

In two cases it takes too too much time, it's longer than the time of running the code itself.

What do you suggest to get my data easily and fast?

Which for loop takes too much time? In the case of the first one, you're only doing 100 iterations whereas the second would do the full 300,000. — SEFL, Sep 15 '16 at 02:21
The value retrieval might be the operation that takes most of the time. Try iterating with `foreach var pair in read`. You will get a pair with key and value and do not need to query the value by key. — Nico Schertler, Sep 15 '16 at 02:24
@SEFL, extracting data either by excel or by text file takes too much time .. the algorithm or the code may be finish operating in two minutes but I keep waiting about 15 mins to get all the data — AlKobtan, Sep 15 '16 at 02:26
I suggest you should use 2D array to write in excel, it much faster (http://stackoverflow.com/questions/536636/write-array-to-excel-range). Moreover, you should turn the ***auto calculate*** off before you write in excel — Prisoner, Sep 15 '16 at 02:40
@NicoSchertler, your way with the txt file is the fastest one, thank you sir .. — AlKobtan, Sep 15 '16 at 13:01
Then also check if you really need a dictionary or if a simple list of pairs is sufficient. — Nico Schertler, Sep 15 '16 at 13:22

score 1 · Answer 1 · answered Sep 15 '16 at 08:09

Writing each value seperately to Excel is just really slow, have a go with this:

   Dim arr2D(100, 1) As String
    For i = 0 To 100
        arr2D(i, 0) = read.Item(read.Keys.ElementAt(i))
        arr2D(i, 1) = read.Keys.ElementAt(i)
    Next
    Dim oExcel As Object = CreateObject("Excel.Application")
    Dim oWorkbook As Object = oExcel.Workbooks.Add
    Dim oWorksheet As Object = oWorkbook.Worksheets(1)
    Dim vRange As Object = oWorksheet.Range("A1")
    vRange = vRange.Resize(UBound(arr2D, 1) + 1, UBound(arr2D, 2) + 1)
    vRange.Formula = arr2D
    vRange.Columns.autofit()
    oExcel.Visible = True

I think this is the best way for excel, I have tried it but write to a txt file is faster than this and copying from a dictionary to a two dimension array consumes time too and I tried 'read' to be a two dimensional array from the beginning but I have to 'redim' it every time and that takes a time too — AlKobtan, Sep 15 '16 at 12:59

score 0 · Accepted Answer · answered Sep 15 '16 at 13:08

every time I creat a new txt file with name "data" tailed with time.hour and time.minute

as @NicoSchertler, using for each var in read to get var.value and var.key

this is the fastest way

Dim file As System.IO.StreamWriter
Dim path As String
path = "C:\Users\Dr. Mohamed ElWakil\Desktop\data" & Now.Hour & Now.Minute & ".txt"
file = My.Computer.FileSystem.OpenTextFileWriter(path, True)

For Each var In read
    file.WriteLine(var.Key & "," & var.Value)
Next

file.Close()

What is the fastest way to get data from vb.net?

2 Answers2