Ποιος ο βέλτιστος τρόπος να περάσουμε ένα Datatable σε SQLite?

AlKiS — Tue, 06 Jun 2017 20:13:58 GMT

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

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

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 If

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

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

WinForms

Ποιος ο βέλτιστος τρόπος να περάσουμε ένα Datatable σε SQLite?