Click here to Skip to main content
15,566,615 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hi everybody :)

i take datatable from excel page then insert oracle database in visual studio

when rows more then 5000 then then it gets very slowly

i changed my insert command with
"INSERT ALL "& INSERT_SATTMSIP &" Select * FROM dual


but for this command i must combine lines in datatable into one string

how can i do this fastly

i used for next in datatable but very slowly

What I have tried:

"INSERT ALL "& INSERT_SATTMSIP &" Select * FROM dual


 Dim INSERT_SATTMSIP As String

        Dim SUTUN = "HAKEDISDONEMI,ONGORULENODEMETARIHI,DURUM,ODEMETARIHI,VADETARIHI,KAYITTARIHI,KAYITNO,KAYITTIPI,KAYITTURU,KAYITSINIFI,
                         SIPARISNO,SIPARISTARIHI,URUNNO,URUNADI,URUNADEDI,BIRIMSATISFIYATI,SATISTUTARI,PARABIRIMI,VADESURESI,PAKETNO"
'dt2 as datatable from excel
       
 For r = 0 To DT2.Rows.Count - 1
            INSERT_SATTMSIP = INSERT_SATTMSIP & vbLf & " INTO " & M_YIL & ".HB_EKSTREGIDER (" & SUTUN & ") " &
                   "VALUES ('" & DT2.Rows(r).Item(0) & "','" & DT2.Rows(r).Item(0) & "','" & DT2.Rows(r).Item(0) & "','" & DT2.Rows(r).Item(0) & "',
                            '" & DT2.Rows(r).Item(0) & "','" & DT2.Rows(r).Item(0) & "','" & DT2.Rows(r).Item(0) & "','" & DT2.Rows(r).Item(0) & "',
                            '" & DT2.Rows(r).Item(0) & "','" & DT2.Rows(r).Item(0) & "','" & DT2.Rows(r).Item(0) & "','" & DT2.Rows(r).Item(0) & "',
                            '" & DT2.Rows(r).Item(0) & "','" & DT2.Rows(r).Item(0) & "','" & DT2.Rows(r).Item(0) & "','" & DT2.Rows(r).Item(0) & "',
                            '" & DT2.Rows(r).Item(0) & "','" & DT2.Rows(r).Item(0) & "','" & DT2.Rows(r).Item(0) & "','" & DT2.Rows(r).Item(0) & "') "
        Next
        MessageBox.Show(INSERT_SATTMSIP)
Posted
Updated 17-Sep-22 2:25am
v3

1 solution

Don't build up a huge string, it gets very inefficient as you have to copy the string data to a new string many, many times.

Start by using a StringBuilder instead, which reduces the number of copies considerably - particularly if you allocate a large chunk to start with: StringBuilder Class (System.Text) | Microsoft Learn[^]

If that isn't fast enough, then you need to start working out where the bottlenecks are: the Stopwatch Class (System.Diagnostics) | Microsoft Learn[^] will help you there.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900