Fastest way to import data in SQLite DB

[jchd]

You can insert a number N of rows in a single insert statement (see the syntax here). Doing so you only invoke one SQLite call (*) and this is much faster than doing N individual inserts. N is bounded by the size of the whole statement, say with your example content you can easily group 100 rows in one INSERT, but 1000 may exceed allowable statement string size (I don't know what the limit is exactly). Doing that in a loop placed inside a transaction speeds things up very signifcantly.

Yet AutoIt being slow and DllCall eating "some time", you benefit from using the standalone CLI (sqlite3.exe). In the example I ran and posted, 11k rows were inserted in under one second.

One thing though, it seems that the CLI doesn't like column names surrounded by double quotes.

I'll try to look why we get an error using _SQLite_SQLite3.exe().

 

(*) In fact the gain in runtime is because the statement is only "prepared" once and values for successive rows binded to this prepared statement. SQLite "prepares" statements fed to it by "compiling" them, turning them into a series of pseudo-executable bytecode ops. This requires checking the schema and the statement for validity and going thru the query optimizer. Doing that only once for N rows is a huge time gain.

Edited May 10, 2017 by jchd

[kylomas]

Fransesco,

Example from proof of concept I did some time ago...note the library dataset name...

#include <FileConstants.au3>
#include <sqlite.au3>
#include <array.au3>

Local $sSQLiteMod = 'C:\Program Files (x86)\AutoIt3\install\Extras\SQLite\sqlite3.exe'

; do NOT use AutoIt macros for the next two file names

Local $sMasterFile = "fully.qualified.filename" ; your file with ALL entries
Local $sDetailFile = "fully.qualified.filename" ; your file with SOME entries

$st = TimerInit()

Local $sIn = 'create table if not exists mstr (c1);' & _
        'create table if not exists det (c1);' & _
        @CRLF & '.import ' & $sMasterFile & ' MSTR' & _
        @CRLF & '.import ' & $sDetailFile & ' DET' & _
        @CRLF & 'SELECT DISTINCT c1 FROM mstr WHERE c1 Not IN (SELECT DISTINCT c1 FROM det);'

Local $Out = _TempFile()
Local $in = _TempFile()

FileWrite($in, $sIn)

Local $sCmd = @ComSpec & ' /c "' & $sSQLiteMod & '" < ' & $in & ' > ' & $Out
Local $pid = RunWait($sCmd, @WorkingDir, @SW_HIDE)
ProcessWaitClose($pid)
ConsoleWrite('Time to find entries unique to mstr = ' & Round(TimerDiff($st) / 1000, 3) & ' seconds' & @CRLF)
ConsoleWrite(StdoutRead($pid) & @CRLF)
ConsoleWrite('! out ' & @CRLF & FileRead($Out) & @CRLF)

FileDelete($Out)
FileDelete($in)

kylomas

edit: How much time are we talking about if you use batched txns and chained inserts (what jchd cited earlier)?

Edited May 10, 2017 by kylomas

[FrancescoDiMuro]

@jchd, @kylomas, @AspirinJunkie

Thanks for your replies...

Today I'm a little bit busy...

As I can test your codes, I'll answer to you

Thank you! Have a good day!  

Francesco

[FrancescoDiMuro]

Guys, in the scenario that I have, what is the best solution, in terms of efficency?

I have a .txt file from which I retrieve some data. This file grows daily, and the information are divided from @TAB... But, some fields, are enclosed by double quotes...
This file is a "storical" log of some alarms in a system ( automation system, not something that I want to hack/violate ).

So, from this file, I have to create a report... 
The report tool I use is named Crystal Reports... It's a VB based tool that allows the user to create a report from a large variety of input files ( .txt, MySQL DB, ODBC and so on... ).

At the moment, the procedure that I've been using for create a report, is read from the source file, that is a .txt file, read the content of the file in an array with _FileReadToArray(), module the information in a For...Next loop, replacing @TABs with semi-colon, double quotes with nothing ( "" ), and splitting the line just moduled with StringSplit(), taking the semicolon as separator, in order to have the fields in an array. Then, I parse the field(s) of the array splitted with the user data, inserted in a GUI ( a date, for example ). If there is a match, I write all the fields of the splitted array in a file. When all the lines have been compared, I run an .exe that I've made through Crystal Reports, and so, I have my report.

Now, I'd like to do something like that, but, with SQLite this time, because the query I should do, are more easy to do through a database instead of a .txt file...
I have to select data between two timestamps, and this, is very easy to do with SQLite with instruction like BETWEEN or LIKE, with just a query, without split anything...

What is the best way to do all this stuff? 
I tried different approaches to this scenario, but I still can't find the fastest...

Don't post codes for now... I want to understand the functionality of what I'd like to develop.

Thanks a lot for everyone has read 'til here.

Have a good lunch

Francesco

EDIT: 

@jchd, I've seen that the best solution for fast inserts is group them and then, make a bulk insert ( as you suggested ).

What is the limit for the bulk insert, always in terms of efficiency? 

Thanks  

Edited May 11, 2017 by FrancescoDiMuro
Thanks jchd! :D

[jchd]

Using sqlite3.exe for direct massive insert of large .tsv file is by far the fastest way. I believe I' have found the reason for the failure of _SQLite_SQLiteExe(). The return code of the RunWait call (variable $nErrorLevel) is 1 albeit the command ran successfully.

To fix, edit the UDF and change line 847:

If @error = 1 Or $nErrorLevel = 1 Then
to
        If @error = 1 And $nErrorLevel = 1 Then

Using this the test script I wrote builds an ALARMI table with 44735 rows in under a second on my prehistoric PC.

If you stick to AutoIt code to do the same, indeed looping insertion of multiple rows, all inside a transaction, is the next fastest way. The practical limit is to be determined as many factors play a role. Experimentation is your best advisor.

[FrancescoDiMuro]

@jchd

Thanks for your time and your answer!

So, the best way to insert N rows inside a table, is through the .import from SQLite CLI?  

Thank you so much!