Array to SQL

[mrtweaver]

Ok here is my question and i hope it is clear. I have been writing an application, one of the features is to take a text file, pull out the data i require, and put it in a SQL table

Now the thing is these text files can vary in size from 40000 up to 8 million records.

Right now due to the limitations of 5000 characters i have my write to SQL broken down where it writes 120 records per execution, however this slows down as the file size grows, when you get to 1.5 million records lets just say that takes about 15 min.

So in a nutshell here is what i have:

$one = (i, array[0]. array[1]) , (i +1, array[i+1][0], array[i+1][1]........ up to 59

$two = same as above but 60 through 119

$limit = ubound($array)

for i = 0 to $limit step 120

Then in the SQL statement i just add the two like SQL_Query(insert into table (num, stored, linking) values $one, $two

I am sorry this is not the actual code but i do hope it gives you an idea of what i am doing.

I am hoping someone here might have a better or faster approach to dump an array down to an SQL table.

[kylomas]

mrtweaver,

You did not give any real specs/criteria/code so I made up my own.

 I was curious and hadn't done any chained insertion before so I cranked up this little doggy.  I experimented to get the right chunk size by using a large number and stepping it down till I no longer got "term limit exceeded" errors.  There may also be a length threshold but I did not exceed this as my input is simply integers.  I took a cursory glance at the SQLite doc but could not find the limits.

This was run under WIN7 x64 and written to a DB on an SSD drive.

expandcollapse popup

; code based on example by jchd found in this thread "https://www.autoitscript.com/forum/topic/164719-sqlite-in-memory-db-inserts-too-slow/"

#include <array.au3>
#include <date.au3>
#include <file.au3>
#include <sqlite.au3>

; --------------------------------------------------------------------------------------------------------------------------------------

filedelete(@scriptdir & '\test.db3')

_SQLite_Startup()
Local $hDB = _SQLite_Open(@scriptdir & '\test.db3')
If Not $hDB Then _fini()
OnAutoItExitRegister('_fini')
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)

Local $ret = _SQLite_Exec(-1, 'CREATE TABLE if not exists t1 (num int, stored int, linking int);')

If $ret <> $SQLITE_OK then Exit (MsgBox(0, 'SQLITE ERROR', 'Create Table Failed'))

; --------------------------------------------------------------------------------------------------------------------------------------

local $iNumRecs = 8000001
local $iMaxInsert = 500
local $sSQLHead = 'insert into t1 values '

local $st = timerinit(), $aTest[$iNumRecs][2]

; generate test file

for $i = 1 to $iNumRecs - 1
    $aTest[$i][0] = random(10000,19999,1)
    $aTest[$i][1] = random(50000,59999,1)
Next

ConsoleWrite('Time to generate test array = ' & stringformat('%02.3f',timerdiff($st)/1000) & ' seconds' & @CRLF)

$st = timerinit()

_SQLite_Exec($hDB, 'begin immediate transaction;')

local $sSQLWork = $sSQLHead
for $i = 1 to ubound($aTest) - 1
    $sSQLWork &= '(' & $i & ',' & $aTest[$i][0] & ',' & $aTest[$i][1] & ')'
    if mod($i,$iMaxInsert) then
        $sSQLWork &= ','
    Else
        _sqlite_exec($hDB,$sSQLWork)
        $sSQLWork = $sSQLHead
    endif
    ;if mod($i,10000) = 0 then ConsoleWrite($i & ' records inserted     ' & _nowtime() & @CRLF)
next

_SQLite_Exec($hDB,';commit;')

ConsoleWrite('Time to insert ' & $iNumRecs - 1 & ' records = ' & stringformat('%02.3f',timerdiff($st)/1000) & ' seconds' & @CRLF)

Func _fini()
    ConsoleWrite('Total changes made = ' & _SQLite_TotalChanges($hDB) & @CRLF)
    _SQLite_Close($hDB)
    _SQLite_Shutdown()
    Exit
EndFunc   ;==>_fini

#cs

>"C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "C:\Users\ADMIN010\Documents\sqlite stress test example.au3" /UserParams
+>22:15:07 Starting AutoIt3Wrapper v.14.801.2025.0 SciTE v.3.4.4.0   Keyboard:00000409  OS:WIN_7/Service Pack 1  CPU:X64 OS:X64    Environment(Language:0409)
+>         SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE   UserDir => C:\Users\ADMIN010\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper   SCITE_USERHOME => C:\Users\ADMIN010\AppData\Local\AutoIt v3\SciTE
>Running AU3Check (3.3.13.19)  from:C:\Program Files (x86)\AutoIt3  input:C:\Users\ADMIN010\Documents\sqlite stress test example.au3
+>22:15:07 AU3Check ended.rc:0
>Running:(3.3.12.0):C:\Program Files (x86)\AutoIt3\autoit3.exe "C:\Users\ADMIN010\Documents\sqlite stress test example.au3"
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
_SQLite_LibVersion=3.8.4.3
Time to generate test array = 42.929 seconds
Time to insert 8000000 records = 112.342 seconds
Total changes made = 8000000
+>22:17:44 AutoIt3.exe ended.rc:0
+>22:17:44 AutoIt3Wrapper Finished.
>Exit code: 0    Time: 157.1

#ce

kylomas

Edit2: I found the limits doc here.  I accidentally hit the max term limit.

 

Edited July 30, 2015 by kylomas
additional info

[jchd]

IFF the input file is a .CSV then using the command-line interface (CLI) sqlite3.exe with the .import command will certainly be much faster than any AutoIt code which requires reading the file, splitting rows, then columns into an array and feeding the baby to SQL in chunks. You may have to create the table first with correct datatypes and experiment a bit to make sure the delimiter is correct.