So, I was using SQL Server but there is a need for me to change to SQLite. I had the following code that worked very fast for 600.000 rows (about a minute).
Dim SQLAdapter As New SqlDataAdapter
Dim dtKinoDraws As New DataTable
Dim SQLcmdBuilder As SqlCommandBuilder
'Emptying the Database
ExecuteSQLQuery(<SQL>
USE <%= DatabaseName %>
TRUNCATE TABLE [<%= DatabaseName %>].[dbo].<%= KinoDrawsTableName %>
</SQL>.Value)
'This is so we have the SCHEMA to use dtKinoDraws.NewRow later to add the 400.000 rows
SQLAdapter = New SqlDataAdapter(<SQL>
USE <%= DatabaseName %>
Select * FROM [<%= DatabaseName %>].[dbo].<%= KinoDrawsTableName %>
</SQL>.Value, SQLConn)
SQLcmdBuilder = New SqlCommandBuilder(SQLAdapter)
SQLAdapter.Fill(dtKinoDraws)
[Adding the 600.000+ rows to the datatable dtKinoDraws as described below]
'Copying everything from the datatable back to SQL Server (takes about a minute)
Using bulkCopy As New SqlBulkCopy(SQLConn)
bulkCopy.DestinationTableName = KinoDrawsTableName
bulkCopy.WriteToServer(item)
End Using
SQLAdapter.Update(dtKinoDraws)
SQLAdapter.Dispose()
SQLConn.Close()
Now I tried to do the same with SQLite but there is no SQLiteBulkCopy, modelling after my previous code and after searching stock overflow for a way, I wrote this extremely inefficient and snail-slow piece of code (takes literally HOURS):
Dim SQLiteAdapter As New SQLiteDataAdapter
Dim dtKinoDraws As New DataTable
Dim SQLitecmdBuilder As SQLiteCommandBuilder
'Emptying the Database
ExecuteSQLQuery(<SQL>
DELETE FROM [<%= KinoDrawsTableName %>];
VACUUM;
</SQL>.Value)
'This is so we have the SCHEMA to use dtKinoDraws.NewRow later to add the 400.000 rows
SQLiteAdapter = New SQLiteDataAdapter(<SQL>
Select * FROM <%= KinoDrawsTableName %>
</SQL>.Value, SQLConnLite)
SQLitecmdBuilder = New SQLiteCommandBuilder(SQLiteAdapter)
SQLiteAdapter.Fill(dtKinoDraws)
[Adding the 600.000+ rows to the datatable dtKinoDraws as described below]
SQLiteAdapter.Update(dtKinoDraws)
SQLiteAdapter.Dispose()
SQLConnLite.Close()
Visual Studio's Output gives me a nearly infinite number of this message at least twice a second: "SQLite notice (27): delayed 25ms for lock/sharing conflict at line 42982"
How can I improve it to be fast like the SQL Server one?
My code is in VB, so I'd prefer VB code, but I can translate C# ones to VB so that's cool too :)
===========================================================================
Now, I didn't think that the way the records are added to the datatable plays a role as THAT only takes seconds, so to keep it succinct, I abstracted that layout of information. If it is indeed needed, here's the whole code:
Private Async Function LoadKinoDrawsFromTextFiles(ByVal FromValue As Integer, ByVal ToValue As Integer, ByVal Files() As String, ByVal dtTable As DataTable) As Task(Of DataTable)
Dim Result As DataTable = dtTable.Clone
Await Task.Run(
Sub()
For i As Integer = FromValue To ToValue
DownloadingDrawsProgress += 1
Dim FileContent() As String = File.ReadAllLines(Files(i))
Dim CurLineKinoDrawNum As Integer = -1
For Each line In FileContent
If line <> "" AndAlso line.Length > 10 Then
Try
Dim LineDecomposition As List(Of String) = line.Split(" "c).ToList
Dim NewDataRow As DataRow = Result.NewRow
CurLineKinoDrawNum = CInt(LineDecomposition(0))
NewDataRow(0) = CurLineKinoDrawNum
Try
NewDataRow(1) = Date.ParseExact(LineDecomposition(1) & " " & LineDecomposition(2), KinoDateFormat, CultureInfo.InvariantCulture)
Catch ex As Exception
Try
NewDataRow(1) = Date.ParseExact(LineDecomposition(1) & " " & LineDecomposition(2), "d/M/yyyy HH:mm", CultureInfo.InvariantCulture)
Catch exc As Exception
NewDataRow(1) = Date.Parse(LineDecomposition(1) & " " & LineDecomposition(2))
End Try
End Try
NewDataRow(2) = ArrayBox(False, " ", 0UI, False, LineDecomposition.Skip(3), True, "")
Result.Rows.Add(NewDataRow)
Catch ex As Exception
Try
Dim Tries As Integer = 0
Dim DownloadClient As New WebClient With {.Proxy = Nothing}
Dim OnlineDrawJSONString As String
Dim OnlineDrawJSON As JObject = Nothing
Do Until Tries >= 10
Try
OnlineDrawJSONString = DownloadClient.DownloadString(DrawByNumberLink.Replace("{NumIdentifier}", CurLineKinoDrawNum.ToString))
OnlineDrawJSON = JObject.Parse(OnlineDrawJSONString)
Tries = 10
Catch exb As Exception
Thread.Sleep(2000)
Tries += 1
End Try
Loop
Tries = 0
If OnlineDrawJSON Is Nothing Then Throw New Exception("Unable to download the Draw!")
Dim OnlineDrawNum As Integer = OnlineDrawJSON(DrawIdentifier)(DrawNumberIdentifier).Value(Of Integer)()
Dim dateStr As String = CStr(OnlineDrawJSON(DrawIdentifier)(DrawDateIdentifier))
dateStr = dateStr.Substring(0, dateStr.IndexOf("T")) & " " & dateStr.Substring(dateStr.IndexOf("T") + 1, 5) '5 because it'll always be 5. e.g. "22:00".Length = 5
Dim OnlineDrawDate As Date
Try
OnlineDrawDate = Date.ParseExact(dateStr, "dd-MM-yyyy HH:mm", CultureInfo.InvariantCulture)
Catch exn As Exception
Try
OnlineDrawDate = Date.ParseExact(dateStr, "d-M-yyyy HH:mm", CultureInfo.InvariantCulture)
Catch exc As Exception
OnlineDrawDate = Date.Parse(dateStr)
End Try
End Try
Dim OnlineResults As List(Of Integer) = OnlineDrawJSON(DrawIdentifier)(DrawResultsIdentifier).Values(Of Integer)().ToList
Dim drRow As DataRow = Result.NewRow
drRow(0) = OnlineDrawNum
drRow(1) = OnlineDrawDate
drRow(2) = ArrayBox(False, " ", 0UI, False, OnlineResults, True, "")
Result.Rows.Add(drRow)
Catch exc As Exception
CreateCrashFile(exc, True,, MsgBoxStyle.Critical)
Exit Sub
End Try
End Try
End If
Next
Next
End Sub)
Return Result
End Function
Public Function ArrayBox(Of T)(ByVal doNumeriseItems As Boolean, ByVal DelimitationStr As String, ByVal SplitOnNum As UInteger, ByVal IgnoreNullValues As Boolean, ByVal IETVar As IEnumerable(Of T), Optional ByVal IgnoreDelimitSpace As Boolean = False, Optional ByVal DefaultSpace As String = " ", Optional ByVal AlwaysDelimitBeforeNewLine As Boolean = False, Optional ByVal PrefixString As String = "", Optional ByVal SuffixString As String = "", Optional DoNotPrefixIfValueIsNumeric As Boolean = False, Optional DoNotSuffixIfValueIsNumeric As Boolean = False, Optional ByVal AddTwoDoubleQuotesBeforeADoubleQuoteCharacter As Boolean = False, Optional ByVal StrInCaseOfNullValue As String = " ") As String
Dim sbRet As New StringBuilder
If IETVar IsNot Nothing Then
For i = 0 To IETVar.Count - 1
If (IETVar(i) IsNot Nothing AndAlso Not IsDBNull(IETVar(i))) OrElse Not IgnoreNullValues Then ' If the IEVariable is something
Dim StringToBeAppended As String = StrInCaseOfNullValue
Try
StringToBeAppended = IETVar(i).ToString
Catch ex As Exception
End Try
If doNumeriseItems Then sbRet.Append(i + 1).Append(") ") ' Numerise it if user asked it
If PrefixString <> "" AndAlso (Not IsNumeric(StringToBeAppended) OrElse Not DoNotPrefixIfValueIsNumeric) Then ' If there is a Prefix set, and value isnt numeric OR, is numeric but prefixing is allowed
sbRet.Append(PrefixString) ' then append it before the actual value
End If
If Not AddTwoDoubleQuotesBeforeADoubleQuoteCharacter Then ' If we are to proceed normally,
sbRet.Append(StringToBeAppended) ' Print the Value of IETVar(i)
Else
sbRet.Append(StringToBeAppended.Replace("""", """""""")) ' Else, add 2 double-quotes before any double-quote character
End If
If SuffixString <> "" AndAlso (Not IsNumeric(StringToBeAppended) OrElse Not DoNotSuffixIfValueIsNumeric) Then 'If there is a Suffix set, and value isnt numeric OR, is numeric but suffixing is allowed
sbRet.Append(SuffixString) ' then append it after the actual value
End If
If AlwaysDelimitBeforeNewLine AndAlso DelimitationStr <> "" AndAlso i <> IETVar.Count - 1 Then ' If Always Delimit And it isn't the last element of all
sbRet.Append(DelimitationStr) ' then Delimit it
If Not IgnoreDelimitSpace AndAlso (SplitOnNum = 0 OrElse (i + 1) Mod SplitOnNum <> 0) Then sbRet.Append(DefaultSpace) ' Put a space if a space should be put
ElseIf DelimitationStr <> "" AndAlso (SplitOnNum = 0 OrElse (i + 1) Mod SplitOnNum <> 0) AndAlso i <> IETVar.Count - 1 Then ' if it should be delimited And it isn't the last element of the line
sbRet.Append(DelimitationStr) ' then Delimit it
If Not IgnoreDelimitSpace Then sbRet.Append(DefaultSpace) ' Put a space if a space should be put
End If
If SplitOnNum <> 0 AndAlso (i + 1) Mod SplitOnNum = 0 AndAlso i <> IETVar.Count - 1 Then ' If you separate them with NewLines and it is the last element of the line
sbRet.AppendLine() ' then append a NewLine
End If
End If
Next
End If
Return sbRet.ToString
End Function
Public Async Sub FillDatabaseWithDefaultDraws()
Dim SQLiteAdapter As New SQLiteDataAdapter
Dim dtKinoDraws As New DataTable
Dim SQLitecmdBuilder As SQLiteCommandBuilder
'Emptying the Database
ExecuteSQLQuery(<SQL>
DELETE FROM [<%= KinoDrawsTableName %>];
VACUUM;
</SQL>.Value)
'This is so we have the SCHEMA to use dtKinoDraws.NewRow later to add the 400.000 rows
SQLiteAdapter = New SQLiteDataAdapter(<SQL>
Select * FROM <%= KinoDrawsTableName %>
</SQL>.Value, SQLConnLite)
SQLitecmdBuilder = New SQLiteCommandBuilder(SQLiteAdapter)
SQLiteAdapter.Fill(dtKinoDraws)
Dim Files() As String = Directory.GetFiles(strKinoDrawsDirPath)
Dim item = Await LoadKinoDrawsFromTextFiles(0, Files.Length - 1, Files, dtKinoDraws)
dtKinoDraws.Merge(item)
SQLiteAdapter.Update(dtKinoDraws)
SQLiteAdapter.Dispose()
SQLConnLite.Close()
End Sub
And here's what each file in Files(i) looks like:
> 304454 30/11/2011 21:00 7 65 31 62 21 50 34 70 37 40 16 59 10 41 42 76 78 71 74 47
> 304453 30/11/2011 20:55 62 30 68 63 9 45 10 50 1 78 22 6 61 52 38 2 55 26 49 72
> 304452 30/11/2011 20:50 45 65 42 1 78 14 16 4 43 70 28 19 67 23 77 40 49 71 22 21
> ...
> 300971 1/11/2011 10:00 52 48 1 76 13 38 24 18 16 20 53 59 4 40 65 68 42 9 2 51
