question

AlanLiddle-0272 avatar image
0 Votes"
AlanLiddle-0272 asked AlanLiddle-0272 commented

What is the Fastest Way to Update Large Amounts of data in SQL Using VB.Net

Hi All,

I have to update 9.5 million records in a table in SQL server using a VB.net Windows Forms app. What is the fastest way to do it?

Regards

Alan Liddle

sql-server-generalsql-server-transact-sqldotnet-visual-basicwindows-forms
· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AlanLiddle-0272 ,
You can consider using Bulk Update in your code.
Here's a related reference you can refer to.
How to update large quantity of data in database using C#?
You can convert the code to vb. net.
Hope it could be helpful.


0 Votes 0 ·

What exactly do you mean by "update using VB.net Windows Form"? Are you going to update every record, some records, 1 record?

0 Votes 0 ·

I have 9.5 million records that all need to be updated at the same time. I have added extra fields/columns for this table and need to apply values to the additional fields all at the same time. The new data is in a .csv file that I will read to update the SQL data table records. I certainly don't want to find and update each record individually.

I want to be able to:

  1. Read all records into memory

  2. Read CSV data into memory

  3. Update each SQL data record in memory with CSV data

  4. Save all records at once

Alan :-)



0 Votes 0 ·

You should read the CSV file into a "stage" table and then use TSQL commands to merge the data to the existing table all at once.

1 Vote 1 ·
Show more comments

Read all records into memory

Huh?
Let SQL Server do the work, it knows best how to handle data.

0 Votes 0 ·
Show more comments

Hi @AlanLiddle-0272 ,
Do you still meet this issue? Please let me know if you need further help.

0 Votes 0 ·
AlanLiddle-0272 avatar image
0 Votes"
AlanLiddle-0272 answered AlanLiddle-0272 commented

Hi All,

I have to admit to have been terrified by the numbers involved in this exercise. It was actually 10.6 million records to update rather than 9.5 million, but close enough. Nobody actually told me how to do it. I was pointed at stuff that really didn't provide answers, so I finally decided to do it the old fashioned way....... What the hell was I worried about? I cut some code in about 30 minutes and it's currently about one hour into execution and about a quarter of the way through. Given that I never have to do this again, I consider this exceptionally fast. Here is my VB code (simples!):

Private Sub ReadShareDays()
Dim Files As Long
Dim theFile() As String
Dim fb As Integer
Dim s1 As String

 lblProgress.Visible = True
 ReDim theFile(0 To 10000)
 Files = 0
 s1 = Dir("E:\Alan & Jill\Shares\Share Market Analysis\Daily Prices\*.txt",)
 Do While s1 <> ""
   Files += 1
   theFile(Files - 1) = s1
   s1 = Dir()
 Loop
 ReDim Preserve theFile(0 To Files - 1)
 PB1.Maximum = Files
 PB1.Step = 10
 PB1.Value = 0
 For i1 = 1 To Files
   ShareDays = 0
   ReDim ShareDay(0 To 4000)
   s1 = theFile(i1 - 1)
   lblProgress.Text = "Reading Share Days - " & GetField(s1, ".")
   fb = FreeFile()
   FileOpen(fb, "E:\Alan & Jill\Shares\Share Market Analysis\Daily Prices\" & theFile(i1 - 1), OpenMode.Input)
   Do While EOF(fb) <> True
     s1 = LineInput(fb)
     If s1 <> "" Then
       ShareDays += 1
       With ShareDay(ShareDays - 1)
         .theShare = GetField(s1, ",")
         .theDate = MakeDate(GetField(s1, ","))
         .theOpen = Val(GetField(s1, ","))
         .theHigh = Val(GetField(s1, ","))
         .theLow = Val(GetField(s1, ","))
         .theClose = Val(GetField(s1, ","))
         .theVolume = Val(GetField(s1, ","))
       End With
     End If
   Loop
   FileClose(fb)
   SaveShareDays()
   PB1.Value += 1
   Application.DoEvents()
 Next
 ReDim Preserve theShare(0 To Shares - 1)
 lblProgress.Visible = False
 Application.DoEvents()

End Sub

Private Function MakeDate(theDate As String) As Date
Dim theYear As String
Dim theMonth As String
Dim theDay As String

 theYear = Mid(theDate, 1, 4)
 theMonth = Mid(theDate, 5, 2)
 theDay = Mid(theDate, 7, 2)
 MakeDate = CDate(theDay & "/" & theMonth & "/" & theYear)

End Function

Private Sub SaveShareDays()
Dim DBCon As New sqlData
Dim DBCon2 As New sqlData
Dim ShareRec As Long
Dim fb As Integer

 fb = FreeFile()
 FileOpen(fb, "E:\Alan & Jill\Shares\Share Market Analysis\Daily Prices\ShareDayErrors.csv", OpenMode.Append)
 For Each Share In ShareDay
   ShareRec = 0
   With DBCon
     .Query("SELECT RecNum FROM Shares WHERE theCode = '" & Share.theShare & "'")
     If .RecordCount > 0 Then
       ShareRec = .DBDT.Rows(0)("RecNum")
     End If
   End With
   If ShareRec > 0 Then
     With DBCon
       .Query("SELECT * FROM ShareDays WHERE theShare = " & CStr(ShareRec) & " AND theDate = '" & Format(Share.theDate, "yyyy-MM-dd") & "'")
       If .RecordCount > 0 Then
         If .DBDT.Rows(0)("theClose") > 0 Then
           Share.theClose = .DBDT.Rows(0)("theClose")
           ShareRec = .DBDT.Rows(0)("theShare")
         End If
         With DBCon2
           .Query("UPDATE ShareDays SET theOpen = " & Share.theOpen.ToString & ", theHigh = " & Share.theHigh.ToString & ", theLow = " & Share.theLow & ", theClose = " & Share.theClose & ", Volume = " & Share.theVolume &
                  "WHERE theDate = '" & Format(Share.theDate, "yyyy-MM-dd") & "' AND theShare = " & CStr(ShareRec))
         End With
       Else
         PrintLine(fb, Format(Share.theDate, "dd/MM/yyyy") & "," & Share.theShare & "," & CStr(Share.theOpen) & "," & CStr(Share.theHigh) & "," & CStr(Share.theLow) & "," & CStr(Share.theClose) & "," & CStr(Share.theVolume))
       End If
     End With
   End If
 Next
 FileClose(fb)

End Sub

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Had you done this in any of the way we suggested, the execution time would have been a lot less, probably less than ten minutes. But if add the total development time to the execution time, I am not going to question that you found the fastest solution, if this is a true one-off.

It would be a different matter if this was to be running on a regular basis.

0 Votes 0 ·

Yep a true one-off situation. :-)

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered OlafHelper-2800 edited

What is the fastest way to do it?

Hello Alan,
There is just one way: Doing it.
You can do it batch wise, e.g. updating 1 mio rows per batch. But at the end of the day it is: Doing it.

For more you have to provide more details. E.g. disabling index may effected by update statement & enabling iut afterwards can speep it up.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
1 Vote"
karenpayneoregon answered

Seems with that many rows this would be done best done via a scheduled job. Dependent on the update logic, consider the advice here or look at using merge.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered AlanLiddle-0272 commented

The problem with this sort of question is that we don't know your current level. We may be give answers that go over your head. Or we may give you answers like "I already know that".

Anyway, the first thing is that you need send all data at once, or possibly in batches of, say, one million rows. You cannot send one row at a time. There are a couple of devices to use:

  1. The SqlBulkCopy class.

  2. Use a table-valued parameter. I written about these here

  3. Pack all data in an XML or JSON document.

Once you have the data in SQL Server, there are just too many "it depends" for it to be meaningful when I know so little.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Erland,

Please refer to my reply to Tom Phillips. Also, I have 40 years experience with VB6 and other desktop languages, but still technically a novice at VB.Net.

0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered AlanLiddle-0272 commented

I want to be able to:

Read all records into memory

Read CSV data into memory

Update each SQL data record in memory with CSV data

Save all records at once

I sense that there is one important thing you have not really grasped. Your VB .Net program and SQL Server are two separate processes. (And this is no different from VB6.) So you must read the data from the file and send it to SQL Server.

And there is not really any reason to read all records into memory at once. Rather you can read the file and stream it to SQL Server through a table-valued parameter. The you would do the update as:

UPDATE tbl
SET      col1 = tvp.col1,
     col2 = tvp.col2,
     ...
FROM  tbl
JOIN  @tvp tvp ON ...

As it happens, my article Using Table-Valued Parameters in .NET, includes an example with reading a CSV file and streaming it to SQL Server.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Erland,

This looks more promising. I'll have a read.

0 Votes 0 ·