Speeding up codes write textbox to datagridview

Question

1.00/5 (1 vote)

See more:

Hi how can i speed up my codes when writing data richtextbox to datagridview

i have 35000 rows in richtextbox

i tested my codes ;
but only 1000 rows write 1 minute

so when all data writing finişhed pasted 35minutes

What I have tried:

With STOK_LIST

          .Rows.Clear()
          For x = 0 To RichTextBox1.Lines.Count - 1
              Dim tx = RichTextBox1.Lines(x).ToString
              If tx <> "" Then
                  Dim i = STOK_LIST.Rows.Add
                  Label1.Text = +i
                  .Rows(i).Cells(0).Value = tx.Split(vbTab)(0)
                  .Rows(i).Cells(1).Value = tx.Split(vbTab)(1)
                  .Rows(i).Cells(2).Value = tx.Split(vbTab)(2)
                  .Rows(i).Cells(3).Value = tx.Split(vbTab)(3)
                  .Rows(i).Cells(4).Value = tx.Split(vbTab)(4)
                  .Rows(i).Cells(5).Value = tx.Split(vbTab)(5)
                  .Rows(i).Cells(6).Value = tx.Split(vbTab)(6)
                  .Rows(i).Cells(7).Value = tx.Split(vbTab)(7)
                  .Rows(i).Cells(8).Value = tx.Split(vbTab)(8)
                  .Rows(i).Cells(9).Value = tx.Split(vbTab)(9)
                  .Rows(i).Cells(10).Value = tx.Split(vbTab)(10)

              End If
          Next

      End With

Posted 17-Jun-20 21:31pm

Member 14588284

Updated 18-Jun-20 2:49am

Add a Solution

Comments

[no name] 18-Jun-20 3:40am

First thing I would try is to split the source string _one_ time and use the one result to assign cell 0 to 10.

Here also some hints concerning BeginUpdate ... .net - BeginUpdate / EndUpdate for DataGridView request - Stack Overflow[^]

Member 14588284 18-Jun-20 3:58am

ı cant open Stack Overflow site for our company proxy rules
can u help prepare an excample code

[no name] 18-Jun-20 6:58am

Btw. DataGridView is not a good choice to save data. I would expect filling in your data first into a DataTable and bind the DataTable afterwards to the DGV will be more performant.

[no name] 18-Jun-20 7:32am

I just made a small test in c#.
a.) Filling a RichtTextBox with 35'000 strings
b.) Loop over the RichTextBox-Lines and only access line by line without further processing.

And it takes time.... looks like 30Min. For me it looks like the RichTextBox is the bottleneck.

From where the data for the RichTextBox come originally? From a file? If yes, I suggest to bypass the RichTextBox.

Member 14588284 18-Jun-20 7:40am

i'm copying rows from excel file then paste in richtextbox
then i want fill datagridview tables from richbox lines
i use this way because i didnt find anything pasting direct datagridview

[no name] 18-Jun-20 7:43am

Ok. I can't give you code in VB.NET because I dn't know it. But in c# first copy the richtext lines into a list of strings does dramatically speed up the things:


List<string> lns = richTextBox1.Lines.ToList<string>();
int lnCount = lns.Count;
for (int i = 0; i < lnCount; i++)
{
   string ln = lns[i];
}

Member 14588284 18-Jun-20 8:29am

Thanks for all advises
probably i will take direkt in excel file.

[no name] 18-Jun-20 8:34am

You are welcome.
"probably i will take direkt in excel file.": Or directly from clipboard. You will find a lot of examples with google for it. But again, copy the RichTextBox Lines into a list of strings I think will help you a lot. After you have have this you still can optimize step by step to bypass richtextbox.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2020-06-17T21:39:00

Solution 1

Start by doing the split once per loop, not 11 - Split does a lot of object allocation so it's not fast.

Second, call Control.SuspendLayout Method (System.Windows.Forms) | Microsoft Docs[^] on your DGV before you start to add any rows, and then call Control.ResumeLayout Method (System.Windows.Forms) | Microsoft Docs[^] on it after your loop ends.

That should fix it.

Posted 17-Jun-20 21:39pm

OriginalGriff

Comments

Garth J Lancaster 18-Jun-20 3:44am

+5, nice

Member 14588284 18-Jun-20 3:50am

Start by doing the split once per loop, not 11 - Split does a lot of object allocation so it's not fast.

how can i do this?

OriginalGriff 18-Jun-20 4:23am

Call it one time at the top of the loop, and use the array it returns instead of calling it repeatedly ...

Member 14588284 18-Jun-20 4:30am

i didnt understand :(

OriginalGriff 18-Jun-20 4:46am

What part do you not understand?
I'm not trying to be annoying, or dense - I only get exactly what you type to work with, I get no other context.

Member 14588284 18-Jun-20 4:50am

i didnt understand how can i change my codes?

OriginalGriff 18-Jun-20 5:29am

You know what the Split method does, yes?
What does it return?

Member 14588284 18-Jun-20 5:42am

of course
but it takes 30 minutes

OriginalGriff 18-Jun-20 5:53am

Oh good grief ...
You know what it does, you know what it returns.
And you can't work out how to use that return value?

[no name] 18-Jun-20 6:07am

A small hint


Dim splitted_once_per_loop= tx.Split(vbTab)
Dim i= STOK_LIST.Rows.Add ...

OriginalGriff 18-Jun-20 6:25am

I'm trying to get him to engage his brain cells ... :laugh:
I'm forever amazed that these guys can create so much code without doing that at all ... :sigh:

[no name] 18-Jun-20 6:34am

Calm down, he is trying to help!

So if spliting only once per loop does not improve much, try next with this "SuspendLayout" stuff and maybe also "BeginUpdate"

Member 14588284 18-Jun-20 6:37am

he is always making smug attitudes

[no name] 18-Jun-20 6:39am

No, he don't. Read again through all the comments. It is really hard to recognize whether you tried anything of his recommendations.

OriginalGriff 18-Jun-20 6:42am

I'm going to walk away from this one and leave it to you, or I'll be rude to him and that isn't fair...

[no name] 18-Jun-20 8:17am

FYI: OP deleted the post where he was angry.

OriginalGriff 18-Jun-20 8:33am

Yes, but it's not the first time - back in February when he was playing with the same code he decided that SHOUTing at me might get me to write his code for him, and I can bear a grudge with the best of 'em. :laugh:

Member 14588284 18-Jun-20 7:05am

oki'm sory
I say I don't understand but he laughs
i'm starter in VBnet

Member 14588284 18-Jun-20 7:30am

i say always because he writed before all member "idiots"

https://yadi.sk/i/TwCHrXOX67ehkA

https://www.codeproject.com/Questions/5261795/Get-data-access-with-hour-condititon

Richard Deeming 19-Jun-20 10:38am

If someone writes code with a SQL Injection vulnerability when they're first learning, that's OK. We all had to learn at one point.

If they continue to write vulnerable code after a more experienced developer has pointed out their mistake and told them how to fix it, then it's perfectly reasonable to call them an idiot.

And if they're going to object to the more experienced developer trying to help them because they don't want to hear about the vulnerabilities in their code, then I'd be inclined to call them worse than that! Words like "dangerous" and "liability" spring to mind...

Member 14588284 18-Jun-20 6:33am

you know everything very much but anyone dont read in your brain

Member 14588284 18-Jun-20 7:20am

With STOK_LIST
' Try
.Rows.Clear()
For x = 0 To RichTextBox1.Lines.Count - 1 Step 1000

Dim tx = RichTextBox1.Lines(x).ToString.Split(vbTab)
Dim i = STOK_LIST.Rows.Add
On Error Resume Next
For s = 0 To 9
.Rows(i).Cells(s).Value = tx(s)
Next s
Next
End With

i changed like this

[no name] 18-Jun-20 7:34am

Have a look to my latest comment to your question.

[no name] 18-Jun-20 8:08am

I just made a test in c# (I don't know VB.NET). The bottleneck seems to be accessing the RichTextBox.Lines (see also my comment to the question). If I loop over the 35'000 lines from the RichtTextBox and access them it takes time. I stopped it after about 5 Min.

If I first copy the lines from the RichTextBox like this "List<string> lns = richTextBox1.Lines.ToList<string>();" things speed up dramatically. But unfortunatelly I can't help OP because I don't know VB.NET.

And yes, all the suggestions in your answer make sense.

User 11060979 · Answer 2 · 2020-06-18T02:50:00

Please also take into account the suggestions from Solution 1.

Furthermore I think instead of accessing the lines of the richtextbox you should get them first something like this:

Dim textLines As List(Of String) = RichTextBox1.Lines.ToList()

and proceed your processing with textLines

Of course you can optimize much more. You can skip the RichTextBox by implementing Paste From Clipboard directly. Also using a DataTable as data store instead of DGV can lead to improvments and is also more clean.

I hope it helps and again, please take into acount, I don't know VB.NET so pay attention with the code snippet above.

[Edit 1] Forgive me that I don't know VB.NET. Anyway I tried it with it

Takes about 15 Min for 35'000 lines (i7/ 3.4 GHz):

VB

Private Sub ButtonProcessByRTBLines_Click(sender As Object, e As EventArgs) Handles ButtonProcessByRTBLines.Click

  Dim len = RichTextBox1.Lines.Count
  For i = 0 To len - 1
      Dim ln = RichTextBox1.Lines(i)
      If i Mod 500 = 0 Then
          Label1.Text = i.ToString()
          Application.DoEvents()
      End If
  Next
End Sub

Takes less than 0.5 seconds for 35'000 lines:

VB

Private Sub ButtonProcessByStringList_Click(sender As Object, e As EventArgs) Handles ButtonProcessByStringList.Click
  Dim textLines As List(Of String) = RichTextBox1.Lines.ToList()
  Dim len = textLines.Count

  For i = 0 To len - 1
      Dim ln = textLines(i)
      If i Mod 500 = 0 Then
          Label1.Text = i.ToString()
          Application.DoEvents()
      End If
  Next
End Sub

[Edit 2] Finally this not optimized code will be processed in less than 15 seconds. 35'000 lines on a i7/ 3.4 GHz:

Private Sub ButtonProcessToDGV_Click(sender As Object, e As EventArgs) Handles ButtonProcessToDGV.Click
    Dim textLines As List(Of String) = RichTextBox1.Lines.ToList()
    Dim textLinesCount = textLines.Count
    With STOK_LIST
        .Rows.Clear()
        For x = 0 To textLinesCount - 1
            Dim tx = textLines(x).ToString
            If tx <> "" Then
                Dim splittedLn = textLines(x).Split(vbTab)
                Dim i = STOK_LIST.Rows.Add
                Label1.Text = +i
                .Rows(i).Cells(0).Value = splittedLn(0)
                .Rows(i).Cells(1).Value = splittedLn(1)
                .Rows(i).Cells(2).Value = splittedLn(2)
                .Rows(i).Cells(3).Value = splittedLn(3)
                .Rows(i).Cells(4).Value = splittedLn(4)
                .Rows(i).Cells(5).Value = splittedLn(5)
                .Rows(i).Cells(6).Value = splittedLn(6)
                .Rows(i).Cells(7).Value = splittedLn(7)
                .Rows(i).Cells(8).Value = splittedLn(8)
                .Rows(i).Cells(9).Value = splittedLn(9)
                .Rows(i).Cells(10).Value = splittedLn(10)

            End If
            If x Mod 500 = 0 Then
                Label1.Text = x.ToString()
                Application.DoEvents()
            End If
        Next

    End With
End Sub