B4J Question Reading in large amounts of data in DB

[strupp01]

How can I import a file with 40,000 - 100,000 lines as fast as possible into a database. Data per line are separated by commas.
"LOAD DATA INFILE" probably does not work. With "LoadCSV" you can only load into a list? Or does it go directly into a DB?
Who can help and can show me the command?

 

[keirS]

Why do you think "LOAD DATA INFILE" will not work? I use "LOAD DATA INFILE LOCAL" all the tme.

 

[rboeck]

If Speed is your main interest, try to use batch files for sqlite.

 

[strupp01]

@keirS,
Can you show me the command for a file 'Test.txt' and a DB 'Test.db' with Table 'Data1' with 'column1', 'column2' and 'column3'?

@rboeck,
Yes, time is the biggest problem.

 

[keirS]

Sorry I assumed you were using MySQL since "LOAD DATA INFILE" is a MySQL specific command.

 

[rboeck]

Here a starting point:
https://www.sqlite.org/cvstrac/wiki?p=ImportingFiles

 

[strupp01]

Does not help unfortunately much, since I probably have too little experience.
I thought there is a command in B4J, which allows a text file in this size to be inserted quickly into a DB.

 

[rboeck]

The problem with csv files is, that its not one standard, but a group of possibilities. It begins with the first row: Sometimes it is used as reference for field names, sometimes data begins in first row. In US and UK they are using a point in floating point values, in europe we use commas; for the date formats are endless possibilities.
The separation character could also be an comma, a semicolon or a tab character.
Only if you are preparing both - the database and the csv file, you can automate such import and get the highest speed. So how could be one command, if you have to select many options?

 

[Erel]

How long does it take to load the CSV file with StringUtils.LoadCSV?

Dim n As Long = DateTime.Now
su.LoadCSV(...)
Log(DateTime.Now - n) 'ms

 

[strupp01]

@Erel,
The time to load the file is not the problem, but save it into an SQL DB. The charge takes only 4-6 seconds. The write to an SQL DB however 1 minute or longer. Is there what is faster than with "Regex.split" the fields to separate and then with "Insert INTO ..." in the DB to write?

 

[PCastagnetti]

Did you use the transactions?
The use of transactions increases performance (in sqlite DB).

SQL.BeginTransaction

''Start insert loop 
           SQL.ExecNonQuery("your insert into.....")
''End insert loop
           SQL.TransactionSuccessful

 

[strupp01]

No, I have to try. Thank you.

 

[Erel]

It will be 100 times faster if you create a single transaction.

Or better: [B4X] SQL with Wait For

 

[strupp01]

I have the file with normal commands in a database read

Dim n As Long = DateTime.Now
l = File.ReadList(File.DirApp , "BRP.txt")

For i = 1 To l.Size - 1
Dim str() As String = Regex.split(",",l.Get(i))
SQL1.ExecNonQuery2("INSERT INTO BRP VALUES (?,?,?,?,?,?)", Array As Object("1", "2", "3", str(0), str(1), str(2)))
Next
Log(DateTime.Now - n) 'ms

This took about 36 minutes for 396001 records.

Then I tried to use the solution of Erel. This has not worked.

First came the message 'Cannot assign void value'.

Then I inserted the 2 lines with 'ProgressDialogShow2("Inserting data...", False)' and 'ProgressDialogHide'. But something is missing. Do not know more. Here is the program and the text file.
Who can help again?

 

[rboeck]

i know, it is not what you looking for, but also interesting: i created a new database with the fields Time, 1, 2, 3 and imported your textfile with sqlite Expert in 182 ms!
Btw. the field 3 never got any value.
I will try further, this time with bj4 or batch commands.

 

[rboeck]

Hi, i removed the two ProgressDialogs, because they did work for me and added

SQL1.BeginTransaction

For i = 0 To L.size-1
        Dim str() As String = Regex.split(",",l.Get(i))
        SQL1.AddNonQueryToBatch("INSERT INTO BRP VALUES (?,?,?,?,?,?)", Array As Object("1", "2", "3", str(0), str(1), str(2)))
    Next

SQL1.TransactionSuccessful

The time difference was ~ 400 msec between Begintransaction and TransactionSuccessfull! I have 8845 records in the database.

 

[keirS]

What hardware are you running this on?

Region  Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 400
    #AdditionalJar: sqlite-jdbc-3.7.2

#End Region

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Private TableView1 As TableView
    Dim ASUtils As ApacheSU
    Dim SQL1 As SQL
    Dim n As Long = DateTime.Now
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    MainForm.RootPane.LoadLayout("Main")
      MainForm.SetFormStyle("UTILITY")
    MainForm.Title = "B4J HowTo Test"
    MainForm.Show
    SQL1.InitializeSQLite(File.DirApp & "\SQL_Datenbank\","Schlaf_gut_Test.db", True)
    SQL1.ExecNonQuery("CREATE TABLE IF NOT EXISTS BRP (Datum_Aufzeichnung Text, Datum TEXT, Uhrzeit TEXT, Plus_Zeit Text, Flow_Rate Text, Mask_Pressure Text)")
  
    AppInit
  
'    Daten_To_DB
End Sub

'Init controls, settings etc.
Sub AppInit
    FillTableView
End Sub

Sub FillTableView
    Dim DB_Daten(3) As String
    Dim su As StringUtils
    Dim l As List
    Dim csvString As String
    Dim csVArray() As String
    csvString = File.ReadString(File.DirApp , "BRP.txt")
    csVArray = ASUtils.SplitWithSeparator(csvString,",")
  

    
  
    Log(csVArray.Length)
    For i = 0 To csVArray.Length- 1 Step 3
        SQL1.AddNonQueryToBatch("INSERT INTO BRP VALUES (?,?,?,?,?,?)", Array As Object("1", "2", "3",csVArray(i) ,csVArray(i+1) , csVArray(i+2)))
    Next
  
    SQL1.ExecNonQueryBatch("Query")
  
  
  

    Log((DateTime.Now - n)/1000)  'ms
  
    'SQL1.Close

End Sub

'Close the app - add any specifics here
Sub AppClose
    MainForm.Close
End Sub

'Handle form closing via system close (top right X button)
Sub MainForm_CloseRequest (EventData As Event)
    AppClose
End Sub

Sub Query_NonQueryComplete(success As Boolean)
    Log(success)
    Log(((DateTime.Now - n)/1000))
End Sub

This code took 55 seconds to generate 4282824 records running B4J in debug mode. (I made you text file a lot bigger!)

 

[strupp01]

@rboeck
Your program code will run in 1.2 seconds, but there is no data in the database. My big file is broken after about 30 seconds.

 

[PCastagnetti]

try to replace
AddNonQueryToBatch
with ExecNonQuery2:

SQL1.BeginTransaction

For i = 0 To L.size-1
        Dim str() As String = Regex.split(",",l.Get(i))
        SQL1.ExecNonQuery2("INSERT INTO BRP VALUES (?,?,?,?,?,?)", Array As Object("1", "2", "3", str(0), str(1), str(2)))
    Next

SQL1.TransactionSuccessful

 

[strupp01]

@keirS,
Thank you for your program code. First, I have windows 7 64-bit.
Your program ran in the beginning in about 8 seconds. After 10-12 attempts it has a running time of more than 70 seconds and sometimes it depends entirely on. Very strange. All test with same file.